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

Display the No Row Information in the SSRS Report Footer

Recently there is a reader asked about how to display row count information in page footer if no data returned.  Since RowNumber() function can only be used in the report body to retrieve the number of rows in the specified scope, we could not use it in either the Header or Footer area.

We could use other counting function such as CountRows() or Count() to retrieve the number of rows retuned for the dataset.

Here is what we can do:

• Add a textbox in the report footer

• Use CountRows() function passing in the dataset name in the expression for the textbox as:

Result

AS you could see, the report footer shows the number of row returned by the dataset now.

Reference:

http://msdn.microsoft.com/en-us/library/dd255237(v=sql.110).aspx

http://msdn.microsoft.com/en-us/library/ms157163(v=sql.110).aspx

http://msdn.microsoft.com/en-us/library/ms156330(v=sql.110).aspx

Use DAX Earlier Function in Calculated Column to Mimic Group By Clause

It is very easy to group or slice the measures in the Pivot table by adding a attribute to the row or slicer.  But, how do we create the same effect in the Calculated Column?

Here is the Sample Data

If we treat this table as a SQL table, we could use the following T-SQL query to find the total queued time for each queue per date.

SELECT [Date], [QueueName], SUM([QueuedTime]) AS [TotalQueuedTime]

FROM CallDetail

GROUP BY [Date], [QueueName]

In PowerPivot, we could use the EARLIER function to achieve the same effect.  EARLIER is a very useful DAX function when you want to use a certain value as input to produce a calculation.

Syntax:

EARLIER(<column>, <number>) 

Solution:

Step 1: Calculate the total of the QueuedTime by using SUM(), and then use FILTER() to obtain the subset of the table for the Date and the QueueName needed.

=CALCULATE(

    SUM(CallDetail[QueuedTime]), 

    FILTER(CallDetail, CallDetail[Date] = ?) 

           && CallDetail[QueueName]= ?

            )

    )

Step 2: In the question mark area, we need to plug in a value/expression to restrict the rows been returned.

If we set they to the static value as “2012-02-18” and “Queue A”, the Sum of QueuedTime will be 900 for all the rows which won’t be correct for the Queue not equal to “Queue A” or the Date not equal to “2012-02-18”.

Step 3: To pass in correct expression, we could use the EARLIER() as the input for the certain value (i.e. Date or Queue name) that the calculation will be based on it. 

=CALCULATE(

    SUM(CallDetail[QueuedTime]), 

    FILTER(CallDetail, CallDetail[Date] = EARLIER(CallDetail[Date])

           && CallDetail[QueueName]= EARLIER(CallDetail[QueueName])

            )

    )

You can see the result now is correct based on the Date and QueueName

The EARLIER function is based on the current row context. If there is no row context, it will return an error.

Implementation Example:

Here is a scenario that I have encountered: Create a measure that contains the cumulated Queued Time only for the last queue.

To solve it, I add the following condition to the pervious formula based on the LastQueue flag

=IF(CallDetail[LastQueue] = 1, 

    CALCULATE(

        SUM(CallDetail[QueuedTime]), 

        FILTER(CallDetail, CallDetail[Date] = EARLIER(CallDetail[Date]) 

           && CallDetail[CallID]=EARLIER(CallDetail[CallID])

            )

        )

     ,BLANK()

    )

This way, only the last queue will have the total queued time in the column.

When summing up in the Pivot table, the total of QueueTimeHandled per day will now match the total of QueuedTime.

My colleague Javier Guillen has another nice blog entry to discuss how to use the EARLIER() in DAX measure. You may want to check it out.

Reference:

http://technet.microsoft.com/en-us/library/ee634551.aspx