January 2002 MDX Puzzle

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?

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.