I am trying to write an mdx ranking query.

Some background: I am collecting performance measure data on stores.
Stores are heirarchized by Region -> Area -> Unit. Performance
measures are also heirarchized, and are derived by applying a scoring
formula to a question. My measures dimension contains a calculated
Average Score.

I have a performance measure hierachy, EG:

-> Score Group
-> Loyalty Score
-> Cleanliness Score
- Q1
- Q2
-> Quality Score
- Q1
- Q2
-> Service Score
- Q1

And a unit hierarchy, EG:

-> Region 1
-> Area 1
- unit1
- unit2
-> Area 2
- unit1
- unit2
-> Region 2
-> Area 1
- unit1
- unit2
-> Area 2
- unit1
- unit2
-> Area 3
- unit1
- unit2

What i am trying to do is create a mdx query that will show the units
on columns (crossjoined with the measures average score and rank) and
the heirchized performance measures on rows. The difficulty i am
having is creating the calculated rank measure. The rank must be
calculated per performance measure across the set of displayed unit.
The result shoud look somthing like this (if i chose the members of
region 2 on my columns):

Area1 Area2 Area3
Average Score|Rank Average Score|Rank Average Score|Rank

Loyalty 54 2 49 3 56 1
Cleanliness 50 3 55 1 51 2
Q1 44 1 41 3 43 2
Q2 ...
Quality ...
Q1
Q2
Service
Q1
Q2

Thanks.

Here's an MDX query on the Foodmart Sales cube, which ranks [Store]
members on the columns by [Store Sales]:

'Rank([Store].CurrentMember,
Order(Extract(StrToSet("Axis(0)"), [Store]),
[Measures].[Store Sales], BDESC))'

Select CrossJoin([Store].[All Stores].[USA].Children,
{[Measures].[Store Sales], [Measures].[SalesRank]}) on columns,
[Product].[All Products].[Drink].[Dairy].[Dairy].[Milk].Children on rows
from [Sales]

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Thanks Deepak... works like a charm (and much more elegant than the
solution i came up with).

Deepak Puri <deepak_puri (AT) progressive (DOT) com> wrote