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
asked Oct 13 '10 at 01:12 PM in Default
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.
WITH SET MyRows AS ORDER( NONEMPTY([Customer].[Customer].CHILDREN), [Measures].[Internet Sales Amount], BDESC )
FROM [Adventure Works]
answered Oct 13 '10 at 01:51 PM