x

TOPCOUNT at different levels of hierarchy

Hi All,

I am very new to SSAS and I have a requirement where I have to filter the cube data to show top 10 items at each level of hierachy.

For example the hierarchy looks something like below...

Country-->State-->City-->so on ...

and I have to get top 10 sales amount for countries,state, city ...

the output should be somethong like... Top 10 countries net sales amount, if i click on a country it should show top 10 states net sales amount and if i click on a state it should show top 10 cities net sales amount and so on..

I tried something like below but not getting the correct output. Please someone help me with this.

SELECT
{[Measures].[Net Cost Global]} ON COLUMNS,
    { TOPCOUNT( DESCENDANTS([Dim Global Drilldown Cube].[Hierarchy].CURRENTMEMBER, 7), 10, 
      [Measures].[Net Cost Global])}
        ON ROWS

FROM
     [XXXCube]

Thanks,

more ▼

asked Jul 20, 2012 at 06:11 PM in Default

SSASnewbie gravatar image

SSASnewbie
0 1 1 2

(comments are locked)
10|1200 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x729
x172
x67
x2

asked: Jul 20, 2012 at 06:11 PM

Seen: 959 times

Last Updated: Jul 20, 2012 at 06:11 PM