# MDX Essentials : Set Functions: The DRILLDOWNMEMBER() Function - Page 2

November 7, 2005

### The DrillDownMember() Function

#### Introduction

According to the Books Online, the DrillDownMember() function "drills down the members in a specified set that are present in a second specified set. Alternatively, the function drills down on a set of tuples." The set of child members returned by DrillDownMember() is ordered by hierarchy, and includes those members specified in the primary set that are also present in the secondary set. The order among the original members in the primary set is maintained, with all child members returned by the function included immediately under their parent member.

DrillDownMember() 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 Descendants() function and others. We will examine in detail the syntax for the DrillDownMember() 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 DrillDownMember() can offer the knowledgeable user. Hands-on practice with DrillDownMember(), 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 DrillDownMember() function drills down the members in a specified primary set that are present in a specified secondary set. The primary set can have any dimensionality, but the secondary set must contain a one-dimensional set, as we shall see. The result set returned by DrillDownMember() is composed of the children of each primary set member that also exists in the secondary set. If the primary set contains a parent member with one or more children, that parent member will not be subjected to drilldown. When the primary set contains tuples, DrillDownMember() returns a set composed of the results of drilling down each tuple in the primary set for which a corresponding match exists in the secondary set.

We noted earlier that the optional RECURSIVE keyword is available to enable repeated comparison passes between the primary and secondary sets. When we do not opt to add RECURSIVE, a single pass through the primary set is performed, matching each member / tuple with a corresponding member in the secondary set. When we add the keyword, DrillDownMember() performs continual, recursive comparisons between the result set and the secondary set, returning, for each member in the result set that is also present in the secondary set, the children - until no further members from the result set can be found within the secondary set. Because only complete hierarchies are accepted, RECURSIVE forces drill down to the leaf level of a specified dimensional hierarchy.

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

#### Syntax

Syntactically, anytime we employ the DrillDownMember() function to drill down by a member, the effect of the function, as we have seen, is to drill down those members of the specified primary set that are also present in the specified secondary set. We specify the two sets within the parentheses to the right of the DrillDownMember keyword. We can optionally follow the specified sets, within the same parentheses, with the RECURSIVE keyword. The general syntax is shown in the following string:

`DrilldownMember(Set1, Set2 [ , RECURSIVE ] )`

(Set1 in the string represents the "Primary Set" to which I refer throughout our discussion. Set2 represents the "Secondary Set.")

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

```DrillDownMember( {[Geography].[Geography].[State-Province].[California],
[Geography].[Geography].[State-Province].[Oregon],
[Geography].[Geography].[State-Province].[Washington] },
{[Geography].[Geography].[State-Province].[Oregon]} ) )ON ROWS
```

This row specification, within a proper query executed against the Adventure Works sample cube that, say, stipulated year 2003 on the column axis, might produce a results dataset similar to that depicted in Illustration 1.

Illustration 1: Results Dataset - Oregon Drilled to Child Cities

In the example dataset, we see that California and Washington (the top and bottom rows of the dataset) are presented in "rolled up" state. Between these two summary values, we see Oregon's summary line, followed by the drilled down values for the constituent child cities. (We note, too, that the total of the child cities in the dataset add to the rolled up Oregon total of 616, 268.06.)

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