Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Salvo(z) SQL

Adam and Jennifer Salvo are IT professionals with over 10 years of diverse experience. Jennifer is a Business Intelligence developer focusing on the Microsoft BI stack (SSIS, SSAS and SSRS). Her prior work experience includes software development, systems analysis, end-user support, training, and SharePoint administration. Adam is a .NET technical lead with a current emphasis on Dev Ops and Windows Azure. His prior work experience includes .NET development, SQL Server administration, and BizTalk development. They also maintain a personal blog at salvoz.com.

MDX Order by Multiple Criteria

The MDX language includes an ORDER function that orders members of a set in ascending or descending order based on a numeric (or string) expression.  The syntax of the Order function is displayed below:

Numeric expression syntax:

Order(Set_Expression, Numeric_Expression [ , { ASC | DESC | BASC | BDESC } ] )

String expression syntax:

Order(Set_Expression, String_Expression [ , { ASC | DESC | BASC | BDESC } ] )

The implementation is straightforward if we want to sort by a single criteria.  But what if we need to sort by multiple criteria? For example, we may first want to sort by Year in descending order and then sort product categories in descending order based on sales.  To accomplish this, we need to use the order function multiple times in a single query.

An example is shown below:

SELECT {[Measures].[Sales]} ON COLUMNS,
       ORDER (
            ORDER ([Dates].[Fiscal Calendar].[Year].MEMBERS*[Items].[Product Category].MEMBERS, [Measures].[Sales], BDESC),
            [Dates].[Fiscal Calendar].CurrentMember.MEMBER_CAPTION, BDESC)
            ON ROWS
FROM [Cube]

It is important to note that the first sort criteria is specified in the outer ORDER function, in this case it is Year.  The inner ORDER function specifies the second sort criteria (Product Category ordered by Sales).

Comments

Leave a comment on the original post [salvoz.com, opens in a new window]

Loading comments...