MDX Essentials : Set Functions: The DRILLDOWNMEMBER() Function

Monday Nov 7th 2005 by William Pearson

Architect Bill Pearson returns to expose the MDX DRILLDOWNMEMBER() function, kicking off an extended examination of the mechanics of drilling up and down within MDX.

About the Series ...

This article is a member of the series, MDX Essentials. The series is designed to provide hands-on application of the fundamentals of the Multidimensional Expressions (MDX) language, with each tutorial progressively adding features designed to meet specific real-world needs. For more information about the series in general, please see my first article, MDX at First Glance: Introduction to MDX Essentials.

Note: To follow along with the steps we undertake within this and prospective articles of the series, the following components, samples and tools are recommended, and should be installed according to the respective documentation that accompanies MSSQL Server 2005:

  • Microsoft SQL Server 2005 Database Engine (for the accompanying SQL Server Management Studio);
  • Microsoft SQL Server 2005 Analysis Services;
  • Business Intelligence Development Studio;
  • The Analysis Services AdventureWorks DW sample database (fully deployed to Analysis Services from the AdventureWorks DW Analysis Services project that accompanies Analysis Services 2005).

    The MSSQL Server 2005 and Analysis Services 2005 samples to which I refer above are available within a Microsoft Windows Installer package that accompanies the respective applications on the installation CDs, or which can be downloaded from Microsoft and perhaps elsewhere. The samples are not installed by default. If you did not install the samples and the AdventureWorks sample database during SQL Server 2005 Setup, you can install them after Setup finishes. For more information, see the topic "Running Setup to Install AdventureWorks Sample Databases and Samples" in the SQL Server Books Online. Make sure that you read and follow the installation instructions in the Readme files that accompany the samples that you want to install. For a list of samples, see the topic "Samples" in the SQL Server Books Online.

    To successfully replicate the steps of the article, you also need to be a member of the appropriate group / role in Analysis Services to access the sample cube for our exercises. Read permissions are assumed for the samples upon which we will base our practice exercises, where we will often be using the AdventureWorks cube, which arrives with Analysis Services 2005 within the AdventureWorks DW Analysis Services project.

    Current Service Pack updates are assumed for the operating system, MSSQL Server 2005 ("MSSQL Server"), MSSQL Server 2005 Analysis Services ("Analysis Services"), MSSQL Server 2005 Reporting Services ("Reporting Services") and the related Books Online and Samples. Images are from a Windows 2003 Server environment, but the steps performed in the articles, together with the views that result, will be quite similar within any environment that supports MSSQL Server 2005 and its component applications.

Within MSSQL Server Analysis Services 2000, we had somewhat limited options (namely, the MDX Sample Application) for easily querying our cube data sources with MDX (at least, that is, options that were available "out of the box" to anyone who had simply installed Analysis Services and its samples). MSSQL Server 2005 offers us numerous means for querying our OLAP data sources. In the interest of working within the current environment, in this and the prospective articles of the series, we will construct and execute our MDX queries, in the most part, from the SQL Server Management Studio, but we will occasionally do so from the SQL Server Business Intelligence Studio, and perhaps from other vantage points, to further enrich the learning experience within the subject matter involved. For more in-depth information on any of these various "points of approach" themselves, see the relevant sections of the Books Online.

Virtually all of the MDX we constructed in earlier articles can now be used in the SQL Server Management Studio, SQL Server Business Intelligence Studio, and in various other areas within the Microsoft integrated Business Intelligence solution, and much of what we construct going forward can be executed in the MDX Sample Application (assuming connection to an appropriate Analysis Services 2000 OLAP data source). MDX as a language continues to evolve and expand: we will focus on many new features in articles to come, while still continuing to focus upon business uses of MDX in general. The use of MDX to meet the real-world needs of our business environments will continue to be my primary focus within the MDX Essentials series.


In this article, we will begin an extended examination of the MDX surrounding drilling up and down within our Analysis Services cubes. Comprising an analytical technique by which an information consumer can maneuver between summarized ("drilling up") and detailed ("drilling down") levels of data, drilling up or down occurs along the lines of drilling paths that are defined within the physical structures of our cubes. Often specified by the cube's dimensional hierarchies, these paths can also be based upon alternative relationships that exist within or between dimensions.

