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 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