Hi Deepak,
I need to display top N levels in a Dimension with the cumulative sum.Here
is the Example

Consider Geography Dimension with 2 levels National and Region.I need top 2
Nationals and top 2 Regions with their cumulative sum.The report should
display as follows,

National1
Region1
Region2
cumulative sum of Regions in National1 - ?

National2
Region3
Region4
cumulative sum of Regions in National2 - ?

Cumulative sum of Nationals in Geography - ?

cumulative sum means the sum of respective members of a level other than
those selected in the report.

Can u pls give me a solution for this problem.

Thanks,
Aravind

Hi Aravind,

This earlier post, which discusses a similar scenario, may help you - it
doesn't do exactly what you want:

http://groups.google.com/group/micro...olap/browse_fr
m/thread/c6e42824d5acb5c1/70f70c94cd7c5139#70f70c94cd7c5139
...
The updated query for Adventure Works is:
Member [Measures].[ProductTop2] AS
'iif(IsLeaf([Product].[Product Categories].CurrentMember),
SetToStr({[Product].[Product Categories].CurrentMember}),
SetToStr(
Generate(TOPCOUNT([Product].[Product Categories].Children,
2, [Measures].[Sales Amount]),
StrToSet(CStr([Measures].[ProductTop2]))) +
Head(VisualTotals({[Product].[Product Categories].CurrentMember,
[Product].[Product Categories].Children -
TOPCOUNT([Product].[Product Categories].Children,
2, [Measures].[Sales Amount])},
"* - Other"))))'

SELECT {[Measures].[Sales Amount]} on columns,
StrToSet(CStr(([Measures].[ProductTop2],
[Product].[Product Categories].[All Products]))) on rows
FROM [Adventure Works]
--------------------------------------------------------
Sales Amount
Road-150 Red, 56 $1,847,818.63
Road-350-W Yellow, 48 $1,774,883.56
Road Bikes - Other $40,256,088.81
Mountain-200 Black, 38 $2,589,363.78
Mountain-200 Black, 42 $2,265,485.38
Mountain Bikes - Other $31,590,594.78
Bikes - Other $14,296,291.27
HL Mountain Frame - Silver, 38 $412,969.20
HL Mountain Frame - Black, 42 $395,972.64
Mountain Frames - Other $3,904,730.31
ML Road Frame-W - Yellow, 44 $255,122.13
ML Road Frame-W - Yellow, 44 $230,578.41
Road Frames - Other $3,364,152.80
Components - Other $3,235,551.17
All Products - Other $3,389,671.34

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***