Problem:
Select[Measures].[Sales Amount] on Columns,
([Product].[Category].members) on 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];
