Sunday, March 18, 2012

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

image

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.


image
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.


image


Reference


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

2 comments:

  1. I read any article in this topic.As similarly it process too many sector at time so they can not handle this for any longer.So i really admire this matters.

    ReplyDelete
  2. Good news.This is a great post. I like this topic.This site has lots of advantage. I found many interesting things from this site. It helps me many away..So i want some information for sharing this side with some of my friend. Thanks
    notebook computer

    ReplyDelete