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.
Illustration
1: Draft of the Primary Report Specification (Partial View)
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.
Illustration
2: Draft of the Drillthrough Target Report Specification (Partial View)
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.
» 