Order
Arranges members of a specified set, optionally preserving or breaking the hierarchy.
Syntax
Order(«Set», {«String Expression» | «Numeric Expression»}
[, ASC | DESC | BASC | BDESC])
Remarks
There are two varieties of Order: hierarchized (ASC or DESC) and nonhierarchized (BASC or BDESC, where B stands for Break hierarchy). The hierarchized ordering first arranges members according to their position in the hierarchy. Then it orders each level. The nonhierarchized ordering arranges members in the set without regard to the hierarchy. In the absence of an explicit specification, ASC is the default.
Example
This example
Order(SampleSet, ([1995], Sales), DESC)
hierarchizes all members and sorts each level according to Sales. Sales are compared at the highest level when the sorted list is constructed. Therefore, if the sum of Sales in all California cities is less than the sum of Sales in all New York cities, California and California.LA will appear below NYC in the sorted, descending list.
The result of
Order(SampleSet, ([1995], Sales), DESC)
is listed in the following table.
| Location | 1995 sales | ||
|---|---|---|---|
| USA | 5000 | ||
| California | 2000 | ||
| LA | 500 | ||
| Buffalo | 300 | ||
| NYC | 900 | ||
| France | 2500 | ||
| Paris | 365 | ||
| Nice | 27 | ||
| UK | 1900 | ||
| London | 250 |
The following expression sorts the members according to their values without regard for their relative positions in the member hierarchy. In this example, numeric values are sorted by 1995 sales per city, including aggregate sales values by state and country:
Order(SampleSet, ([1995], Sales), BDESC)
The following table shows the result of the previous expression.
| Location | 1995 sales |
|---|---|
| USA | 5000 |
| France | 2500 |
| California | 2000 |
| UK | 1900 |
| NYC | 900 |
| LA | 500 |
| Paris | 365 |
| Buffalo | 300 |
| London | 250 |
| Nice | 27 |
Note When the input set has two elements for which the «String Expression» or «Numeric Expression» has the same value, the input order is preserved.
For example, if the sales for USA and Europe is 300 each, and the sales for Asia is 100, the following expression returns the set {Asia, USA, Europe}, not the set {Asia, Europe, USA}:
Order({USA, Europe, Asia}, Sales, BASC)