Sort a MDX Dataset by Multiple Criteria

Recently I work on a SSRS report that needs a parameter to return a set of user to be selected as the filter for the report.  The list of user has to be sorted by Department Code and User’s name.

In MDX, sorting a dataset based on one criterion is directly supported
through the Order( ) function.  However, it doesn’t support multiple
criteria for sorting explicitly.  In order to sort the dataset with multiple criteria, some tricks will be needed to complete the task.

Sample Data

Here is how we could return a user list dataset that are sorted by Department Code and User Name.

Trick One:

Concatenate the two attributes into one and then use Order() function to sort it accordingly. 

WITH MEMBER [Measures].[ParameterCaption] 

AS [User].[User].CURRENTMEMBER.MEMBER_CAPTION 

MEMBER [Measures].[ParameterValue] 

AS [User].[User].CURRENTMEMBER.UNIQUENAME 

MEMBER [Measures].[Department] 

AS [User].[User].CURRENTMEMBER.Properties( "Department Code")

SELECT {  [Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[Department] } ON COLUMNS ,  

{

    ORDER(

                [User].[User].Children ,

                [User].[User].CURRENTMEMBER.Properties( "Department Code") + [User].[User].CURRENTMEMBER.MEMBER_CAPTION, 

                BASC

                )

} ON ROWS

FROM [Test]

As you could see, the result set returned has been sorted by Department first and then Username in alphabetical ascending order.

You may use this trick when the sorting order of both members are in the same direction either ascending or descending.

Trick TWO

When the sorting criteria are in different alphabetical order i.e. one in ascending order and the other in descending order, we will need to use two ORDER() functions.

WITH MEMBER [Measures].[ParameterCaption] 

AS [User].[User].CURRENTMEMBER.MEMBER_CAPTION 

MEMBER [Measures].[ParameterValue] 

AS [User].[User].CURRENTMEMBER.UNIQUENAME 

MEMBER [Measures].[Department] 

AS [User].[User].CURRENTMEMBER.Properties( "Department Code")

SELECT {  [Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[Department] } ON COLUMNS ,  

{

 ORDER( 

        ORDER(

                [User].[User].Children ,

                [User].[User].CURRENTMEMBER.MEMBER_CAPTION, 

                BASC

                ),

    [User].[User].CURRENTMEMBER.Properties( "Department Code"),

    BDESC

    )

} ON ROWS

FROM [Test]

As you could see, the result set returned now will be sorted by the Department Code in descending order first, then the Username in ascending order.

Reference

http://msdn.microsoft.com/en-us/library/ms145587.aspx