March MDX Puzzle Solution Revealed - 15 Mar 2000

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 COLUMNSFROM [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.