Order

Return to: Calculated Member Builder Function List

Arranges members of a 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” indicates “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

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 as follows:

Location     1995 sales
USA     5000
  California   2000
    LA 500
    Buffalo 300
    NYC 900
France     2500
    Paris 365
    Nice 27
UK     1900
    London 250

Order(SampleSet, ([1995], Sales), BDESC)

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.

The result of

Order(SampleSet, ([1995], Sales), BDESC)

is as follows:

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 & Europe is 300 each, and that for Asia is 100, then the call:

Order({USA, Europe, Asia}, Sales, BASC)

returns the set

{Asia, USA, Europe}

and not the set

{Asia, Europe, USA}.

 

(c) 1988-1998 Microsoft Corporation. All Rights Reserved.