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

deepak gravatar 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 
        HAVING [Measures].[Rank] >= 1 AND [Measures].[Rank] <= 20
      ON AXIS(1)
FROM [Adventure Works]
more ▼

answered Oct 13, 2010 at 01:51 PM

asammartino gravatar 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.

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



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: 2068 times

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