posted 8/27/2010 by PatrickLeBlanc - Views: [876]
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
Founder www.TSQLScripts.com and www.SQLLunch.com.
Visit www.BIDN.com, Bring Business Intelligence to your company.
Here is my solution once again:
SELECT [Measures].[Reseller Sales Amount] ON COLUMNS, NONEMPTY( ORDER( [Product].[Subcategory].CHILDREN, [Measures].[Reseller Sales Amount], DESC ) ) ON ROWS FROM [ADVENTURE WORKS]
Thanks Dustin. Hopefully I can challenge you a little more with the next puzzle .
Bring it, foo!
:) I'm looking forward to it too! Of course I'll probably be a little stumped. It's been over a month since I've done any MDX. Time to get back to it!
Here is the MDX Puzzles index: http://ssas-wiki.com/w/Articles#MDX_Puzzles
Interesting series and learnt how to build the MDX queries, thanks a millions!!!
By default TOPCOUNT sorting in descending & Order function doing in ascending.
Thanks once again :)