MDX Monday -- Order()

Happy MDX Monday. I thought I'd take a few minutes to improve upon the documentation for the Order() function today.

The first issue I see with the documentation is that it's sorting on something not shown in the data set. It makes it very difficult to see if the function is working correctly. The document states that the "display of products is sorted based on ascending Actual Sales in Qtr1". Let's check if that's true by adding Actual Sales for Qtr1 to the query. I will do this by adding another CrossJoin to the column selection to add the Qtr1 and Qtr2 members. While I do that I need to drop the where clause which filtered on Qtr2. Finally, I will add Actual to the Scenario set.

1:  SELECT  
2:  CrossJoin({[Year].[Qtr1],[Year].[Qtr2]},  
3:    CrossJoin(  
4:        {[Scenario].[Actual],[Scenario].[Budget]},   
5:        {[Measures].[Marketing], [Measures].[Sales]}  
6:      )  
7:    )  
8:  ON COLUMNS,  
9:  Order(  
10:      [Product].Levels(0).Members,  
11:      ([Year].[Qtr1], [Scenario].[Actual])  
12:     )   
13:  ON ROWS  
14:  FROM Sample.Basic;  

This gives me the following output:

This is clearly not sorted on Qtr1 Actual Sales. My hunch is that they've left Sales out of the tuple in the second argument in the Order() function. If I add that in I have:

1:  SELECT  
2:  CrossJoin({[Year].[Qtr1],[Year].[Qtr2]},  
3:    CrossJoin(  
4:        {[Scenario].[Actual],[Scenario].[Budget]},  
5:        {[Measures].[Marketing], [Measures].[Sales]}  
6:      )  
7:    )  
8:  ON COLUMNS,  
9:  Order(  
10:      [Product].Levels(0).Members,  
11:      ([Year].[Qtr1], [Scenario].[Actual], [Measures].[Sales])  
12:    )  
13:  ON ROWS  
14:  FROM Sample.Basic;  

This give me the following output:

Now we're getting somewhere. I'm guessing that originally the query was just using the member Measures to do the sorting. Let's try it:

1:  SELECT  
2:  CrossJoin({[Year].[Qtr1],[Year].[Qtr2]},  
3:    CrossJoin(  
4:        {[Scenario].[Actual],[Scenario].[Budget]},  
5:        {[Measures].[Marketing], [Measures].[Sales], [Measures]}  
6:      )  
7:    )  
8:  ON COLUMNS,  
9:  Order(  
10:      [Product].Levels(0).Members,  
11:      ([Year].[Qtr1], [Scenario].[Actual])  
12:    )  
13:  ON ROWS  
14:  FROM Sample.Basic;  

This gives me the following output:

It looks like my hypothesis was correct.

Now that we're talking about Order(), let me give you one bonus tip. You can sort a report based upon member names or aliases. I just need to use the CurrentMember() function and the desired property.

This query will order my report based upon Product Member names.

1:  SELECT  
2:    {[Measures].[Sales]}  
3:  ON COLUMNS,  
4:  Order(  
5:      [Product].Levels(0).Members,  
6:      [Product].CurrentMember.[MEMBER_NAME]  
7:    )  
8:  ON ROWS  
9:  FROM Sample.Basic  
10:  WHERE ([Year].[Jan],[Scenario].[Actual]);  

The output:

This query will sort based upon Product Aliases.

1:  SELECT  
2:    {[Measures].[Sales]}  
3:  ON COLUMNS,  
4:  Order(  
5:      [Product].Levels(0).Members,  
6:      [Product].CurrentMember.[MEMBER_ALIAS]  
7:    )  
8:  ON ROWS  
9:  FROM Sample.Basic  
10:  WHERE ([Year].[Jan],[Scenario].[Actual]);  

The output:

You can also order based upon other properties such as level and generation number. Happy sorting!