Order

The MDX Order function for Essbase sorts members of a set in order based on an expression.

Syntax

Order ( set, string_expr | numeric_value_expression [,BASC | BDESC] )

Parameters

set

The set to sort.

string_expr

String sorting criteria.

numeric_value_expression

Numeric sorting criteria (see MDX Grammar Rules).

BASC

If this keyword is used, the returned set is arranged in ascending order. Ascending order is the default even if no keyword is used.

BDESC

If this keyword is used, the returned set is arranged in descending order.

Notes

This function ignores missing values.

Example

The following query displays budgeted Sales and Marketing in Qtr2, and the display of products is sorted based on ascending Actual Sales in Qtr1.

SELECT
 CrossJoin(
           {[Scenario].[Budget]}, 
           {[Measures].[Marketing], [Measures].[Sales]}
          )
ON COLUMNS,
 Order(
        [Product].Levels(0).Members,
        ([Year].[Qtr1], [Scenario].[Actual])
      ) 
ON ROWS
FROM Sample.Basic
WHERE ([Year].[Qtr2])

This query returns the grid:

Table 4-119 Output Grid from MDX Example

(axis)	Budget	Budget
(axis)	Marketing	Sales
400-30	510	3240
100-30	450	3400
300-20	550	3800
200-40	310	2830
200-30	550	4060
100-20	1160	8800
100-20	1160	8800
200-10	2090	10330
400-20	880	6590
300-10	1450	10080
300-30	1080	7880
300-30	1080	7880
400-10	790	7410
200-20	1080	9590
200-20	1080	9590
100-10	1800	17230