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\] ) ) \} )'
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.

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.