An example might be described as follows, based upon an engagement within which I have recently been involved: An executive within a state Department of Education wishes to examine annual Scholastic Aptitude Test (SAT) results within a cube constructed primarily to allow comparison and analysis of those results at various levels. The executive can examine national averages and numbers of test takers, and then perform a drilldown operation within a Geography dimension that might then present the data by states. He could then perform subsequent drilldowns to display state regions (or perhaps congressional districts), counties / school systems, and more. Depending upon the design of the cube, the drilldown process could ultimately take the consumer to the level of the individual schools - perhaps even to the individual students themselves.

Underneath the capability of Analysis Services to meet this common need is the DrillDownMember() function. DrillDownMember() drills down to the members within a specified set that are present within a second specified set. Working with two sets, the function thus inserts the children of the members of the primary set into a secondary set for members that are common to both specified sets, as we shall see. Typically, the primary set is a subset of the secondary set, but this is not a hard and fast rule.

DrillDownMember() can be used in conjunction with tuples within the sets. Through the provision of the RECURSIVE keyword, the function can be enabled to allow repeated comparison passes of the sets. The idea is to repeat set comparisons, and the resulting drilldowns, until no members / tuples that exist in both the specified primary and secondary sets, remain unexpanded, as we shall see within the discussion and hands-on practice session to follow.

In a manner similar to other functions that we have examined in the MDX Essentials series, DrillDownMember() can be useful in a host of different reporting and analysis applications. DrillDownMember(), along with other "navigational" functions like it (virtually all of which we examine in other articles of this series) allows us to exercise a great deal of presentation sleight of hand, in working with MDX in Analysis Services, as well as within Reporting Services and various other reporting applications that can access an Analysis Services cube.

The DrillDownMember() function can be leveraged within and among the various "layers" of the Microsoft integrated Business Intelligence solution to supporting sophisticated presentations and features. We will introduce the function, commenting upon its operation and touching upon examples of effects that we can employ it to deliver. As a part of our discussion, we will:

  • Examine the syntax surrounding the function;
  • Undertake illustrative examples of the uses of the function in practice exercises;
  • Briefly discuss the results datasets we obtain in the practice examples.

The DrillDownMember() Function


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.


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().


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].[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.


Preparation: Access SQL Server Management Studio

To reinforce our understanding of the basics we have covered so far, we will use the DrillDownMember() function in a couple of ways that illustrate its operation. We will do so in simple scenarios that place DrillDownMember() within the context of meeting business requirements similar to those we might encounter in our respective daily environments. The intent is to demonstrate the operation of the DrillDownMember() function in a straightforward, memorable manner.

We will turn to the SQL Server Management Studio as a platform from which to construct and execute the MDX we examine, and to view the results datasets we obtain.

1.  Click the Start button.

2.  Select Microsoft SQL Server 2005 within the Program group of the menu.

3.  Click SQL Server Management Studio, as shown in Illustration 2.

Click for larger image

Illustration 2: Opening SQL Server Management Studio

The Connect to Server dialog appears.

4.  Select Analysis Services in the Server type selector.

5.  Type / select the server name (server name / instance, if appropriate) in the Server name selector.

6.  Supply authentication information, as required in your own environment.

7.  Click the Connect button to connect with the specified Analysis Services server.

The SQL Server Management Studio opens.

8.  In the Object Explorer pane (it appears by default on the left side of the Studio), expand the Databases folder (click the "+" sign to its immediate left), appearing underneath the Analysis Server with which we are working.

The Databases folder opens, exposing the Analysis Services database(s) with which we have established connection(s), as depicted in Illustration 3.

Illustration 3: Exposing the Analysis Services Databases in the Object Browser ...

NOTE: The Analysis Services databases that appear will depend upon the activities that have taken place in your own environment, and will likely differ from those shown in Illustration 3 above. For purposes of this practice session, the Adventure Works DW database must be present. If this is not the case, consult the Books Online for the installation / connection procedures, and complete these procedures before continuing.

9.  Expand the Adventure Works DW database.

The Database expands, exposing the folders for the various objects housed within an Analysis Services database, as shown in Illustration 4.

Illustration 4: Exposing the Object Folders in the Database ...

10.  Expand the Cubes folder within the Adventure Works DW database.

The Cubes folder opens. We see two cubes, the first of which, Adventure Works, is the sample cube with which we will be conducting our practice exercises. The cubes appear as depicted in Illustration 5.

Illustration 5: The Cubes Appear ...

11.  Click the Adventure Works cube to select it.

12.  Click the New Query button just under the main menu, in the upper left corner of the Management Studio, as shown in Illustration 6.

Illustration 6: Click the New Query Button with the Adventure Works Cube Selected

The metadata pane for the Adventure Works cube appears, along with the query pane to its right, as depicted in Illustration 7.

Illustration 7: Adventure Works Cube Metadata Appears ...

We will be using the Query pane in our practice session that follows, to construct and execute our MDX queries.

In addition to its role of providing an environment for the management of all server types in the SQL Server family, including Analysis Services, Reporting Services and DTS servers, many additional functions can now be performed from the SQL Server Management Studio. Among those functions, I find the capabilities to easily browse data, and to issue queries, highly convenient. We can accomplish querying in several other ways within the Microsoft integrated BI solution, but this is certainly one of the most direct.

We will be using the appropriate query editor in this and many prospective articles for the purpose of issuing MDX queries within the practice exercises of the MDX Essentials series. The query editors within the Management Studio afford us the capability to not only execute MDX queries against our cubes, but handle DMX (queries against our data mining models) and XMLA (covering all command types that can be sent to Analysis Services), as well. The MDX and DMX editors each include a Metadata pane for the convenient display of the metadata for the currently selected data source, a dramatic enhancement over the native tools provided in earlier versions. To some extent, the "drag and drop" capabilities afforded us via the Metadata pane might be useful, but we will typically limit our exercises to "straight MDX" queries, as the focus of this series is the MDX functions, properties, techniques and so forth. (Articles within my other series explore other capabilities and features of the SQL Server Management Studio, as well as the SQL Server Business Intelligence Studio).

Procedure: Satisfy Business Requirements with MDX

Let's assume, for our practice example, that we have received a call from the Reporting department of the Adventure Works organization, requesting our assistance in meeting a specific report presentation need that, while involving a given drilldown scenario in the immediate term, will manifest itself in numerous similar scenarios that the report authors are aware that they will face in the long term. The authors want to perform drilldowns using MDX functions as the mechanism. Without getting too heavily into the details of the approach for this at the reporting application level, we can suffice it to say that this could be easily accommodated within the realm of the Analysis Services and Reporting Services components of the Microsoft integrated Business Intelligence solution. We would simply create a targeted "drilldown" report that would be triggered from a primary, summary level report, and then "connect" the two in a manner similar to that which we followed in a drillthrough scenario in a recent article in my MSSQL Server Reporting Services series, Mastering OLAP Reporting: Drilling Through Using MDX.

What lies at the heart of the request, we note in our early discussions, is the need to perform drilldown on a group of members. In the case at hand, the authors tell us that they need to provide a query that enables drilldown for the Calendar Year 2003 Reseller Sales of a specific group of the bicycle models that Adventure Works builds. Out of three Product Model Lines within the Product dimension of the Adventure Works cube, the immediate need is to return a dataset that presents two of the lines, Mountain and Road models, in summary, while drilling down the Touring model to the level of its (non-empty) children (the various types of bicycle components for the Touring model) and presenting the Reseller Sales Amounts at that level.

We work with the Reporting department representatives to prepare a quick draft of the requirement, to corroborate the business need. The result of our joint efforts is the sample data, laid out in a small spreadsheet, shown in Illustration 8.

Illustration 8: "Confirmation Draft" of the Proposed Dataset Objective

This represents a simple, yet practical, need that we can readily answer using the DrillDownMember() function. We are confident that, once we construct and demonstrate the workings of the function, we can count on the authors to both grasp the concepts and to extend their requirements with further questions. This is an excellent starting point for an evolving examination of possible uses.

Let's construct a simple query, therefore, to return the requested Calendar Year 2003 Reseller Sales information for the three selected Product Model Lines, presenting the summarized sales for each of the Mountain and Road models, together with the sales values for each of the individually broken out component types that make up the Touring model.

1.  Type (or cut and paste) the following query into the Query pane:

-- MDX037-001 Initial DrillDown With Selected Members
   {[Measures].[Reseller Sales Amount]} ON COLUMNS,
   [Product].[Product Model Lines].[Product Line].[Mountain], 
        [Product].[Product Model Lines].[Product Line].[Road], 
            [Product].[Product Model Lines].[Product Line].[Touring]}, 
        {[Product].[Product Model Lines].[Product Line].[Touring]}))ON ROWS
    [Adventure Works]
              ([Date].[Calendar Year].[CY 2003])

