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

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Apr 3, 2006

Set Functions: The DrillUpLevel() Function - Page 2

By William Pearson

The DrillUpLevel() Function


According to the Books Online, the DrillUpLevel() function "drills up the members of a set that are below a specified level." The set of members returned by DrillUpLevel() is ordered by hierarchy, based upon the members included in a set that we specify within the function. The order among the original members in the Set Expression is preserved.

DrillUpLevel() also provides us the option of specifying a Level, to serve as the threshold upon which the set returned by the function is based; that is, when we specify a Level, DrillUpLevel() retrieves only those members within or above the specified level to construct the set it returns. In cases where we do not specify the optional Level, DrillUpLevel() retrieves only those members that are a single level higher that the lowest level of the first dimension referenced within the Set we specify within the function.

DrillUpLevel() has many applications, and, as we have seen to be the case with other "drilling" functions, pairing it with other MDX functions can help us to leverage its power even further. I frequently use it in combination with various functions, including the CrossJoin() and Descendants() functions (as we do within the practice session of this article), among others. We will examine in detail the syntax for the DrillUpLevel() function after our customary overview in the Discussion section that follows. Following that, we will conduct practice examples within a couple of scenarios, constructed to support hypothetical business needs that illustrate uses for the function. This will afford us an opportunity to explore some of the delivery options that DrillUpLevel() can offer the knowledgeable user. Hands-on practice with DrillUpLevel(), where we will create queries that leverage the function, will help us to activate what we learn in the Discussion and Syntax sections.


To restate our initial explanation of its operation, the DrillUpLevel() function drills up the members in a specified Set that lie below a specified Level. In effect, DrillUpLevel() removes the members of the specified Set that lie below the specified Level. The order of the specified Set, again, is maintained, and the set of members returned is ordered by hierarchy.

As we mentioned earlier, a Level Expression can optionally be specified. If no Level is specified within the function, DrillUpLevel() assumes the specified Level to be a single level above the members of the lowest hierarchical level within the Set. The members residing at the lowest level of the set are drilled up to the next highest level – meaning that the lowest level members, upon which the drillup is based, disappear.

When we specify the Level, DrillUpLevel(), in effect, drills up all members within the Set Expression that exist below the specified Level, to the next highest level (assuming, of course, that a "next highest level" exists within the structure). If we specify a Level in the function, and it turns out that no members within the Set Expression exist within the specified Level, then an identical set to that specified within the Set Expression is returned by DrillUpLevel(), in a manner identical to that we saw in the DrillDownLevel() function in MDX Set Functions: DrillDownLevel().

DrillUpLevel() works quite well with sets that have been produced by the DrillDownLevel() function – for that matter, even with the DrillDownMember() function. We will be using it with DrillDownLevel(), in the first of our practice examples, to illustrate the "cooperation" between the two, perhaps giving insight into the way that they can be readily "paired," in switch-like fashion, to support navigation within our reporting and analysis applications.

As we have noted earlier, when we couple it with other functions, we can leverage DrillUpLevel() to deliver a wide range of analysis and reporting utility. As in so many cases with the Microsoft integrated Business Intelligence solution, consisting of MSSQL Server, Analysis Services and Reporting Services, this function, residing within the Analysis Services layer, can be extended to support capabilities and attributes in the Reporting Services layer. Knowing "where to put the intelligence" among the various layers is critical to optimization, in many cases. For more of my observations on this subject, see Multi-Layered Business Intelligence Solutions ... Require Multi-Layered Architects.

Let's look at some syntax illustrations to further clarify the operation of DrillUpLevel().


Syntactically, anytime we employ the DrillUpLevel() function to drill up, the effect of the function, as we have seen, is to create a set by retrieving only the members that exist above the specified Level. We specify the Set_Expression (which can be multi-dimensional) within the parentheses to the right of the DrillUpLevel keyword. The general syntax is shown in the following string:

DrillUpLevel(Set_Expression [ , Level_Expression] )

When no Level Expression is specified, DrillUpLevel() retrieves only the members of the Set Expression that are one level above the lowest level in the first dimension referenced in the Set Expression. DrillUpLevel() returns the set specified in the Set Expression itself, when there are no set members within the specified Level – in other words, when there is nothing from which to drill up – as might be expected.

Let's take a look at an example. The following snippet employs the DrillUpLevel() function:

    {[Geography].[Geography].[Country].[United States],
                   )) ON AXIS(1)

This row specification, within a proper query executed against the Adventure Works sample cube that, say, stipulated the Reseller Sales Amount measure within the column axis, and Calendar Year 2004 as a slicer, might produce a results dataset similar to that depicted in Illustration 1.

Illustration 1: Results Dataset – Georgia Cities Drilled Up to Higher Levels

In the example dataset, we see that State-Province Georgia, along Country United States, two members specified within our Set Expression, appears alongside its summary Reseller Sales Amount. The two Georgia child Cities (Atlanta and McDonough) and their respective values do not appear. While the two cities are members of the set we specify, DrillUpLevel() has, in this case, been supplied the optional Level Expression. That expression, [Geography].[Geography].[State-Province], directs the function to drill up the members of the set that appear below the specified level, in effect removing any members from the result dataset that lie below the State-Province level. The cities therefore are swept out of the retuned dataset.

We will practice some uses of the DrillUpLevel() function in the section that follows.

MS SQL Archives

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