SQL Server Analysis Services 2005/2008 articles,blogs,scripts,FAQs,tools,books

Q: How in the report can I order date dimension members in descending order?

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 count for the last 3 years. In Adventure Works MDX query would be:

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

And the result for this query is:

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

In the results above years are ordered in ascending order 2002, 2003 and 2004. If we want to get results ordered in descending order, 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 in a descending order, we can use the Member_Key property for Numeric_Expression and use DESC flag to order in descending order. So new MDX query is:

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

And new result:

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