SSAS-Info.com

Q: How in MDX query can I get top 3 sales years based on order quantity?

A: By default Analysis Services returns members in an order specified during attribute design. Attribute properties that define ordering are "OrderBy" and "OrderByAttribute". Lets say we want to see order counts for each year. In Adventure Works MDX query would be:

SELECT {[Measures].[Reseller Order Quantity]} ON 0
, [Date].[Calendar].[Calendar Year].Members ON 1
  FROM [Adventure Works];

And the result for this query is:

	Reseller Order Quantity
CY 2001	10,835
CY 2002	58,241
CY 2003	100,172
CY 2004	45,130

In the results above years are ordered in ascending order 2001, 2002, 2003 and 2004. If we want to get results ordered based on [Reseller Order Quantity] measure, we can use MDX function ORDER. This function has following parameters (copied from BOL):

Numeric expression syntax
Order(Set_Expression, Numeric_Expression [ , { ASC | DESC | BASC | BDESC } ] )

String expression syntax
Order(Set_Expression, String_Expression [ , { ASC | DESC | BASC | BDESC } ] )

As we want to order dimension members based on measure, we will use [Measures].[Reseller Order Quantity] for Numeric_Expression and we will use DESC flag to order in descending order. So new MDX query is:

SELECT {[Measures].[Reseller Order Quantity]} ON 0
 , ORDER([Date].[Calendar].[Calendar Year].Members, [Measures].[Reseller Order Quantity], DESC) ON 1
  FROM [Adventure Works];

And new result:

	Reseller Order Quantity
CY 2003	100,172
CY 2002	58,241
CY 2004	45,130
CY 2001	10,835

To get top 3 members, we will use function Head(<Set>,<member count>) that will return first specified number of members from the set. Updated MDX query:

SELECT {[Measures].[Reseller Order Quantity]} ON 0
 , Head(ORDER([Date].[Calendar].[Calendar Year].Members, [Measures].[Reseller Order Quantity], DESC),3) ON 1
  FROM [Adventure Works];

And new updated result:

	Reseller Order Quantity
CY 2003	100,172
CY 2002	58,241
CY 2004	45,130

And Romuald Coutaud suggested even shorter query to do the same thing. Instead of Head + Order we can use TopCount function. New query is:

SELECT
{[Measures].[Reseller Order Quantity]} ON 0,
TopCount([Date].[Calendar].[Calendar Year].Members,3, [Measures].[Reseller Order Quantity]) ON 1
FROM [Adventure Works];

Results are exactly the same. Thank you Romuald Coutaud!