MDX Set Functions: DrillDownLevel() - Page 2

February 6, 2006

The DrillDownLevel() Function


According to the Books Online, the DrillDownLevel() function "drills down the members of a set to one level below the lowest level represented in the set, or to one level below an optionally specified level of a member represented in the set."

The set of child members returned by DrillDownLevel() is ordered by hierarchy, and includes those members specified in the specified Set Expression. The order among the original members in the Set Expression is maintained, with all child members returned by the function included immediately under their parent member, much as we saw to be the case with the DrillDownMember() function in Set Functions: The DRILLDOWNMEMBER() Function.

DrillDownLevel() has many applications, and 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, among others. We will examine in detail the syntax for the DrillDownLevel() 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 DrillDownLevel() can offer the knowledgeable user. Hands-on practice with DrillDownLevel(), 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 DrillDownLevel() function drills down the members (or tuples) in a specified Set Expression to the next lower level (assuming, of course, that one exists) in the structure. The order of the specified set is maintained, except that the children returned by the function are presented after their respective parents. The Set Expression can be of mixed dimensionality.

As we mentioned earlier, a Level argument can optionally be specified. If no Level is specified within the function, the members / tuples residing at the lowest level within the first dimension of the set are drilled down to the next lower level.

When we specify the Level, DrillDownLevel(), in effect, uses all members / tuples within the Set Expression that exist within the specified Level, and drills them down to the next lowest level (again, assuming that a next lowest 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 DrillDownLevel().

DrillDownLevel() also allows us to use a use a numerical Index, instead of specifying a Level in the function. Recall what we said earlier: If no Level is specified within the function, the members / tuples residing at the lowest level within the first dimension of the set are drilled down to the next lower level. When we have a set that consists of tuples of mixed dimensionality, the Index, which is zero-based, allows us to specify the precise hierarchy upon which to drill. Specification of Index 0, for example, would mean to drill down the lowest level members of the tuples residing within the first dimension appearing in the Set Expression, while specifying and Index of 1 or 2 would direct drilldown to occur upon the tuples containing the members of the lowest level of the second or third dimension, respectively.

When we couple it with other functions, we can leverage DrillDownLevel() 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 DrillDownLevel().


Syntactically, anytime we employ the DrillDownLevel() function to drill down by a level, the effect of the function, as we have seen, is to drill down a specified set to the next lower level. We specify the Set_Expression within the parentheses to the right of the DrillDownLevel keyword. The general syntax is shown in the following string:

DrillDownLevel(Set_Expression [ , {Level_Expression | , Index} ] )

We can specify a Level ("Level_Expression"), as we have discussed, to cause the function to drill to the next lowest level the members / tuples within the Set Expression which exist within the specified Level. (The function returns the set specified in the Set Expression when there are no members within the specified Level – in other words, when there is nothing to which to drill down – as might be expected.) Finally, we can substitute the Index number for the level, as another option for specifying the hierarchy within a Set Expression composed of tuples of mixed dimensions, upon which drilldown is performed by DrilldownLevel(). In our use of the Index option, we simply identify the hierarchy by its numeric position within the tuples, instead of focusing the drilldown via level specification.

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


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 Drilled to (Non-empty) Child Cities

In the example dataset, we see that Georgia, the State-Province specified in our Set Expression, appears alongside its summary Reseller Sales Amount, atop its drilled down non-empty child Cities and their respective values. (We note, too, that the total of the child Cities in the dataset add to the "rolled up" Georgia total of 179,522.90.)

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

The Network for Technology Professionals



Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers