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

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.

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.

-- MDX037-001 Initial DrillDown With Selected Members
SELECT
   {[Measures].[Reseller Sales Amount]} ON COLUMNS,
   
NON EMPTY(DRILLDOWNMEMBER( {
   [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
FROM 
    [Adventure Works]
WHERE
              ([Date].[Calendar Year].[CY 2003])

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

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

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

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