Home      Functions     Common Solutions      Contact     






MDX Functions:
 
MDX Functions

 
Common Solutions

Common Solutions
 
Resource Links

MDX Books
MDX Related Software
MDX Related Blogs
 
Other Links

Contact Us
 
Microsoft SQL Server Analysis Services Function Page
Function Name: Order
Category: Set
Description: The Order function returns a sorted set of members.  There is optional flag that allows you to honor or break hierarchies.
Syntax: ORDER( «Set», {«String Expression» | «Numeric Expression»}[, ASC | DESC | BASC | BDESC] )
Technet Link: MDX Order Function




The following query shows the Internet Sales Amount for the children of August 2003 member using the default sort order of the dimension.
 

SELECT
      {[Measures].[Internet Sales Amount]} ON COLUMNS,
      {[Date].[Calendar].[Month].[August 2003].Children} ON ROWS
FROM
      [Adventure Works]
 
 

 
The query below uses the Order function to sort the children (in descending order) of the August 2003 member by Internet Sales Amount.
 

SELECT
      {[Measures].[Internet Sales Amount]} ON COLUMNS,
      ORDER({[Date].[Calendar].[Month].[August 2003].Children}, [Measures].[Internet Sales Amount], DESC) ON ROWS
FROM
      [Adventure Works]
 
 

 
This query uses the Order function to sort the children of the August 2003 and September 2003 members by Internet Sales Amount honoring the existing hierarchy.
 

SELECT
      {[Measures].[Internet Sales Amount]} ON COLUMNS,
      ORDER({[Date].[Calendar].[Month].[August 2003].Children} + {[Date].[Calendar].[Month].[September 2003].Children}, [Measures].[Internet Sales Amount], DESC) ON ROWS
FROM
      [Adventure Works]
 
You will see the results are first ordered by the Month hierarchy, then by the members.
 

 
This query uses the Order function to sort the children of the August 2003 and September 2003 members by Internet Sales Amount breaking the existing hierarchy.
 

SELECT
      {[Measures].[Internet Sales Amount]} ON COLUMNS,
      ORDER({[Date].[Calendar].[Month].[August 2003].Children} + {[Date].[Calendar].[Month].[September 2003].Children}, [Measures].[Internet Sales Amount], BDESC) ON ROWS
FROM
      [Adventure Works]
 
You will see the results are ordered by the members without regard for the Month hierarchy.
 

 
 

 
Copyright 2008 by MDXpert.com.
 
Terms of Use                              Privacy Statement
 
Site Powered By - WinHost.Com