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

Procedure: Satisfy Business Requirements with MDX

Let's assume, for our practice example, that we have received a call from members of the Reporting department of a client, the AdventureWorks organization, requesting our assistance in meeting a report presentation need. As has been the case in numerous requests for help in the past, the report authors realize that, while they are seeking assistance with a specific scenario in the immediate term, the need will manifest itself in numerous similar scenarios that they expect to face in the long term. They seek working examples, which they intend to extrapolate to recurring business needs they encounter within the enterprise.

The authors want to perform drilldowns much as they did in our previous session in Set Functions: The DRILLDOWNMEMBER() Function, but wish to extend the basic drilldowns we performed there to include the capability to present "top" and "bottom" results based upon drilldown, using the related MDX functions as the mechanism. Without getting too heavily into the details of the approach for this at the reporting application level, it is safe to say that this can be easily accommodated within the realm of the Analysis Services and Reporting Services components of the Microsoft integrated BI solution. We would simply create a targeted "drilldowntop" or "drilldownbottom" report (or perhaps even a single drilldown report with a parameterized "top" or "bottom" modifier) that would be triggered from a primary, summary level report, and then "connect" the primary and secondary report(s) 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 with the client representatives, is the need to perform drilldown on a group of members. In the example case at hand, the authors tell us that they need to provide a query that enables drilldown for the Calendar Year 2003 Internet Order Quantity and Internet Sales Amount for a couple of the Adventure Works Product Categories. While, as we have said, the ultimate objective might be to make the "top" or "bottom" number returned within our report datasets variable, we decide to begin with examples of the routine usage of the DrillDownMemberTop() and DrillDownMemberBottom() functions.

The report authors tell us that they first wish to drill down for Internet Order Quantity and Internet Sales Amount for the Accessories and Clothing Product Categories, returning the top seven (7) seller Subcategories for CY2003, ranking them upon Internet Sales Amount.

We suggest beginning with the generic DrillDownmember() function, and then morphing it into its DrilldownMemberTop() derivative, so as to build upon our work with DrilldownMember() in the previous session. (This may also prove beneficial for readers who are joining us with this article, and have not had an opportunity to explore our work with the more general function in Set Functions: The DRILLDOWNMEMBER() Function.) Based upon this reasoning, therefore, we propose to begin by returning the two Product Categories in summary, displaying beneath each summary the drilled down children of the respective Category (themselves Product Subcategories) together with the Internet Order Quantity and Internet Sales Amount values related to each Subcategory for CY2003. Once this is accomplished, we will have a core query upon which to base our subsequent efforts with the DrillDownMemberTop() and DrillDownMemberBottom() functions.

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 then expand upon those concepts with examples of each of the focuses of this article, the DrillDownMemberTop() and DrillDownMemberBottom() functions.

Let's construct a simple query, therefore, to return the requested Calendar Year 2003 Internet Order Quantity and Internet Sales Amount information for the two selected Product Categories, presenting the summarized sales for Accessories and Clothing, together with the corresponding values for each of the individually broken out component Subcategories that make up each of the Accessories and Clothing categories.

-- MDX038-001 Standard DrillDown With Selected Members

SELECT
   {[Measures].[Internet Order Quantity], [Measures].[Internet Sales Amount]} 
         ON COLUMNS,
DRILLDOWNMEMBER( {
 [Product].[Product Categories].[Category].[Accessories], 
 
    [Product].[Product Categories].[Category].[Clothing]},
  
          {[Product].[Product Categories].[Category], 
   
    [Product].[Product Categories].[Category].[Clothing].Children }
  
              ) ON ROWS 
  
FROM 
    [Adventure Works]
WHERE
    ([Date].[Calendar Year].[CY 2003])

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

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

In the returned dataset, we see that Accessories and Clothing are presented in "rolled up" state. However, the rows underneath each of Accessories and Clothing are composed of the respective Category's children, the various Subcategory components. (And we can verify easily that the values of the child rows in the dataset add to the rolled up totals.)