Sort function of MDX syntax order

Usage scenarios:

Arranges the members of a specified set, optionally preserving or disrupting the original hierarchy.

Grammar:

Numeric expression Syntaxorder (set_expression, numeric_expression [, {ASC | DESC | BASC | BDESC}]) String expression syntaxorder (set_expression, string_expression [, {ASC | DESC | BASC | BDESC}])

Parameters:

Set_Expression

A valid Multidimensional Expressions (MDX) expression that returns a set.

Numeric_Expression

A valid numeric expression, typically a Multidimensional Expressions (MDX) expression that returns the cell coordinates of a numeric value.

String_Expression

A valid string expression that is typically a valid Multidimensional Expressions (MDX) expression of cell coordinates that return a number represented as a string.

Description

If ASC or DESC is specified, the Order function first arranges the members according to their position in the hierarchy, and then sorts each level. If BASC or BDESC is specified, the order function arranges all the members of the set regardless of the hierarchy. If no flag is specified, ASC is the default.

If the Order function is used for a collection of its two or more hierarchies that perform cross-product, and the DESC flag is used, only the members of the last hierarchy in the collection are sorted. This differs from Analysis Services 2000, which sorts all the hierarchies in the collection. Instance:

Sort by dimension.

SelectNon EmptyOrder( {[Customer].[ City].[ All]. Children},[Customer].[ City]. Currentmember.name,DESC)  on 1,[Measures].[Internet Sales Amount]  on 0 from [Adventure Works];

The results are as follows:

Sort by measure value

SelectNon EmptyOrder( {[Customer].[ City].[ All]. Children},[Measures].[Internet Sales Amount],DESC)  on 1,[Measures].[Internet Sales Amount]  on 0 from [Adventure Works];

The results are as follows:

Sort function of MDX syntax order