How to rollup using RollupChildren function

Question

Hi All,

How can I rollup a measure value based on childrens
I was using following code but the member  returns null
WITH member[Measures].[Revenue By RollUp] AS RollUpChildren ([Measures].[Revenue],"+")

I want to rollup values for dimension
Location:Country --> Region --> State --> City

So, I want revenue for year 2011 , for country "United States" and I want revenue rollup
1. All cities of United States
2. All States of united states
3. All Regions of united states
Note:
(a) there may be some revenue belongs to a state but not to any cities, means data are not only in leaf level.
(b) Similarly,  there may be some revenue belongs to a region but not to any state.
 I hope my question makes sense,

 Thanks advance for you help

   

Answer 1

Try this to do a rollup based on the children of a member

WITH member[Measures].[Revenue By RollUp]
AS SUM(<<Dimension Name>>.<<Hierarchy Name>>.CurrentMember.Children, [Measures].[Revenue])

hth

---

-Remember to mark as helpful/the answer if you agree with the post.

Answer 2

Hi Naveen,

Thanks for your response, But I am looking for multilevel hierarchy, like

cities rollup to state , state rollup to region, region rollup to countries.

-Maria

Answer 3

Hi Maria,

I totally agree that the RollupChildren function  is a bit confusing.

The concept is RollupChildren function is for hierarchies of a dimension not for measures

So your calculated member should be for dimension hierarchy

So, if you want to rollup for a country, say "United States"

then create a new member "United States By Rollup" , which rolls up all childrens of  "United States".

WITH member [Location].[Location Hierarchy].[United States By Rollup]

AS RollUpChildren

(

[Location].[Location Hierarchy].[United States],

"+"

)

Thanks,

-Ashim

---

Note : If this is helpful, Do not forget to mark as "Answered" or "Helpful"

Answer 4

Hi,

I am using "Adventure Works " sample database ,

I created a new dimension member "Canada By Rollup" .

By Rolling up all childrens of country "Canada", I used unary operator as "-" , which made the result as negative amount.

Note: All measures of "Canada By Rollup" will be calculated by rolling up children

I hope this helps

Thanks,

Ashim

---

Note : If this is helpful, Do not forget to mark as "Answered" or "Helpful"

Answer 5

Hi,

Another important point is that , once you created  your calculated member, the rollup apples to other measures too, for example

    [Measures].[Reseller Sales Amount],
    [Measures].[Reseller Tax Amount]

as shown below.

Hope this helps,

Ashim

---

Note : If this is helpful, Do not forget to mark as "Answered" or "Helpful"

Answer 6

Hi Maria,

I missed last part of the post

(a) there may be some revenue belongs to a state but not to any cities, means data are not only in leaf level.

Ashim: When roling up childrens of a region it will calculate from leaf and non leaf values.

(b) Similarly,  there may be some revenue belongs to a region but not to any state.
 I hope my question makes sense,

Ashim: Similarly, When roling up childrens of a country, it will calculate from leaf and non leaf values.

Hope this helps

-Ashim

---

Note : If this is helpful, Do not forget to mark as "Answered" or "Helpful"

Answer 7

That worked.

Very nicely explained.

Thank you very much,