Puzzle #6 should not have presented too much of a challenge, it's intentions were to introduction you to the ORDER function that is available in MDX. Ok, so this is what I started with:
This is a very simple statement that satisfies most of the puzzle requirements. The only one that is does not satisfy is the ordering of the results. To do this I used the ORDER function. The function accepts two arguments. The first is a valid expression that returns a set. In this example it is the set of Product Subcategories. The next argument is the numeric or string expression, which will be used to determine the order. Finally, you must include the direction that your want to order by, DESC or ASC. There are a couple of more, but I am saving them for a later puzzle.
Modifying the above query to include the ORDER function yields the result to this puzzle. See the following:
Stay tuned for Puzzle #7, I promise it will be a little more challenging.
Talk to you soon,
Patrick LeBlanc, SQL Server MVP, MCTS
Visit www.BIDN.com, Bring Business Intelligence to your company.