January 2002 MDX Puzzle Solution Revealed


When creating a financial report such as an income statement, you need to display the subtotals (which are parent members of a dimension) at the bottom of the statement—after the details (which are children). You can use the MDX Hierarchize() function with the POST option to force parent members to appear after their children. The following example shows the Hierarchize() function on the FoodMart 2000 Sales cube:

WITH SET MySet AS '\{CA,CA.Children,\[OR\],\[OR\].Children\}'
SELECT Hierarchize(MySet, POST) ON Columns
FROM sales
WHERE \[Sales Count\]

How can you change this query to sort the set MySet in ascending order while making sure the parents appear after their children?

Thanks to Shahar Prish of Microsoft for providing the clever answer that Listing A shows. First, he sorted the items in descending order while preserving peer groupings (i.e., keeping children of a common parent together). Then, he used a Generate() function to reverse the order of the set. The result maintains the peer groupings, keeps the items in ascending order, and places the parents after the children. Notice that Shahar uses the AS keyword to name the sorted set MySetIterator. He also uses the Count and Current properties on the named set.

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.