Search code examples
ssasmdxssas-2012

Order hierarchy members by natural order

I have a SSAS cube with the following dimensions:

Time
    DayName <- Hierarchy

Item
    Description <- Hierarchy

In the Measures dimension I have a measure for the LostAmount.

I want to query the cube so that I can get the sum of lost amount per week day. The following query works:

select
    except(Time.DayName.members, {Time.DayName.[All]}) on 0,
    except(Item.Description.members, {Item.Description.All}) on 1
from Cube

But when returning the results the order of columns is not the natural order of week days (i.e. the order of columns is Friday, Monday, Sunday etc).

Query results.

How can I have the result columns in the natural order of week days i.e. Monday, Tuesday etc.?

Solution

  • If you look at your screenshot, the days in the Time.DayName level are being sorted in natural order: it's the alphabetical order of the member names.

    There's no way that SSAS can "know" what the order you want is. I'd suggest adding a Key column to this dimension, numbering the days in the order you want, and changing the dimension attribute's OrderBy property to Key rather than Name. (Perhaps you already do have a key: but OrderBy=Name will naturally order the members by their name).

    Addition: There is a dimension attribute Type property in SSAS, which can be set to "Day of Week" - but I've never used it, and don't know whether it would correctly detect that your member names are the names of weekdays. Or whether it will order your week starting on the "right" day that you want to start your week from. You could try setting it and seeing what happens.