Report Portal

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

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 200258,241
CY 2003100,172
CY 200445,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 200445,130
CY 2003100,172
CY 200258,241

Tags: mdx, faq

 

2007-2015 VidasSoft Systems Inc.