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.
Here is how we could return a user list dataset that are sorted by Department Code and User Name.
Concatenate the two attributes into one and then use Order() function to sort it accordingly.
As you could see, the result set returned has been sorted by Department first and then Username in alphabetical ascending order.
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.
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.