The MDX language includes an ORDER function that orders members of a set in ascending or descending order based on a numeric (or string) expression. The syntax of the Order function is displayed below:
Numeric expression syntax:
Order(Set_Expression, Numeric_Expression [ , { ASC | DESC | BASC | BDESC } ] )
String expression syntax:
Order(Set_Expression, String_Expression [ , { ASC | DESC | BASC | BDESC } ] )
The implementation is straightforward if we want to sort by a single criteria. But what if we need to sort by multiple criteria? For example, we may first want to sort by Year in descending order and then sort product categories in descending order based on sales. To accomplish this, we need to use the order function multiple times in a single query.
An example is shown below:
SELECT {[Measures].[Sales]} ON COLUMNS,
ORDER (
ORDER ([Dates].[Fiscal Calendar].[Year].MEMBERS*[Items].[Product Category].MEMBERS, [Measures].[Sales], BDESC),
[Dates].[Fiscal Calendar].CurrentMember.MEMBER_CAPTION, BDESC)
ON ROWS
FROM [Cube]
It is important to note that the first sort criteria is specified in the outer ORDER function, in this case it is Year. The inner ORDER function specifies the second sort criteria (Product Category ordered by Sales).