MSSQL Server Reporting Services : Mastering OLAP Reporting: Drilling Through Using MDX - Page 2

Drilling Through with MDX in Reporting Services

Introduction and Business Scenario

As I explored in my article Drilling Through with MDX: The DRILLTHROUGH Statement, the DRILLTHROUGH statement "retrieves the source rowset(s) from the fact table (that is, data source) for a specified tuple." Used in combination with the MDX SELECT keyword, the statement allows a client application (including OLAP reporting applications, such as Reporting Services) to take advantage of DRILLTHROUGH. DRILLTHROUGH is used in combination with a SELECT statement that specifies a cell in a cube, and makes possible the retrieval of the rowset(s) that make up the value in the targeted cell. It therefore follows that, if we already have a report that contains a suitable MDX SELECT query supporting its Dataset, we can construct a report with a similar query, with the DRILLTHROUGH statement preceding the SELECT keyword in its own Dataset query, to target in a Jump to action from the primary report. In this way, we leverage the native drillthrough capabilities of our Analysis Services OLAP data source.

To begin our examination of this approach to meeting business needs for drillthrough within Reporting Services, let's consider a relatively common business scenario. We will assume that we have received a request from a group of information consumers in the Purchasing Department of the FoodMart organization. The request is for support in creating a specialized report that, in fact, contains a subset of the data in the existing FoodMart Sales OLAP report. (FoodMart Sales is a popular, multi-use medium within numerous operational departments of the organization). In this case, the consumers want a primary report that, while based upon the same Sales cube as FoodMart Sales, presents only the Store Sales measure for the FoodMart product line. The report will be used for 1997 data only, but data for the entire year needs to be displayed in a compact fashion; hence, we will need only a single value column without drilldown capabilities to the underlying quarters (the approach taken in the FoodMart Sales report).

Moreover, the primary report should present products one level below the current lowest hierarchical level, Product Brand Name, and list the products by Product Name. The consumers would like the products to continue to be visually grouped, however, by Product Brand Name, for clarity. They would like for the other levels of the report, Product Department, Product Category and Product Subcategory, to be removed from the primary report, as it will be intended for specific product analysis purposes. Finally, the consumers would like the products to appear in alphabetical order to make it easier to find specific products for analysis purposes.

As a part of our standard business requirements gathering process, we work with the consumers to create a "draft" of the desired report – few tools are more assistive in confirming our understanding of exactly how the report is to look. An example, rendered in MS Excel, appears in Illustration 1.

In addition to the information contained within the primary report itself, the consumers have expressed a desire for the capability to drill through on the Store Sales value: they want to be able to click the Store Sales value for a given product, and be presented with a breakdown of the values that make up that total value. The consumers want the target report to present the following details about each of the contributing line items that appear:

• Date of Sale(s)
• State of Sale(s)
• City of Sale(s)
• Store of Sale(s)
• Product Brand
• Product Name
• Sales Value

The consumers tell us that they want a caption atop this secondary report, under the report title ("Product Details"), denoting the Product Name whose 1997 Store Sales value has been selected via the drillthrough action. Finally, they specify the need to have a total at the bottom of the report – to easily corroborate that the presented line items do, indeed, add up to the total Sales Value in the primary report from which the drillthrough report is triggered.

We listen carefully to the requirement, and, once again, create a draft of the report to confirm our understanding of the specification. A portion of the draft appears as shown in Illustration 2.

The information consumer group approves the drafts, giving us the go-ahead to develop the paired reports. After discussing briefly some of the considerations involved in our approach in the section that follows, we will proceed to a practice session whose end deliverable will be basic reports that meet the foregoing consumer specifications.

Considerations and Comments

The setup-from-scratch of all the components to prepare for a hands-on walkthrough of meeting the described hypothetical business needs would be time consuming. We will "kick start" our practice session with preparation steps that leverage existing sample data sources, cubes, reports, and other objects. Modifying existing samples will allow us more quickly to get to the focus of the session, the creation of drillthrough capabilities in Reporting Services).

For purposes of the lesson, we will need access to the sample Sales cube that installs with MSSQL Server Analysis Services, access to Analysis Manager and the Cube Editor to make modifications to the Sales cube, and access to the FoodMart Sales report within the Reporting Services development environment.