MDX Essentials: Basic Set Functions: The Order() Function - Page 2

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.

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.

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.