# Set Functions: The DrillDownLevelTop() and DrillDownLevelBottom() Functions - Page 2

March 6, 2006

### The DrillDownLevelTop() and DrillDownLevelBottom() Functions

#### Introduction

According to the Books Online, the DrillDownLevelTop() function "drills down the topmost members of a set, at a specified level, to one level below." Books Online states, moreover, that the DrillDownLevelBottom() function "drills down the bottommost members of a set, at a specified level, to one level below."

The DrillDownLevelTop() and DrillDownLevelBottom() functions thus behave exactly like the DrillDownLevel() function that we examined in Set Functions: The DrillDownLevel() Function, with one main difference: both "limit the result set to a specified number of members."

Both DrillDownLevelTop() and DrillDownLevelBottom(), like the DrillDownLevel() function, return children for members within the Level Expression that we specify. However, in addition to returning a set containing the parent members and child members, like DrilldownLevel(), DrillDownLevelTop() and DrillDownLevelBottom() return members limited to a specified Count. DrillDownLevelTop() and DrillDownLevelBottom() thus return sets containing parent members – with the specified count of child members at the level specified in Level Expression (when they exist within the structure to which the functions are applied) – with the highest (for DrillDownLevelTop() ) and lowest (for DrillDownLevelBottom() ) values involved. In other words, instead of including all children for each Set Expression member at the specified level, DrillDownMemberTop() and DrillDownMemberBottom() return the topmost and bottommost, respectively, specified number of children for each member.

As was the case with DrillDownLevel(), the DrillDownLevelTop() and DrillDownLevelBottom() functions have many applications, and pairing them with other MDX functions can help us to leverage their power even further. We will examine in detail the syntax for the functions after our customary overview in the Discussion section that follows. After that, we will conduct practice examples within a couple of scenarios, constructed to support hypothetical business needs that illustrate uses for the tandem functions. This will afford us an opportunity to explore some of the delivery options that DrillDownLevelTop() and DrillDownLevelBottom() can offer the knowledgeable user. Hands-on practice with these functions, where we will create expressions that leverage them to meet reporting needs, will help us to activate what we learn in the Discussion and Syntax sections.

#### Discussion

To restate our initial explanation of their operation, the DrillDownLevelTop() and DrillDownLevelBottom() functions drill down the members in a specified Set Expression that exist at the level we specify in Level Expression. The functions drill down the members of the Set Expression to one level below the lowest level represented within the Set Expression, or to one level below an (optional) Level Expression that we can specify, for a member existing within the set. When we do not supply a Level Expression, the expressions drill to the lowest level of members in the Set Expression. DrillDownLevelTop() and DrillDownLevelBottom() return the top or bottom (respectively) Count of members / tuples.

Several basic rules apply, some of which are similar to those that we have seen with the DrillDownLevel() function, within the core operations of the DrillDownLevelTop() and DrillDownLevelBottom() functions:

• The set specified in the Set Expression can have any dimensionality;

• The result set returned by each function is composed of the children of each member of the Set Expression that exist within the level specified in the Level Expression.

• When one or more of its children follow a given member within the Set Expression, drilling does not occur upon the member.

• The default behavior for each function is to drill down to the lowest level of members that exist within the Set Expression, when a Level Expression is not provided.

• The DrillDownLevelTop() function returns the set containing the parent members (from the Set Expression) and the top Count members / tuples from within the specified or default level, whichever is appropriate, sorting them in descending order, according to the Numeric Expression (or in descending order according to the values of the cells represented by the set of child members).

• The DrillDownLevelBottom() function returns the set containing the parent members (from the Set Expression) and the bottom Count members / tuples from within the specified or default level, whichever is appropriate, sorting them in ascending order, according to the Numeric Expression (or in ascending order according to the values of the cells represented by the set of child members).

• The DrillDownLevelTop() and DrillDownLevelBottom() functions base their evaluation and return of the top or bottom Count, respectively, upon the Numeric Expression. The Numeric Expression would represent a measure, typically; input of a constant numeric expression here would result in an error.

A wide range of analysis and reporting opportunities await the practitioner who becomes adept at coupling DrillDownLevelTop() and DrillDownLevelBottom() with other functions. As is often the case with MDX functions within the Microsoft integrated BI solution, (consisting of MSSQL Server, Analysis Services and Reporting Services), these functions, residing within the Analysis Services layer, can be extended to support (directly and indirectly) 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 DrillDownLevelTop() and DrillDownLevelBottom().

#### Syntax

Syntactically, anytime we employ the DrillDownLevelTop() and DrillDownLevelBottom() functions to drill down by a level, the initial effect of both functions, as we have discussed, is to drill down those members of the specified Set Expression to members within the next lower level, or to a level which we can specify using an optional Level Expression. We specify the Set Expression and Count within the parentheses to the right of the DRILLDOWNLEVELTOP / DRILLDOWNLEVELBOTTOM keyword. We can supply a Level Expression next, within either function. In both cases, too, we can optionally follow the Level Expression with a Numeric Expression, upon which the "top" or "bottom" Count is based. The general syntax is shown in the following string:

`DrillDownLevelTop / Bottom (Set_Expression, Count [ , [ Level_Expression ][ ,Numeric_Expression ] ] )`

Let's take a look at an example. The following snippet employs the DrillDownLevelTop() function (and, with a simple change of the keyword, it would represent the same syntax as for the DrillDownLevelBottom() function):

```DRILLDOWNLEVELTOP(
({[Customer].[Customer Geography].[Country].[United States],
[Customer].[Customer Geography].[Country].[Australia]}, 5,
[Customer].[Customer Geography].[Country],
[Measures].[Internet Sales Amount])
ON AXIS(1)
```

This row specification, within a proper query executed against the Adventure Works sample cube that, say, stipulated the Internet 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 – United States and Australia Drilled Down to Top Five Child State -Provinces

In the example dataset, we see that the function has drilled down on the Customer Geography members United States and Australia. Drill down has occurred to the Country level, with the returned members (from the level below) limited to the top five, based upon Internet Sales.

DrillDownLevelBottom() works in much the same manner, except that it provides the bottom values, based upon the Level and (optional) Numeric Expression we supply in conjunction with the Set Expression, in an ascending sort, as we shall see. We will get some practice using the DrillDownLevelTop() and DrillDownLevelBottom() functions in the section that follows.