MDX ORDER Riddle

I just found this question on MSDN forums:

I wonder if anyone has run into this before.

I’m trying to produce a report in which all rows are sorted by a specific
column. For example the following works fine:

WITH
SET [RowSet] AS [Product].[Subcategory].Levels(1).Members
SET [ColumnSet] AS [Date].[Calendar].Levels(1).Members
Select
Order([RowSet], [ColumnSet].Item(0), BDESC) ON ROWS,
[ColumnSet] ON COLUMNS
FROM [Adventure Works]

All product sub-categories are sorted by CY 2001.

However when the rows and columns of the report contain related non-leaf
attributes of the same hierarchy then the sort does nothing, as illustrated
in this example:

WITH
SET [RowSet] AS [Product].[Subcategory].Levels(1).Members
SET [ColumnSet] AS [Product].[Category].Levels(1).Members
Select
Order([RowSet], [ColumnSet].Item(0), BDESC) ON ROWS,
[ColumnSet] ON COLUMNS
FROM [Adventure Works]

Is such sort possible at all, may be using different MDX functions, or is
this a known limitation for related attributes?

Interestingly enough it works when the leaf-level attribute
([Product].[Product]) is mixed with a non-leaf level attribute (e.g.
[Product].[Category]).

If you think about it for a while, you’ll see that it is not easy to comprehend what is happening here at a first glance. Some light is shed on it by Deepak Puri on MSDN forums – please mark his answer as useful if you think it is:

http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/cde6f082-6dbd-4368-a20a-e64ea21de2bc

You could also write:

 WITH
SET [RowSet] AS [Product].[Subcategory].Levels(1).Members
SET [ColumnSet] AS [Product].[Category].Levels(1).Members
Select
Order([Product].[Subcategory].Levels(1).Members AS a, ([ColumnSet].Item(0), a.CurrentMember), BDESC) ON ROWS,
[ColumnSet] ON COLUMNS
FROM [Adventure Works]

So, the ordering tuple is defaulting to the All member of the Subcategories – therefore, we are trying to order the RowSet by the same value. If we do that on a non-directly related attribute, the ordering expression is in fact getting evaluated within the current RowSet member..which seems natural. So all in all, it looks like a peculiarity in the ORDER function – most likely because it would make some sense in terms of performance. Please let me know if you know why it happens.