asked
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
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
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]
