The DrillDownLevel() Function
Introduction
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.
Discussion
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().
Syntax
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:
NON EMPTY DRILLDOWNLEVEL(
{[Geography].[Geography].[State-Province].[Georgia]})
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 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.
» 
Comment and Contribute
