How to sort specific Hierarchy Level in OLAP (SSAS) via MDX

Question

I'm using SSAS OLAP and I want to apply sorting of the levels of a hierarchy.

I know that I can sort the whole hierarchy via ORDER function (I have some issues when I'm trying to apply DESC sorting on the whole hierarchy), but what I really want to achieve is sorting of a specific level. For example in the [Date].[Calendar] hierarchy (Adventure Works Cube), I want to have ASC sorting of years, DESC sorting of Quarter, ASC sorting of Months, etc. I do not want to break the hierarchy (using BASC or BDESC), I just need them sorted on the same level. Do you have an idea if this is possible at all, as I was unable to find anything simillar?

Answer 1

What you can do is crossjoining the ordered correspoding attribute hierarchies. Could you verify the results of the following:

select
[Measures].[Internet Sales Amount] on 0,

Order(
[Date].[Calendar Year].[Calendar Year].MEMBERS,
[Measures].[Internet Sales Amount]
,ASC)
*
Order(
[Date].[Calendar Quarter of Year].[Calendar Quarter of Year].MEMBERS,
[Measures].[Internet Sales Amount]
,DESC)
*
Order(
[Date].[Calendar].[Month].MEMBERS,
[Measures].[Internet Sales Amount]
,ASC)

ON 1
from [Adventure Works]

Including the all members could help ([Date].[Calendar Year] instead of [Date].[Calendar Year] in case of the first crossjoin set)

Philip,

Answer 2

The following is a working solution, if not a really nice one:

WITH MEMBER Measures.[year] as
            [Date].[Calendar].CurrentMember.Properties('Key0', typed)
     MEMBER Measures.[qtr or mth] as
            [Date].[Calendar].CurrentMember.Properties('Key1', typed)
     MEMBER Measures.[sortKey] as
            CASE
                WHEN [Date].[Calendar].CurrentMember.Level IS [Date].[Calendar].[Calendar Year] THEN
                     Measures.[year] * 1000
                WHEN [Date].[Calendar].CurrentMember.Level IS [Date].[Calendar].[Calendar Quarter] THEN
                     Measures.[year] * 1000 + (5 - Measures.[qtr or mth]) * 100
                ELSE // month
                     Measures.[year] * 1000 + (4 -  Int((Measures.[qtr or mth] - 1) / 3)) * 100 + Measures.[qtr or mth]
            END
SELECT {Measures.[year], Measures.[qtr or mth], Measures.[sortKey]}
       ON COLUMNS,
       Order(
               [Date].[Calendar].[Calendar Year]
               +
               [Date].[Calendar].[Calendar Quarter]
               +
               [Date].[Calendar].[Month]
               ,
               Measures.[sortKey]
               ,BASC
       )
       ON ROWS
FROM [Adventure Works]

The measures on the columns are just for clarification how it works. And the main logic is in the generation of the sortKey measure which generates an integer key as follows: four digits for the year, one digit for the backwards quarter (Q4 -> 1, ... Q1 -> 4), two digits for the month. Then, using that, it is straightforward to call Order to return the sorted set.

Answer 3

It turned out that it is impossible to achieve the desired effect - there's no such MDX query that can sort different levels with different sorting type. More information is available in this thread.