Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Dec 5, 2005

MDX Essentials: Set Functions: The DRILLDOWNMEMBERTOP() and DRILLDOWNMEMBERBOTTOM() Functions - Page 2

By William Pearson

The DrilldownMemberTop() and DrilldownMemberBottom() Functions


According to the Books Online, both the DrillDownMemberTop() and DrillDownMemberBottom() functions "drill down the members in a specified set that are present in a second specified set, limiting the result set to a specified number of members." Moreover, both functions "alternatively ... drill down on a set of tuples." The DrillDownMemberTop() and DrillDownMemberBottom() functions thus behave exactly like the DrilldownMember() function that we examined in Set Functions: The DRILLDOWNMEMBER() Function, with one main difference: both "limit the result set to a specified number of members."

Both DrillDownMemberTop() and DrillDownMemberBottom(), like the DrilldownMember() function, work with a primary and secondary set that we specify. The set of child members returned by the functions are ordered by hierarchy, and include those members specified in the primary set that are also present in the secondary set. Implicit in their design, both DrillDownMemberTop() and DrillDownMemberBottom() sort the children of each member in a specified primary set (DrillDownMemberTop() sorts the members in descending order, and DrillDownMemberBottom() sorts the members in ascending order), based upon an optional numeric expression that we provide in either case. After ranking, each of the functions return, like DrilldownMember(), a set containing the parent members and child members, but the members returned by DrillDownMemberTop() and DrillDownMemberBottom() are limited to a specified count. DrillDownMemberTop() and DrillDownMemberBottom() thus return sets containing parent members – with the specified count of child members - that are contained in both the primary and secondary sets – with the highest (for DrillDownMemberTop() ) and lowest (for DrillDownMemberBottom() ) values involved. In effect, instead of including all children for each member that exists in both the primary and secondary sets, DrillDownMemberTop() and DrillDownMemberBottom() return the topmost and bottommost, respectively, specified number of children for each member.

As was the case with DrillDownMember(), the DrillDownMemberTop() and DrillDownMemberBottom() 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. Following 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 DrillDownMemberTop() and DrillDownMemberBottom() 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.


To restate our initial explanation of their operation, the DrillDownMemberTop() and DrillDownMemberBottom() functions drill down the members in a specified primary set that are present in a specified secondary set, just as we found to be the case with the more "general" DrillDownMember() in our earlier article. The same basic rules, with regard to the core operation of the functions, apply:

  • The primary set can have any dimensionality, but the secondary set must contain a one-dimensional set;
  • The result set returned by each function 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, each of the functions 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.

The main difference between the operation of each of the DrillDownMemberTop() and DrillDownMemberBottom() functions and the DrilldownMember() function is that DrillDownMemberTop() and DrillDownMemberBottom() each limits the results set it returns to a specific number of members. The number is specified within the function in a count we supply. As one might expect from the "top" and "bottom" specialization of the functions, DrillDownMemberTop() and DrillDownMemberBottom() perform rankings of the children of the members that they identify as existing in both the primary and secondary sets (based upon an optional numeric expression we can specify), DrillDownMemberTop() in descending order and DrillDownMemberBottom() in ascending order.

This is the point at which the count we provide comes into play: DrillDownMemberTop() and DrillDownMemberBottom() limit the members returned to the number of the count. As an example, if the count we specified was five (5), DrillDownMemberTop() would return the highest five members, and DrillDownMemberBottom() would return the lowest five members (assuming five or more members existed within the qualified set).

We noted earlier that the optional RECURSIVE keyword is available to enable repeated comparison passes between the primary and secondary sets, again just as was the case with the DrillDownMember() function. 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, before applying the limitations of the count we have discussed. When we add the RECURSIVE keyword, both DrillDownMemberTop() and DrillDownMemberBottom() perform a continual, recursive comparison 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. The count limitation is then applied to the accumulated members before returning the set of desired size.

A wide range of analysis and reporting opportunities await the practitioner who becomes adept at coupling DrillDownMemberTop() and DrillDownMemberBottom() 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 DrillDownMemberTop() and DrillDownMemberBottom().


Syntactically, anytime we employ the DrillDownMemberTop() and DrillDownMemberBottom() functions to drill down by a member, the initial effect of both functions, as we have discussed, 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 DRILLDOWNMEMBERTOP / DRILLDOWNMEMBERBOTTOM keyword. In both cases, too, we can optionally follow the specified sets, within the same parentheses, with the RECURSIVE keyword. The general syntax is shown in the following string:

DrilldownMemberTop / Bottom(Set1, Set2, Count [ , [Numeric Expression] [, 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 DrillDownMemberTop() function (which, with a simple change of the keyword, would represent the same syntax as for the DrillDownMemberBottom() function):

   [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]},3, 
  [Measures].[Reseller Sales Amount] )) ON ROWS

This ROWS axis specification, within a proper query executed against the Adventure Works sample cube that, say, stipulated the Reseller Sales Amount in the column specification, and calendar year 2003 in the slicer, might produce a results dataset similar to that depicted in Illustration 1.

Illustration 1: Results Dataset – Product Lines Drilled to Child Products

In the example dataset, we see that the three cycle Product Lines, Mountain, Road and Touring (the top three rows of the dataset) are presented in "rolled up" state. Underneath the Touring summary value (the third row from the top), we see the drilled down values for the top three (ranked upon the Reseller Sales Amounts for each) constituent child Model Names, Touring-1000, Touring-3000 and Touring-2000. (We note, too, that the total of the child Model Names in the dataset add to the rolled up Touring total of 6,464,481.11.) Moreover, we recognize, too, the descending sort of the top three children presented within the Touring Product Line.

DrillDownMemberBottom() works in much the same manner, except that it provides the bottom values, based upon the measure upon which we choose to rank, in an ascending sort, as we shall see. We will get some practice using the DrillDownMemberTop() and DrillDownMemberBottom() functions in the section that follows.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM