Querying Data Warehouses - Data Warehouse Systems: Design and Implementation - page 198

set of countries can be ordered regardless of the hierarchy in the following
way:

SELECT Measures.MEMBERS ON COLUMNS,
ORDER(Customer.Geography.Country.MEMBERS,
Customer.Geography.CURRENTMEMBER.NAME, BASC) ON ROWS
FROM Sales

Here, the property NAME returns the name of a level, dimension, member,
or hierarchy. A similar property, UNIQUENAME , returns the corresponding
unique name. The answer to this query will show the countries in alphabetical
order, that is, Argentina, Australia, Austria, and so on.
It is often the case that the ordering is based on an actual measure. In
the query above, the countries can be ordered based on the sales amount as
follows:

SELECT Measures.MEMBERS ON COLUMNS,
ORDER(Customer.Geography.Country.MEMBERS,
Measures.[Sales Amount], BDESC) ON ROWS
FROM Sales

Ordering on multiple criteria is dicult to express in MDX. Indeed, unlike
in SQL, the ORDER function allows a single expression for sorting. Suppose,
for instance, that we want to analyze the sales amount by continent and
category. Further, suppose that we want to order the result first by continent
name and then by category name. For this we need to use the GENERATE
function:

SELECT Measures.[Sales Amount] ON COLUMNS,
NON EMPTY GENERATE(
ORDER( Customer.Geography.Continent.ALLMEMBERS,
Customer.Geography.CURRENTMEMBER.NAME, BASC ),
ORDER( { Customer.Geography.CURRENTMEMBER } *
Product.Categories.Category.ALLMEMBERS,
Product.Categories.CURRENTMEMBER.NAME, BASC ) ) ON ROWS
FROM Sales

In the first argument of the GENERATE function, we sort the continents
in ascending order of their name. In the second argument, we cross join the
current continent with the categories sorted in ascending order of their name.
This query will give the following answer:

Sales Amount
Europe Beverages $120,361.83
Europe Condiments $60,517.12
Europe Confections $95,690.12
Europe Dairy Products $137,315.75
Europe Grains/Cereals $48,781.57

··· ··· ···