The Query pane appears, with our input, as depicted in Illustration 9.

Illustration 9: Our Initial Query in the Query Pane ...

The above query meets the specific requirement beautifully, but also presents us with opportunities for extending the drilldown capability that it imparts even further within reporting, and other, consumer-facing applications. One such opportunity lies within the parameterization of various components of the axis definitions, together with the slicer. An obvious example might be to reference a parameter in the last set, {[Product].[Product Model Lines].[Product Line].[Touring]}, of the row axis, through Reporting Services. The parameter might then be passed from a "launch" point in a summary report to a target report containing a query similar to the above within its dataset, generating a drilldown report upon, say, the click of a summary item within the former by an information consumer. (This is but one approach; see my MSSQL Server Reporting Services series for articles that expose practical approaches to this and other methods for accomplishing similar effects.)

2.  Execute the query by clicking the Execute button in the toolbar, as shown in Illustration 10.

Illustration 10: Click Execute to Run the Query...

The Results pane is populated by Analysis Services, and the dataset shown in Illustration 11 appears.

Illustration 11: Results Dataset - Initial Use of DrillDownMember() Function

In the returned dataset, we see that Mountain and Road (the top two rows of the dataset) are presented in "rolled up" state, as is Touring, the third row. However, the rows underneath Touring are composed of its children, the various components of the Touring Model Line. (Moreover, we can verify easily that the values of the child rows in the dataset add to the rolled up Touring total of 616, 268.06.)

3.  Select File -> Save MDXQuery1.mdx As ..., name the file MDX037-001, and place it in a meaningful location.

Our report author colleagues have expressed satisfaction with the example we have presented. They present a similar request at this point for assistance in crafting another drilldown scenario. This example will further activate what we have discussed and seen thus far, employing DRILLDOWNMEMBER() in a simple scenario somewhat like our first example, initially, and then adding the RECURSIVE keyword. This will give us a hands-on, "before and after" look at how DRILLDOWNMEMBER() behaves with and without RECURSIVE.

The report authors outline the next drilldown scenario as follows: using the Adventure Works cube, once again, as a data source, they wish to create a query that returns information surrounding the composition of select sales from the perspective of certain customer locations. Specifically, they wish to present Calendar Year 2003 Internet Sales for United States customers, in such a way that the states within which sales took place are summarized. The drilldown requirement in this case consists in the need to present the total sales for the United States, drilled down to the sales totals for the states, which make up the U.S. total that appears. The report authors are convinced that, once they achieve this objective, the capability to perform ad hoc drilldowns on a given state (or a group of states) at runtime will become a matter of parameterizing the key component of the row axis specification within the MDX query involved, a mechanism that can be leveraged, as one example, in a summary / target report pairing within Reporting Services such as we have discussed earlier.

To outline the requirement further, our "confirmation of understanding draft," again in MS Excel, is depicted in Illustration 12.

Illustration 12: "Confirmation Draft" of the Proposed Dataset

We obtain consensus on the dataset, and set about constructing the query.

4.  Replace the syntax from our initial query in the Query pane by typing (or cutting and pasting) the following in its place:

-- MDX037-002 DrillDown With Selected Members - Basic
   {[Measures].[Internet Sales Amount]} ON COLUMNS,
   [Customer].[Customer Geography].[Country].[United States]}, 
      {[Customer].[Customer Geography].[Country].[United States] 
    })) ON ROWS
   [Adventure Works]
   ([Date].[Calendar Year].[CY 2003])

The Query pane appears, with our input, as shown in Illustration 13.

Illustration 13: Second Query in the Query Pane ...

5.  Select File -> Save MDX037-001 As..., name the file MDX037-002, and place it in a meaningful location, to protect the former query.

6.  Execute the query by clicking the Execute button in the toolbar, as before.

