Ordering data queried from a database is a common operation. SQL makes it very easy to receive and order relational data by providing the ORDER BY clause in a SELECT statement.
However, ordering data is not as easy when querying SSAS database through the use of MultiDimensional eXpressions (MDX). The default order for an attribute is defined by the value of its key (KeyColumns property of DimentionAttribute). For custom ordering, one usually adds a new column to the underlying data warehouse and makes it part of KeyColumns property. This approach works well when only one sort order is required. There are scenarios when more flexible approach is necessary. Microsoft provides the MDX ORDER function to sort a set of members at runtime. The most common use case for the ORDER function is to sort an attribute based on the value in the cube’s measures, for example, to sort by company name based on the total sales amount.
A simpler use case is to sort members in an alphabetical order. This is especially useful when generating a dropdown list for SSRS report’s parameters. The second parameter required of the ORDER function is a String Expression which is described as ‘A valid string expression that is typically a valid MultiDimensional eXpressions (MDX) expression of cell coordinates that return a number expressed as a string’. The correct MDX expression to feed to the ORDER function is: [Dimension].[Attribute].CurrentMember.Name
The full MDX query looks like:
