Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Oct 13, 2003

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

By William Pearson

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.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date