The Results pane is populated by Analysis Services, and the dataset shown in Illustration 14 appears.

Illustration 14: Results Dataset - Another Basic Use of DrillDownMember() Function

In the returned dataset, we see that Country United States (the top row of the dataset) is presented in "rolled up" state. The states within which we have conducted Internet Sales transactions in Calendar Year 2003 appear underneath the USA summary line. The states represent the drilled down children of Country United States, whose values add up to the Country United States total.

7.  Select File -> Save MDX037-002.mdx to ensure that the file is saved. (Leave MDX037-002.mdx open for the next steps).

The report authors again express satisfaction with the results, and immediately ask for a further extension of the query: As an example of the process involved, they wish to see how they might easily make it possible to "automatically" drill further into the secondary set we have used in the DRILLDOWNMEMBER() function that we have placed within our query ( {[Customer].[Customer Geography].[Country].[United States] ). As an illustrative example, they wish to drill down into the state Illinois to further break out its children, cities, and display their sales contributions, in turn, to the Illinois total.

We explain that the RECURSIVE keyword exists specifically for this purpose, and take the following steps to demonstrate its operation.

8.  Modify the top line in the query (the commented line) to the following:

                   MDX037-003 DrillDown With Selected Members - RECURSIVE in Place

9.  Select File -> Save MDX037-002 As..., name the file MDX037-003, placing it with the previous to query files, to protect the former query.

10.  Place a comma (",") after the existing member of the secondary set of the ROWS axis specification ([Customer].[Customer Geography].[Country].[United States]).

11.  Insert the following member expression into the secondary set, between the newly added comma and the right curly brace ("}):

 [Customer].[Customer Geography].[Country].[United States].[Illinois] 

12.  Add a comma behind / to the immediate right of the right curly brace, and then follow the comma with the RECURSIVE keyword.

Our addition to the ROW axis specification thus becomes:

[Customer].[Customer Geography].[Country].[United States].[Illinois]}, RECURSIVE 

The Query pane appears, with the areas affected by our modifications circled, as depicted in Illustration 15.

Illustration 15: Our Second Query, with Modifications

13.  Execute the query by clicking the Execute button in the toolbar, as we did earlier.

The Results pane is populated by Analysis Services, and the dataset shown in Illustration 16 appears.

Illustration 16: Results Dataset - RECURSIVE Keyword at Work ...

Illinois is, indeed, drilled down to the two cities, Chicago and Carol Stream (enclosed in the red rectangle above), whose sales totals make up the summary Illinois total of $ 2,686.66. As we noted was generally the case with the secondary set in the ROWS axis specification in earlier examples, added set members, in combination with the RECURSIVE keyword, can also be subjected to parameterization within a reporting (or other) application, for far-reaching capabilities with regard to manipulation of the supporting dataset of a target report containing the DRILLDOWNMEMBER() function under consideration.

The report authors state that their immediate goals have been met. We agree to return at a later time to demonstrate approaches to implement the DRILLDOWNMEMBER() function in reporting services to leverage MDX to support interactive drilldown by organizational information consumers.

14.  Select File -> Save MDX037-003.mdx to save our work.

15.  Select File -> Exit to leave the SQL Server Management Studio, when ready.

Summary ...

In this article, we begin an extended examination of the MDX surrounding drilling up and down within our Analysis Services cubes, focusing upon the DrillDownMember() function. We noted that the DrillDownMember() function can be leveraged within and among the various "layers" of the Microsoft integrated Business Intelligence solution to support sophisticated presentations and features. We introduced the function, commenting upon its operation and touching upon examples of effects that we can employ it to deliver, to empower information consumers to maneuver between summarized and detailed levels of data.

We introduced DrillDownMember(), commenting upon its operation and touching upon the "extended" datasets we can deliver using the function with a specified primary and secondary set. Next, we examined the syntax involved with DrillDownMember(), and then undertook a couple of illustrative practice examples within which we met hypothetical business requirements with the function, generating queries that capitalized on its primary features. Further, we discussed points within our query where we might consider the insertion of parameterization in a reporting application (or other consumer application), such as Reporting Services, to leverage the function to support drilldown in an ad hoc manner. Throughout our practice session, we briefly discussed the results datasets we obtained from each of the queries we constructed.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.

Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved