MDX Ranking Problem (new to MDX)

Discussion in 'microsoft.public.sqlserver.olap' started by Rob Rasile, Nov 17, 2004.

  1. Rob Rasile

    Rob Rasile Guest

    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.
     
    Rob Rasile, Nov 17, 2004
    #1
    1. Advertisements

  2. Rob Rasile

    Deepak Puri Guest

    Here's an MDX query on the Foodmart Sales cube, which ranks [Store]
    members on the columns by [Store Sales]:
    With Member [Measures].[SalesRank] as
    '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
     
    Deepak Puri, Nov 19, 2004
    #2
    1. Advertisements

  3. Rob Rasile

    Rob Rasile Guest

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

     
    Rob Rasile, Nov 19, 2004
    #3
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.