MDX Ranking Question

Hi

We are trying to create a Ranking of a dimension based on multiple different measures. An example would be to rank the Sales Consultant by Num Sales, Total Sales and Num Customers.

In SQL, I would create three different temp tables, with the Sales Consultant, Measure and Ranking for each different measure. Then you could join these results together and generate the final ranking based on this result set.

Now the problem we are having is doing this in MDX. You can only order by one of the measures in any result set. Any way I look at this, it looks to be a multi step process to create this data. The only way I can think that this might work is to create a stored proc, but then this will limit the users ability to slice data by all the dimensions.

Are there any ideas on how we can do this that I have missed

Thanks

Michael

Hi

Thanks very much!!!! This just shows how far I have to go to understand MDX...

I took your query and expanded it to give what we were looking for:

Set [OrderedProducts] as

Order([Product].[Category].[Category]

[Measures].[Order Quantity], BDESC)

+ Order([Product].[Category].[Category]

[Measures].[Sales Amount], BDESC)

+ Order([Product].[Category].[Category]

[Measures].[Total Product Cost], BDESC)

Member [Measures].[QuantityRank] as

Rank(([Product].[Category].CurrentMember,

Exists([OrderedProducts],

Member [Measures].[SalesRank] as

Rank(([Product].[Category].CurrentMember,

Exists([OrderedProducts],

Member [Measures].[CostRank] as

Rank(([Product].[Category].CurrentMember,

Exists([OrderedProducts],

Member [Measures].[OverallActivity] as

Set [OrderedActivity] as

Order([Product].[Category].[Category]

[Measures].[OverallActivity], BASC)

Member [Measures].[ActivityRank] as

Rank(([Product].[Category].CurrentMember,

Exists([OrderedActivity],

[Measures].[ActivityRank]} on 0,

Extract(Exists([OrderedProducts],

[Product].[Category]) on 1

from [Adventure Works]

Order Quantity QuantityRank Sales Amount SalesRank Total Product Cost CostRank OverallActivity ActivityRank
Bikes 90,220 1 $94,620,526.21 1 $84,105,429.60 1 0.333333333333333 1
Clothing 73,598 2 $2,117,613.45 3 $1,748,777.45 3 2.11764705882353 3
Accessories 61,931 3 $1,272,057.89 4 $637,590.73 4 4.23529411764706 4
Components 49,027 4 $11,799,076.66 2 $10,766,110.18 2 1.77777777777778 2

Once again, Thanks for the help!!!!

Hi

A further question to this. We are also wanting to make this ranking Time aware. Currently, you can only run the quesry for a specific time period (eg [dimDate].[Year].[2006]). If you try to create a crossjoin in the report to have all the above measures by 2005 and 2006, then the ranking does not work anymore. As far as I understand it, this will be the case as the Order Set does not know about the time dimension, and hasn't ordered the Products by Time.

So, how I see this is that I will need to add the time Dim to the order Set, but at what level. We would like to make this dynamic within the cube and would like the ranking to work in a year on year, quarter on quarter, month on month ...etc level. I would think that ordering by the lowest level of time (day in my case) might work, but would create a set that is extremely large.

Would this work, or is there a better way to do this

Michael