creating dynamic hierarchies on a dimension using MDX

Question

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

Answer 1

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 {
       ADDCALCULATEDMEMBERS([Measures].MEMBERS)} ON AXIS(0),
       {MyRows}
        HAVING [Measures].[Rank] >= 1 AND [Measures].[Rank] <= 20
      ON AXIS(1)
   FROM [Adventure Works]

creating dynamic hierarchies on a dimension using MDX

Follow this question

Related Questions