Search code examples
ssasmdx

ORDER on mdx query is not working

I'm having a problem to order a column with a MDX query.

this is my query:

with
     Member
     [Measures].[MemberUN] as '[Employee].[Employee Department].CurrentMember.UNIQUE_NAME'
     member
     [Measures].[Memberlevel] as '[Employee].[Employee Department].CurrentMember.LEVEL.ordinal'
Select
ORDER([Employee].[Employee Department].allmembers,[Measures].[Memberlevel]) on 1,
{[Measures].[MemberUN],[measures].[MemberLevel]} on 0
From [Adventure Works]

For some reason it's not ordering the table with the level ordinal.

Thank!

Solution

  • The default setting for sorting of MDX does not break the level hierarchy, i. e. it keeps the main order of first the parent, then its children. It only applies the sorting to the children of the same parent. If you want to break the hierarchy in your sorting, use BASC ("breaking sort ascending") instead of the default setting ASC, or use BDESC instead of DESC. The following query sorts in the way that you probably intended:

    with
     Member
     [Measures].[MemberUN] as '[Employee].[Employee Department].CurrentMember.UNIQUE_NAME'
     member
     [Measures].[Memberlevel] as '[Employee].[Employee Department].CurrentMember.LEVEL.ordinal'
Select
ORDER([Employee].[Employee Department].allmembers, [Measures].[Memberlevel], BASC) on 1,
{[Measures].[MemberUN],[measures].[MemberLevel]} on 0
From [Adventure Works]