A Simple MDX Example Using Adventure Works

Posted on by 
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]

Output(truncated):

CY 2001 CY 2002 CY 2003 CY 2004 CY 2006
Mountain Bikes $4,545,336.51 $9,190,838.09 $8,854,263.03 $3,902,246.74 (null)
Road Bikes $2,850,012.12 $10,765,176.58 $11,294,381.37 $4,448,636.90 (null)
Mountain Frames $371,587.79 $1,400,331.68 $2,067,908.64 $873,844.03 (null)
Road Frames $243,887.19 $1,618,391.51 $1,631,377.27 $356,197.37 (null)
Jerseys $28,255.57 $110,243.77 $290,004.73 $150,804.63 (null)

If you change the value of 

     Order( [RowSet], [ColumnSet].Item(0), BDESC )

to 

     Order( [RowSet], [ColumnSet].Item(1), BDESC )

then the sorted column changes.
Also if you change the parameter of the Levels in the following section: 

SET [RowSet]    AS [Product].[Subcategory].Levels(1).Members
SET [ColumnSet] AS [Date].[Calendar].Levels(1).Members

to

SET [RowSet]    AS [Product].[Subcategory].Levels(0).Members
SET [ColumnSet] AS [Date].[Calendar].Levels(0).Members

then this collapses down to a single number so depending on how many objects exist in the dimensions hierarchy you can use higher and higher numbers for the Levels to get more columns or rows (I think). 

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

This now sorts by the values in the rows and columns the values used in the columns.

Leave a Reply