MDX - Ranking And Ordering By a Measure Value

The following example is based on an Adventure Works cube and shows you how to create a measure that ranks countries according to sales revenue.

It implements the following MDX functions:

• Rank
• CurrentMember
• Children
• Order

Rank by Largest to Smallest

The MDX WITH MEMBER statement creates a temporary measure for the rank value based on total internet slaes in the calendar year 2006, and the associated SELECT part uses the rank to order the countries.

--Rank without ordering
WITH MEMBER [Measures].[Rank] AS
	Rank([Customer].[Customer Geography].CurrentMember,
	     [Customer].[Customer Geography].[All Customers].Children,
	     [Measures].[Internet Sales Amount]
	    )
SELECT {[Measures].[Internet Sales Amount], [Measures].[Rank]} ON 0,
       {[Customer].[Customer Geography].[All Customers].Children} ON 1
FROM [Adventure Works]
WHERE [Date].[Calendar].[Calendar Year].&[2006]

The WITH MEMBER statement is required here as the Rank function returns a vaue and not a tuple or set. The only way we work with values in MDX is as measures, therefore, we must create a measure from the Rank result.

The MDX Rank function takes up to three arguments.

Rank(Tuple_Expression, Set_Expression [ ,Numeric Expression ] )

• The first identifies the member that will be reordered
• The second is the set of members that will be reordered
• The third (optional) argument is the value that determines the order

So, in the above example it is the country members that we are ranking within the set of all countries (top level of the [Customer Geography] hierarchy) based on descending sales amount value.

The default behaviour is to rank from largest value to smallest. The resulting set will not be ordered according to the rank

Here is the output from the above query:

You can see in the screenshot that the rank values are correct, but the results are not in rank order.

We can add an Order function to the SELECT statement to return the results in rank order as shown in the following example;

WITH MEMBER [Measures].[Rank] AS
    Rank([Customer].[Customer Geography].CurrentMember, 
         [Customer].[Customer Geography].[All Customers].Children,
         [Measures].[Internet Sales Amount]
        )
SELECT {[Measures].[Internet Sales Amount], [Measures].[Rank]} ON 0,
       {Order([Customer].[Customer Geography].[All Customers].Children, [Measures].[Rank], ASC)} ON 1
FROM [Adventure Works]
WHERE [Date].[Calendar].[Calendar Year].&[2006]

Here is the screenshot showing the ordered results:

The third argument is optional. If it is not provided then the ranking is based on the order of the set passed in to the Rank function:

--Rank with no measure specified
WITH MEMBER [Measures].[Rank] AS
	Rank([Customer].[Customer Geography].CurrentMember,
	     [Customer].[Customer Geography].[All Customers].Children
            )
SELECT {[Measures].[Internet Order Quantity], [Measures].[Rank]} ON 0,
	   {Order([Customer].[Customer Geography].[All Customers].Children, [Measures].[Rank], ASC)} ON 1
FROM [Adventure Works]
WHERE [Date].[Calendar].[Calendar Year].&[2006]

This version of the query will rank by the alphabetical order of the country members as that is the default order for the country members definied in the Customer dimension. 

You can see from the screenshot above that the rank value follows the alphabetical country name and not the sales amount.

Ranking By Smallest to Largest

The Rank function does not have an argument to determine the order for the ranking, but we can use the behaviour of rank without the third argument to achieve ranking from smallest to largest:

WITH MEMBER [Measures].[Rank] AS
	Rank([Customer].[Customer Geography].CurrentMember,
             Order([Customer].[Customer Geography].[All Customers].Children,[Measures].[Internet Sales Amount], ASC)
	    )
SELECT {[Measures].[Internet Sales Amount],
        [Measures].[Rank]} ON 0,
       {Order([Customer].[Customer Geography].[All Customers].Children,
	      [Measures].[Rank], ASC)} ON 1
FROM [Adventure Works]
WHERE [Date].[Calendar].[Calendar Year].&[2006]

If you would like to learn more about MDX queries and programming why not take a look at the SQL Server MDX training course - Introduction to SQL Server MDX course.