How to order the MDX query results?

Problem:
Select[Measures].[Sales Amount] on Columns,

      ([Product].[Category].memberson Rows

From  [AdventureWorks]

where [Date].[Calendar Year].&[2003]

 

It returns the result as below:

                               Sales Amount

All                          9791060.29770049

Bikes                      9359102.61770052

Components           (null)

Clothing                 138247.970000006

Accessories            293709.709999959

Unknown               (null)

 

But I need it ordered by category name as below:

                               Sales Amount

All                          9791060.29770049

Accessories            293709.709999959

Bikes                      9359102.61770052

Clothing                 138247.970000006

Components           (null)

Unknown               (null)

 

Solution:
Option 1 - Use the Order function:

Select[Measures].[Sales Amount] on Columns,

      Order([Product].[Category].members, [Product].[Category], desc) on Rows

From  [AdventureWorks]

where [Date].[Calendar Year].&[2003];

Notice, it's DESC, not ASC. Why so? Because The Order function can either be hierarchical (as specified by using the ASC or DESC flag) or nonhierarchical (as specified by using the BASC or BDESC flag; the B stands for "break hierarchy"). If ASC or DESC is specified, the Orderfunction first arranges the members according to their position in the hierarchy, and then orders each level. If BASCor BDESC is specified, the Order function arranges members in the set without regard to the hierarchy. In no flag is specified, ASC is the default. The members in the hierarchy are not necessarily arranged alphabetically. See BOL for details.

 

Option 2 - Use the AllMembers function

SELECT

  [Measures].[Sales Amount] on Columns,

  [Product].[Category].ALLMEMBERS ON ROWS

FROM [Adventure Works]

where [Date].[Calendar Year].&[2003];