If you have a set of members defined in the following way:
CREATE SET \[Sales\].\[97Quarters\] AS '\{ \[1997\].Children \}'
how can you write an MDX query that reverses the order of this set?
WITH SET \[Reverse\] AS ' Generate( \[97Quarters\], \{ \[97Quarters\].item( Count( \[97Quarters\] ) - Rank( \[Time\].CurrentMember, \[97Quarters\] ) ) \} )'
SELECT \[Reverse\] ON COLUMNS
FROM \[Sales\]
This answer demonstrates a couple of good MDX tricks. It shows how to use the keyword item to index into a set and how to use the Rank function to find a member's position in a set.
The Generate function evaluates its second parameter for each item in the set \[97Quarters\]. For example, the first evaluation of the second parameter is with \[1997\].\[Q1\]. Count(\[97Quarters\]) is always 4. The Rank( \[1997\].\[Q1\], \[97Quarters\] ) is 1 because Q1 is the first member of the \[97Quarters\] set. So the Generate function evaluates \[97Quarters\].item( 4 1 ) and returns the 4th item (Q4) in the set because the item property uses a zero-based index.
