creating dynamic hierarchies on a dimension using MDX

I have requirement to create a filter to show a Vendor Name list and allow users to choose one/multiple Vendors. However the list of Vendors runs into hundreds so I have to split up the Vendors into categories based on a Measure and allow users to choose vendors from these categories instead.

In more detail : Vendor Name List Vendor1 Vendor2 . . . Vendor100 as you can see on a reporting interface this list is too long to choose from instead I want to do something like this: Vendor Name List Top 20 Vendors -> List the top 20 Vendors in descending order of a Measure Other Vendors -> List the remaining Vendors in descending order of a Measure Upon expanding the Top 20 Vendors or the Other Vendors categories I want to show the Vendors list based on a measure. Can someone help me with this?

Thanks, Deepak

more ▼

asked Oct 13, 2010 at 01:12 PM in Default

avatar image

1 1 1 1

Does it have to be a measure? The members will keep changing. Why not create an hierarchy such as Country,State/Union,District,City Then when adding this filter to Excel it is automatically a tree

Oct 13, 2010 at 04:07 PM Scot Hauder
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Hi Deepak,

I think this is what you want and just did this example using Adventure Works. Customer instead of Vendor ordered by the measure Internet Sales in descending order using the rank function to limit to 20 values. Hope this helps.

-Anthony Sammartino

WITH SET MyRows AS ORDER( NONEMPTY([Customer].[Customer].CHILDREN), [Measures].[Internet Sales Amount], BDESC )

    MEMBER [Measures].[Rank]  AS RANK ([Customer].[Customer].DIMENSION, MyRows) 
    MEMBER [Measures].[MemberKey0] AS [Customer].[Customer].Member_Key
    MEMBER [Measures].[MemberName0] AS [Customer].[Customer].Member_Name 
    SELECT {
         HAVING [Measures].[Rank] >= 1 AND [Measures].[Rank] <= 20
          ON AXIS(1)
    FROM [Adventure Works]
more ▼

answered Oct 13, 2010 at 01:51 PM

avatar image


(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

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

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Oct 13, 2010 at 01:12 PM

Seen: 2681 times

Last Updated: Oct 13, 2010 at 01:12 PM

Copyright 2018 Redgate Software. Privacy Policy