How to rollup using RollupChildren function

# How to rollup using RollupChildren function

• martes, 13 de marzo de 2012 14:21

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,

### Todas las respuestas

• martes, 13 de marzo de 2012 16:06

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.

• miércoles, 14 de marzo de 2012 19:15

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

• jueves, 15 de marzo de 2012 3:02

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

• Propuesto como respuesta jueves, 15 de marzo de 2012 3:02
• Marcado como respuesta jueves, 15 de marzo de 2012 11:00
•
• jueves, 15 de marzo de 2012 3:11

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

• Marcado como respuesta jueves, 15 de marzo de 2012 11:00
•
• jueves, 15 de marzo de 2012 3:15

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

• Marcado como respuesta jueves, 15 de marzo de 2012 11:01
•
• jueves, 15 de marzo de 2012 3:20

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