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 | $4,545,33 | $9,190,83 | $8,854,26 | $3,902,24 | (null) |
| Road Bike | $2,850,01 | $10,765,1 | $11,294,3 | $4,448,63 | (null) |
| Mountain | $371,587. | $1,400,33 | $2,067,90 | $873,844. | (null) |
| Road Fra | $243,887. | $1,618,39 | $1,631,37 | $356,197. | (null) |
| Jerseys | $28,255.57 | $110,243. | $290,004. | $150,804. | (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.
