The Order() Function
The Order() function, according to the Analysis
Services Books Online, "arranges members of a specified set,
optionally preserving or breaking the hierarchy." As we have already
intimated, we can use the function to arrange sets in hierarchies, or we can
order members of a set without regard to hierarchies. Whichever way we choose
to order the results returned by our queries, it is important to keep in mind
that we should not rely on defaults to provide sort orders for us, as we may
not obtain the results we expect.
We will examine the syntax for the Order() function
in general, then we will look at the two types of order that we
can enforce in separate examples. This will give us a richer understanding of
the options we have within the Order() function, together with a feel
for the similarities, as well as for the differences, of the results those
options afford.
Discussion
Order() allows us to impose sort order
on a standard MDX set in a manner that allows us to list results based upon any
of our measures. The only difference in two Order() functions that
provide different types of sorts is the optional order specification at the end
of the function, as we shall see.
Order() can accept either a string expression or a
numeric expression as criterion for ranking. Needless to say, proper
selection, as well as filtering, of dimensions and levels is critical to
prevent MDX from returning unexpected results.
The Order() function allows us to meet myriad common and
uncommon business needs, including the obvious need to order lists of members
within a set, be they employees, products, accounts, customers, months, or
others. We might also wish to organize a set of members by specific attributes,
such as statuses, degree of completion or readiness, and locations, to name a
very few.
Let's look at some illustrations to further clarify the
operation of Order().
Syntax
Syntactically, the sort
criteria (String Expression or Numeric Expression) and the order
specification (ASC, DESC, BASC, or BDESC) are placed within the
parentheses to the right of Order(), as shown in the following
illustration:
Order (<<Set>>, {<<String Expression>> | <<Numeric Expression>>}
[, ASC | DESC | BASC | BDESC])
Remarks
The Order() function returns hierarchized data
when the ASC or DESC order specifications are appended to the
function, and nonhierarchized data when BASC or BDESC are
used. (The "B" serves as an instruction to "break,"
or "ignore" the hierarchy.) ASC is the default order
specification when none is specified in the function.
In the case of the hierarchized order option, members
are first ranked according to position within the hierarchy, then according to
each level involved, based upon a string or numeric expression.
The following example expression
illustrates a use of the Order() function with the hierarchized order
option (inherent with the use of ASC):
{ORDER({[Warehouse].[USA].[WA].Children, [Warehouse].[USA].[OR].Children},
([Measures].[Warehouse Cost]) , ASC)} ON ROWS
This expression in a proper query would result
in the return of the set depicted in Table 1.
|
|
Warehouse
Cost
|
|
Portland
|
11,509.54
|
|
Salem
|
13.530.31
|
|
Bellingham
|
921.39
|
|
Walla Walla
|
981.81
|
|
Spokane
|
2,294.52
|
|
Bremerton
|
3,249.29
|
|
Yakima
|
4,454.60
|
|
Seattle
|
12,335.21
|
|
Tacoma
|
13,672.34
|
Table 1: Ordering of the Set, Hierarchized Ascending, by
Warehouse Cost
In the expression above, we use the Order() function
to enumerate all Washington and Oregon warehouses, in Ascending
(ASC) order, with respect to the total Warehouse Cost for each. Because
we use ASC (and therefore the hierarchized option), we order with
respect to the existing hierarchy: We obtain the two Oregon children
first, sorted by Warehouse Cost in ascending order, followed by the
children of Washington, sorted in ascending order. We can order the
result dataset based upon any relevant measure in just this fashion.
By contrast, the choice of the alternative nonhierarchized
option (in keeping with an example identical, otherwise, to the above, except
for its use of the BASC order type) again ranks the
members of the set, based once more upon the Warehouse Cost measure, but
without regard to hierarchy. Let's look at the result when we simply change
the order type in our first expression to BASC, and, in effect,
direct that the hierarchy be ignored:
{ORDER({[Warehouse].[USA].[WA].Children, [Warehouse].[USA].[OR].Children},
([Measures].[Warehouse Cost]) , BASC)} ON ROWS
This expression in a proper query would
result in the return of the set depicted in Table 2.
|
|
Warehouse
Cost
|
|
Bellingham
|
921.39
|
|
Walla Walla
|
981.81
|
|
Spokane
|
2,294.52
|
|
Bremerton
|
3,249.29
|
|
Yakima
|
4,454.60
|
|
Portland
|
11,509.54
|
|
Seattle
|
12,335.21
|
|
Salem
|
13.530.31
|
|
Tacoma
|
13,672.34
|
Table 2: Ordering of the Set, Nonhierarchized Ascending,
by Warehouse Cost
In the expression above, we use the Order() function,
again, to enumerate all Washington and Oregon warehouses, in Ascending
order, with respect to the total Warehouse Cost for each. This time, however,
because we use BASC (and therefore the nonhierarchized option), we order
without respect to the existing hierarchy: We obtain the complete group of
children sorted by Warehouse Cost in ascending order, plain and
simple. No attention is paid to hierarchy whatsoever.
» 