MSSQL Server Reporting Services : Mastering OLAP Reporting: Drilling Through Using MDX

Thursday Oct 20th 2005 by William Pearson
Share:

Architect Bill Pearson performs Drillthrough from an OLAP Report by leveraging the MDX DRILLTHROUGH statement in a target detail report.

About the Series ...

This article is a member of the series MSSQL Server Reporting Services. The series is designed to introduce MSSQL Server Reporting Services ("Reporting Services"), with the objective of presenting an overview of its features, together with many tips and techniques for real-world use. For more information on the series, as well as the hardware / software requirements to prepare for the exercises we will undertake, please see my initial Database Journal article, A New Paradigm for Enterprise Reporting.

As I have stated since the charter article of the series, published about the time Reporting Services was first publicly released, my conviction is that Reporting Services will commoditize business intelligence, particularly in its role as a presentation component within an integrated Microsoft BI solution. Having been impressed from my first exposure to this exciting application, when it was in early beta, my certainty in its destiny grows stronger by the day, as I convert formerly dominant enterprise business intelligence systems, such as Cognos, Business Objects, Crystal, and others, to the Reporting Services architecture. I receive constant requests to conduct strategy sessions about these conversions with large organizations in a diverse range of industries – the interest grows daily as awareness of the solution becomes pervasive. Indeed, the five- to six-plus figures that many can shave from their annual IT budgets represent a compelling sweetener to examining this incredible toolset.

Basic assumptions underlying the series are that you have correctly installed Reporting Services, including current service packs, along with the applications upon which Reporting Services relies, and that you have access and the other rights / privileges required to complete the steps we undertake in my articles. For details on the specifics of the adjustments necessary to quickly allow full freedom to complete the exercises in this and subsequent articles, as well as important assumptions regarding rights and privileges in general, please see earlier articles in the series, as well as the Reporting Services Books Online.

About the Mastering OLAP Reporting Articles ...

One of the first things that become clear to "early adopters" of Reporting Services is that the "knowledgebase" for OLAP reporting with this tool is, to say the least, sparse. As I stated in my article, Mastering OLAP Reporting: Cascading Prompts, the purpose of the Mastering OLAP Reporting subset of my Reporting Services series is to focus on techniques for using Reporting Services for OLAP reporting. In many cases, which I try to outline in my articles at appropriate junctures, the functionality of the reporting solutions of well-established, but expensive, solutions, such as Cognos PowerPlay, can be met in most respects by Reporting Services – at a tiny fraction of the cost. The vacuum of documentation in this arena, to date, represents a serious "undersell" of Reporting Services from an OLAP reporting perspective. I hope to contribute to making this arena more accessible to everyone, and to share my implementation and conversion experiences as the series evolves. In the meantime, rest assured that the OLAP potential in Reporting Services will be yet another reason that the application "commoditizes" Business Intelligence.

For more information about the Mastering OLAP Reporting articles, see the section entitled "About the Mastering OLAP Reporting Articles" in my article Ad Hoc TopCount and BottomCount Parameters.

Overview

In my MDX Essentials series at Database Journal, I recently published an article entitled Drilling Through with MDX: The DRILLTHROUGH Statement, where I introduced drillthrough concepts, and then focused on the use of the DRILLTHROUGH statement within MDX. Nowhere is the concept of drillthrough more practical than within a reporting application, and Reporting Services naturally leads the report writer universe in enabling us to fully leverage MDX in reporting from Analysis Services data sources.

As I mentioned in Drilling Through with MDX: The DRILLTHROUGH Statement, practically anyone involved in business intelligence is aware that multidimensional databases contain aggregated information to support rapid query processing. The beauty of OLAP (Online Analytical Processing) is that it stores high-level summaries for virtually instant delivery to our reports and other applications – summaries that can, indeed, be assembled directly from an OLTP (Online Transactional Processing) system, but only after it goes through the work of summing what might be millions (or more) transactions to get the same results.

The downside to the otherwise superior reporting scenario of OLAP lies within its very advantage: aggregation. An important consideration within the realm of business intelligence is the provision of the capability of selective focus. More specifically, within the context of this article, the enterprise often needs to be able to see the underlying transactions once he or she identifies a summary that raises questions. An example might exist in the case of a real estate portfolio manager who notes, while looking at monthly performance metrics for a group of properties, that profit margins for a given property within the group seem consistently lower than the rest, or perhaps that, over a three-year period, a property's monthly profit has gradually trended lower, while others remain stable. Because transactional data is not contained within the OLAP cube, the manager needs a mechanism to present the underlying transactions (in this case, the revenues and expense transactions) that make up the margins under examination. This mechanism, to which the business intelligence community refers as drillthrough, allows the property manager to see the transactions that make up the margins, exposing, for example, the tenants, vendors, services, and other entities involved within each, so that focused action can be taken to ultimately control results.

The natively supported drillthrough of MSSQL Server Analysis Services is manifested in the retrieval and return of the underlying source's individual records –the "details" that combine to make up the value of the cell upon which drillthrough is performed. As we saw in Drilling Through with MDX: The DRILLTHROUGH Statement, drillthrough from a client application can be accomplished, in the most straightforward approach, through the passage of the MDX DRILLTHROUGH statement to Analysis Services. Drillthrough can mean different things in Reporting Services. The term "drillthrough" can be used to describe two approaches to "drilling through":

  • The establishment of a target report to which we "point" a primary report (the point from which we wish to drill through), passing parameters in a way to allow for selection of the underlying data. The mechanism under the drillthrough effect is, therefore, a jump from the primary report to another report. This is done via the Jump to report designation within the Navigation tab underlying the report object (often a value), from which we wish to trigger the drillthrough action.
  • Putting a report in place as a target of a Jump to action, similar to the above, but with a significant difference: the Dataset query of the target report will be identical, in the relevant aspects, to that of the primary report, but with the MDX DRILLTHROUGH statement preceding the SELECT statement of the primary query.

In this article, we will explore using the latter of the two approaches, and leverage the DRILLTHROUGH statement that we introduced in Drilling Through with MDX: The DRILLTHROUGH Statement. As a part of our discussion, we will:

  • Present a business requirement based upon the needs of a hypothetical client, which we will address with a drillthrough capability in Reporting Services;
  • Prepare for our practice session by creating a project within Reporting Services, and by creating a "clone" report (based upon an existing sample OLAP report to save time), within which we will perform our exercises;
  • Enable Drillthrough for a sample cube, upon which our hypothetical client's reports are based, within Analysis Services;
  • Examine configuration of Drillthrough to present the appropriate transactional details to its intended users;
  • Briefly examine the syntax surrounding the DRILLTHROUGH statement;
  • Create a "target" report that leverages DRILLTHROUGH;
  • Link the primary report (our "clone" of the sample report) to the new target report;
  • Execute the drillthrough action in the working example pair;
  • Briefly discuss concepts along the way, as well as the results we obtain within various actions we take in our practice session.

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.

Practice

Preparation: Set up the Reporting Services Environment

For purposes of our practice session, we will create a copy of the Foodmart Sales sample report (one of several report samples, as we have said, that are available for installation with Reporting Services). Creating a "clone" of the report means we can make changes to our report while retaining the original sample in a pristine state – perhaps for other purposes, such as using it to accompany relevant sections of the Books Online, and other documentation, in learning more about Reporting Services in general.

Before we can work with a clone of the Foodmart Sales report, we need to create a Reporting Services project in the Visual Studio.Net 2003 Report Designer environment. Streamlining, and then making the enhancements to the report to add the functionality to support the subject of our lesson, can be done easily within the Reporting Services Report Designer environment. Working with a copy of the report will allow us the luxury of freely exploring our options, and leave us a working example of the specific approach we took, to which we can refer in our individual business environments. If you already have a project within which you like to work with training or development objects, you can simply skip the Create a Reporting Services Project section.

If the sample FoodMart 2000 Analysis Services database or the Foodmart Sales report was not created / installed as part of the initial installation of the associated application, if either was removed prior to your beginning this article, or if either or both applications have yet to be installed, etc., see the respective Books Online and other documentation supplied with MSSQL Server, Analysis Services and / or Reporting Services for the necessary procedures to prepare for the exercises that follow.

Create a Reporting Services Project

To begin, we will launch Reporting Services' Report Designer, found in Microsoft Visual Studio .NET 2003.

1.  Click Start.

2.  Navigate to, and click, the Microsoft Visual Studio .NET 2003 shortcut in the Programs group, as appropriate.

The equivalent on my PC appears as depicted in Illustration 3.


Illustration 3: Beginning in Microsoft Visual Studio .NET 2003 ...

Visual Studio .NET 2003 opens at the Start page.

3.  Select File --> New from the main menu.

4.  Click Project from the cascading menu, as shown in Illustration 4.


Illustration 4: Selecting a New Project

The New Project dialog appears. Business Intelligence Projects appears in the Project Types tree, indicating an installation of Reporting Services.

5.  Click Business Intelligence Projects in the Project Types tree, if necessary.

6.  Click Report Project in the Templates list.

7.  Type the following into the Name box, leaving other settings at default:

RS022_MDX_DRILLTHROUGH

8.  Navigate to a location in which to place the Report Project files.

The New Project dialog appears, with our input, as depicted in Illustration 5.


Illustration 5: The New Project Dialog, with Our Input

9.  Click OK.

Our new project appears in the Solution Explorer (upper right corner of the Visual Studio .NET interface), as we see in Illustration 6.


Illustration 6: The New Project Appears in the Solution Explorer

Having created a Report Project, we are ready to proceed with creating the new report clone.

Create a Copy of the Foodmart Sales Report

As we have noted, we will be working with a copy of the Foodmart Sales report, to keep the original sample intact for easy access and use at another time.

1.  Right-click the Reports folder underneath the Shared Data Sources folder, in the Solution Explorer.

2.  Select Add --> Add Existing Item from the cascading context menus that appear, as depicted in Illustration 7.


Illustration 7: Adding the Report to the Project ...

The Add Existing Item – RS022_MDX_DRILLTHROUGH dialog appears.

When we installed Reporting Services, the default installation point for the sample report files was the Samples folder within the Reporting Services program folder. A common example of this default path is as follows:

C:\Microsoft SQL Server\MSSQL\Reporting Services\Samples\Reports

3.  Using the Add Existing Item - RS022_MDX_DRILLTHROUGH dialog, navigate to the location of the sample reports in your own environment.

An example of the Add Existing Item - RS022_MDX_DRILLTHROUGH dialog, having been pointed to the sample Reports folder (which contains the Foodmart Sales report we seek), appears as partially shown in Illustration 8.


Illustration 8: Navigating to the Sample Reports Folder ...

4.  Right-click the Foodmart Sales report inside the dialog.

5.  Select Copy from the context menu that appears, as depicted in Illustration 9.


Illustration 9: Performing a Quick Copy of the FoodMart Sales Report

6.  Within the Add Existing Item - RS022_MDX_DRILLTHROUGH dialog, navigate to the RS022_MDX_DRILLTHROUGH folder we created earlier, when we added the new RS022_MDX_DRILLTHROUGH project.

7.  Right-click somewhere in the white space inside the RS022_MDX_DRILLTHROUGH folder, within the dialog.

8.  Select Paste from the context menu that appears, as shown in Illustration 10.


Illustration 10: Select Paste within the New Folder ...

A copy of the Foodmart Sales report appears within the dialog.

9.  Right-click the new file.

10.  Select Rename from the context menu that appears.

11.  Type the following name in place of the highlighted existing name:

RS022_MDX_DRILLTHROUGH.rdl

NOTE: Be sure to include the .rdl extension in the file name.

The renamed copy of the Foodmart Sales sample report appears as depicted in Illustration 11.


Illustration 11: The New Report File, RS022_MDX_DRILLTHROUGH.rdl

12.  Click the white space to the right of the file name, to accept the new name we have assigned.

13.  Re-select the new file by clicking it.

14.  Click Open on the dialog box to add the new report to report project RS022_MDX_DRILLTHROUGH.

RS022_MDX_DRILLTHROUGH.rdl appears in the Reports folder, within the RS022_MDX_DRILLTHROUGH project tree in the Solution Explorer, as shown in Illustration 12.


Illustration 12: The New Report Appears in Solution Explorer – Report Folder

15.  From the main menu in the design environment, select File ---> Save All, as depicted in Illustration 13.


Illustration 13: Select File --> Save All to Save Our Work So Far ...

Leaving the Report Designer open, we will now briefly visit Analysis Services to enable drillthrough for the Sales sample cube, which we will then target with an MDX query in our practice exercise.

Enable Drillthrough in the FoodMart Sales Cube within Analysis Services

Before we can leverage the Drillthrough capabilities of an Analysis Services cube, we must enable the feature from within the Cube Editor. When we enable Drillthrough, we also select the tables and columns that are included in the result set returned by a drillthrough operation. As many of us know, these columns can be from any table in the cube's underlying data source, (and can include columns that are not part of the cube's schema, if set up properly). We can also limit resources consumed by Drillthrough operations (a potentially significant concern with larger, more complex cubes), limiting the number of rows returned for the columns we have selected, when appropriate.

NOTE: For more information on Drillthrough in Analysis Services, see my articles Drilling Through to Details: From Two Perspectives and Mastering Enterprise BI: Create Aging "Buckets" in a Cube (both members of the Introduction to MSSQL Server Analysis Services series) here at Database Journal.

Let's enable Drillthrough for the Sales sample cube, taking the following steps.

1.  Open Analysis Manager, beginning at the Start menu.

2.  Expand the Analysis Servers folder by clicking the "+" sign to its immediate left.

Our server(s) appear.

3.  Expand the desired server.

Our database(s) appear, in much the same manner as shown in Illustration 14.


Illustration 14: Example Databases Displayed within Analysis Manager

NOTE: Your databases / cube tree will differ, depending upon the activities you have performed since the installation of Analysis Services (and the simultaneous creation of the original set of sample cubes). Should you want or need to restore the cubes to their original state, simply restore the database under consideration. For instructions, see the MSSQL Server 2000 Books Online.

4.  Expand the FoodMart 2000 database.

5.  Expand the Cubes folder.

The cubes appear as depicted in Illustration 15.


Illustration 15: Example Cubes within the FoodMart 2000 Analysis Services Database

6.  Right-click the Sales cube within the FoodMart 2000 Analysis Services database.

7.  Select Edit... from the context menu that appears, the relevant portion of which is shown in Illustration 16.


Illustration 16: Opening the Cube Editor

The Cube Editor opens.

8.  Select Tools --> Drillthrough Options... from the main menu inside the Cube Editor, as depicted in Illustration 17.


Illustration 17: Select Tools --> Drillthrough Options ...

The Cube Drillthrough Options dialog appears.

9.  Click the check box labeled Enable drillthrough (atop the dialog) to check it, if necessary.

10.  Within the selection checklist, on the Columns tab, ensure that only the checkboxes presented in Table 1 below are checked:

Column

Table

"store_sales"

"sales_fact_1997"

"store_name"

"store"

"store_city"

"store"

"store_state"

"store"

"store_country"

"store"

"the_date"

"time_by_day"

"brand_name"

"product"

"product_name"

"product"


Table 1: Select Settings for the Detail Drillthrough View ...

The Cube Drillthrough Options dialog appears as partially shown in Illustration 18.


Illustration 18: The Cube Drillthrough Options Dialog (Partial View) with Settings

Our intent here is to establish a simple display of dated "transactions" – to give us a feel that transactions within reasonable date ranges are, indeed, underneath the totals we see in the value upon which we can drill through, Store Sales. The columns selected here could obviously be varied to accomplish other specific needs, just as easily, and would apply to any measure in the cube.

11.  Click OK to accept settings.

The Drillthrough Settings message box appears, alerting us to the fact that our settings take effect only after the cube is saved, as depicted in Illustration 19.


Illustration 19: Drillthrough Settings Message Box

12.  Click OK to dismiss the message box, and to close the Cube Drillthrough Options dialog.

13.  In the upper left corner of the Cube Editor (top of the cube tree), right-click the Sales cube.

14.  Select Process Cube ... from the context menu that appears, as shown in Illustration 20.


Illustration 20: Select Process Cube ...

15.  Click Yes on the Save the cube dialog that appears, to save the cube, as depicted in Illustration 21.


Illustration 21: Save the Cube Before Processing ...

The Process a Cube dialog appears next. We will select Full processing, simply to ensure that we are all in sync going forward.

16.  Click the radio button to the left of the Full Process label, as shown in Illustration 22.


Illustration 22: Select Full Process ...

17.  Click OK to begin Full processing of the Sales cube.

Processing begins immediately, and the Process viewer appears, displaying the various logged events of the processing cycle. Processing completes, and the viewer presents the green Processing completed successfully message, as depicted in Illustration 23.


Illustration 23: Processing Completes Successfully, as Indicated on the Process Viewer

18.  Click Close to dismiss the viewer.

19.  Select File --> Exit to close the Cube Editor.

20.  Select File --> Exit to exit Analysis Manager.

Now that we have enabled drillthrough for the Sales cube, we are ready to examine running a query against it that leverages the capability. In our case, that query will support the Dataset for our drillthrough target report. We will get some hands-on practice with construction of this report, as soon as we complete preparation of the primary report (from which we will launch drillthrough activity) in the next section.

Modify the Cloned Report to Convert It to Meet the New Requirements

While we certainly might have modified the copy of the FoodMart Sales report to meet the requirements we have obtained for the new primary report, I wanted to defer this final preparatory step to just before the creation of the drillthrough target report. Keeping our work with the two reports together will make it easier to understand their interrelationships, and how we reference them to each other for passage of parameters, and so forth, as we shall see.

Let's return to the Reporting Services development environment, where we left our new report, RS022_MDX_DRILLTHROUGH.rdl, and make the modifications required to match the specifications we have received as part of our business requirements gathering efforts.

1.  From within the Solution Explorer, within the Reports folder, right-click RS022_MDX_DRILLTHROUGH.rdl.

2.  Select Open, as shown in Illustration 24, from the context menu that appears.


Illustration 24: Select Open from the Context Menu ...

RS022_MDX_DRILLTHROUGH opens and appears on the Layout tab of the Report Designer, as depicted in Illustration 25.


Illustration 25: RS022_MDX_DRILLTHROUGH Appears in Report Designer - Layout Tab

3.  Click the Preview tab to execute the report.

RS022_MDX_DRILLTHROUGH executes briefly, and then returns data. We see, atop the Preview tab, the Product Family parameter selector, as shown (expanded) in Illustration 26.


Illustration 26: RS022_MDX_DRILLTHROUGH Appears in Preview (Partial View) – with Parameter Selector Expanded

Each of the three Product Family selections gives us a result set filtered for its members. The Purchasing team has asked for a "consolidated view," where all products appear on a single report by Product Name, visually grouped by Product Brand Name. Elimination of the parameter in place will be a simple way to get started toward customizing the report to meet the new specifications the consumers have relayed to us.

4.  Click the Layout tab to return to the Layout view.

5.  Select Report --> Report Parameters from the main menu atop the Report Designer, as depicted in Illustration 27.


Illustration 27: Select Report --> Report Parameters from the Main Menu

The Report Parameters dialog, where we define parameters for the report, appears, as shown in Illustration 28.


Illustration 28: The Report Parameters Dialog

We will get more exposure to this dialog later in our session, within the drillthrough target report we create from scratch. Our objective at this point is to remove the existing parameter, and to consolidate the Product Families thereby.

6.  In the Parameters list, on the left side of the dialog, click the single entry, ProductFamily, to select it.

7.  Click the Remove button underneath the Parameters list.

The ProductFamily parameter is removed from the list.

8.  Click OK to accept our removal of the parameter.

The now empty Report Parameters dialog closes, and we are returned to the Layout view of the report.

We now need to modify our Dataset query to enable it to support the new reporting requirements of the information consumers. We will do this from the Data tab within the new report.

9.  Click the Data tab within the Report Designer.

10.  Select ProductList in the Dataset selector.

The ProductList Dataset appears. Because this Dataset was created to support the picklist behind the Parameter we deleted earlier, we can eliminate the Dataset, as a matter of simple housekeeping, while we are "in the neighborhood."

11.  Click the Delete Selected Dataset button, as depicted in Illustration 29.


Illustration 29: Deleting the ProductList Dataset

12.  Click Yes on the warning dialog that appears next, as shown in Illustration 30.


Illustration 30: Click Yes to Confirm Intentions ...

The no longer useful Dataset is deleted, and we arrive at the remaining ProductData dataset, where we will now modify the existing Dataset query, which appears as depicted in Illustration 31.


Illustration 31: The Data Tab with Existing Dataset Query

13.  Replace the existing query with the following MDX syntax:


SELECT 
  {[Measures].[Store Sales]} ON COLUMNS, 
  {Descendants([Product].[All Products],[Product].[Product Name],LEAVES)} ON ROWS
FROM 
  [SALES]
  WHERE
  ([Time].[Year].[1997])

The new Dataset query appears as shown in Illustration 32.


Illustration 32: The Dataset Query Replacement on the Data Tab

14.  Click the Run button on the toolbar to execute the query, as shown in Illustration 33.


Illustration 33: Executing the New Dataset Query ...

The new Dataset appears in the Results pane, below the query, as partially depicted in Illustration 34.


Illustration 34: Partial View of the Returned Dataset

The changes we have made are largely to simplify the query, to narrow the Dataset it returns to more leanly support the new requirements of the report. We will see the query again, in our target drillthrough report. There we will reuse it, together with the DRILLTHROUGH statement, to generate a report that presents the detail under a selected Store Sales value, as we shall see.

Having recast our Dataset query to the new business requirements, we now must delete a remaining reference to the parameter we have removed, which we can access via the Properties dialog for the matrix. We will have additional tasks to perform within the Properties dialog, as well.

15.  Click the Layout tab to return to the Layout view of the report.

16.  Click at some point within the label textbox of the report (containing the label Foodmart Sales), to make the row and column headers of the matrix data region visible.

17.  Right-click the upper left corner of the matrix. (If the headers disappear as you touch them with the cursor, you should still see a faint outline of the matrix.)

18.  Select Properties from the context menu that appears, as shown in Illustration 35.


Illustration 35: Accessing the Matrix Properties

The Matrix Properties dialog opens, defaulted to the General tab.

19.  Click the Filters tab.

20.  Click the Value field of the single occupied row to select it.

21.  Click the Delete button to delete the reference to the parameter, as indicated in Illustration 36.


Illustration 36: Select and Delete the Parameter Reference

The remaining reference to the now-deleted parameter is itself deleted. Our next steps, which we will also undertake within the matrix Properties dialog, involve a bit of "level extraction." We recall that the consumers have told us that the row axis needs to display only the lowest two levels of the Product dimension, Product Brand Name and Product Name. Moreover, they have declared that the custom report will no longer require drilldown features, as it will be a fixed report whose purpose in life is as a limited analysis tool. Further, they have stated that the total Stores Sales value displayed for each product will represent the total for 1997, therefore the existing Quarter groups (and the drilldown feature which is based upon the Quarter groups) can also be eliminated.

Our next steps will deal with the requested "extractions," as well as with the addition of a new group, Product Name, to ready the report for the specialized focus that the Purchasing department representatives have specified. These extractions include the Product Family, Product Category and Product Subcategory portions of the row axis, and the column levels Year and Quarter.

The targeted "extractions" are depicted in Illustration 37.


Illustration 37: Targeted Levels for Removal in the Customized Report

22.  Click the Groups tab.

Four groups appear in the Rows list box, and two additional groups appear in the Columns list box. The groups appear, with those targeted for elimination enclosed in red rectangles, as shown in Illustration 38.


Illustration 38: Existing Groups in the Clone Report

23.  Click the BrandSales_Product_Department field (the top in the Rows list) to select it.

24.  Click the Delete button to delete the group.

25.  Click the BrandSales_Product_Category field (now the top in the Rows list) to select it.

26.  Click the Delete button to delete the group.

27.  Click the BrandSales_Product_Subcategory field to select it.

28.  Click the Delete button to delete the group.

29.  Click the BrandSales_Year field (currently the top entry in the Columns list) to select it.

30.  Click the Delete button to delete the group.

31.  Click the BrandSales_Quarter field to select it.

32.  Click the Delete button to delete the group.

The Groups tab appears, after our deletions, as depicted in Illustration 39.


Illustration 39: Groups Tab after Eliminations

33.  Click the BrandSales_Brand_Name group (now the only entry in the Rows list) to select it.

34.  Click Edit.

The Grouping and Sorting Properties dialog for the group opens to the General tab.

35.  Click the Visibility tab.

36.  Click the Visible radio button, to enable static visibility for the BrandSales_Brand_Name group, in accordance with the information consumers' wishes.

37.  Uncheck the box marked "Visibility can be toggled by another report item" located on the lower half of the tab.

The Grouping and Sorting Properties dialog appears as shown in Illustration 40.


Illustration 40: The Grouping and Sorting Properties Dialog with Our Changes

38.  Click the Sorting tab.

39.  Click the top row in the Sort on list, just beneath the Expression label, to expose the selector.

40.  Using the selector, select the following from the picklist provided to populate the top row of the list:

=Fields!Brand_Name.Value

The Grouping and Sorting Properties dialog, Sorting tab, with our input, appears as we see in Illustration 41.


Illustration 41: Brand Name Field as Sort Criteria ...

41.  Click OK to accept our changes.

We return to the Groups tab.

The information consumers have also requested that the Product Name appear in the report; we will now add another group, subordinate to the Product Brand Name group, while we are "under the hood" with regard to the matrix properties.

42.  Click the Add button to the right of the rows list on the Groups tab.

The Grouping and Sorting Properties dialog appears, once again.

43.  Type the following into the Name box of the dialog.

BrandSales_Product_Name

44.  Click the top row in the Group on list, just beneath the Expression label, to expose the selector.

45.  Using the selector, select the following from the picklist provided to populate the top row of the list:

=Fields!Product_Product_Name.Value

The selector list, with our selection circled, appears as depicted in Illustration 42.

Click for larger image

Illustration 42: Selecting the Product Name Field as an Additional Group for the Report ...

The Grouping and Sorting Properties dialog, General tab, with our input, appears as shown in Illustration 43.


Illustration 43: Selecting the Product Name Field as an Additional Group for the Report ...

46.  Click the Sorting tab.

47.  Click the top row in the Sort on list, just beneath the Expression label, to expose the selector.

48.  Using the selector, select the following from the picklist provided to populate the top row of the list:

=Fields!Product_Product_Name.Value

The Grouping and Sorting Properties dialog, Sorting tab, with our input, appears as depicted in Illustration 44.


Illustration 44: Product Name Field as Sort Criteria ...

49.  Click OK to accept our input.

The Grouping and Sorting Properties dialog closes, returning us to the Groups tab of the Matrix Properties dialog, which now appears as shown in Illustration 45.


Illustration 45: The Groups Tab with Our Changes

50.  Click OK to accept changes and to close the Matrix Properties dialog.

We return to the Layout tab, where our report appears similar to that depicted in Illustration 46.


Illustration 46: Modified Report Layout ...

Let's rename the report, and eliminate all except the specified measure, Store Sales.

51.  Right-click the upper left textbox in the matrix, where we currently see the title FoodMart Sales in a white background.

52.  Select Expression... from the context menu that appears, as shown in Illustration 47.


Illustration 47: Modifying the Report Title ...

The Edit Expression dialog appears.

53.  Replace FoodMart Sales, within the Expression pane, with the following new title:

Product Summary - by Brand

The Edit Expression dialog appears as depicted in Illustration 48.


Illustration 48: Replacing the Existing Expression ...

54.  Click OK to accept the new expression and to close the Edit Expression dialog.

55.  With the textbox containing our new title still selected, replace the current font size with "16," via the selector on the Report Formatting toolbar, located between the Report Designer and the main menu, as shown in Illustration 49.


Illustration 49: Changing the Font Size in the Report Formatting Toolbar ...

NOTE: If the Report Formatting toolbar does not appear, summon it by selecting View --> Toolbars --> Report Formatting from the main menu, as partially depicted in Illustration 50.


Illustration 50: Summoning the Report Formatting Toolbar (Partial Menu View) ...

56.  Click the Store Profit measure expression, under the Store Profit label, once to select it, as shown in Illustration 51.


Illustration 51: Select with a Single Click ...

57.  Press the Delete key on the PC to delete the Store Profit measure column entirely.

58.  Perform the same select and delete process for the Store Cost measure.

The Layout tab for the report appears similar to that depicted in Illustration 52.


Illustration 52: The Layout Tab with Our Changes ...

59.  Open the Fields list for the ProductData Dataset we have defined. (If it is not docked in the development environment, open it by simply selecting View --> Fields from the main menu, as shown in Illustration 53).


Illustration 53: Select View --> Fields, as Required ...

60.  Right-click the Store Profit calculated field (it appears at the bottom of the fields list).

61.  Select Delete from the context menu that appears, as depicted in Illustration 54.


Illustration 54: Deleting the Store Profit Calculated Field ...

Let's take a look at the results of our handiwork so far.

62.  Click the Preview tab to execute the report.

RS022_MDX_DRILLTHROUGH executes briefly, and then returns data. Our report appears similar to that shown in Illustration 55.


Illustration 55: RS022_MDX_DRILLTHROUGH Appears in Preview (Partial View)

NOTE: Widen the columns as desired to prevent crowding / word wrapping in the data fields.

63.  Select File --> Save All to save all work to the present point.

We have now completed preparation of the primary report, which we will enhance in the section that follows to allow for drillthrough to a targeted detail report, which we will also create and align with the primary report.

Procedure: Enable Drillthrough from the Primary Report to a Targeted Detail Report

The next steps in constructing the specified drillthrough capability include the creation of the targeted detail report that the Purchasing consumers have specified. We will create the new report itself first, and then return to the primary report to enact settings to enable and align the drillthrough action. Because the primary report must reference a report parameter within the targeted report as part of aligning the two, we will create the targeted report, with its parameter, first.

Create a New Detail Report

We will create a basic detail report, to serve as the report we target from drillthrough within our primary report, in the steps that follow:

1.  Right click the Reports folder in the Solution Explorer.

2.  Select Add from the context menu that appears.

3.  Select Add New Item from the cascading menu that appears next, as depicted in Illustration 56.

Click for larger image

Illustration 56: Adding a New Detail Report to the Project

The Add New Item - RS022_MDX_DRILLTHROUGH dialog appears.

4.  Click Report in the Templates pane to create a new empty report.

5.  Type the following into the Name box at the bottom of the dialog:

RS022_DRILLTHROUGH_TARGET

The Add New Item - RS022_MDX_DRILLTHROUGH dialog appears as shown in Illustration 57.


Illustration 57: The Add New Item - RS022_MDX_DRILLTHROUGH Dialog with Our Input

6.  Click Open to create the new report.

RS022_DRILLTHROUGH_TARGET.rdl appears in the Reports folder of the Solution Explorer, as depicted in Illustration 58.


Illustration 58: The New Report Appears in the Reports Folder - Solution Explorer

We will first create the Dataset for the new report, before designing it further in Layout view.

7.  Click the Data tab within the Report Designer.

8.  Select <New Dataset ...> in the Dataset selector, as shown in Illustration 59.


Illustration 59: Select <New Dataset ...>

The Data Link Properties dialog appears, defaulted to the Connection tab.

9.  Click the Provider tab.

10.  Within the list under Select the data you want to connect to, select Microsoft OLE DB Provider for OLAP Services 8.0, as depicted in Illustration 60.


Illustration 60: Select Microsoft OLE DB Provider for OLAP Services 8.0

11.  Click Next to navigate to the Connection tab.

12.  Type the server name into the Data Source box (mine is MOTHER1).

13.  Select and complete the appropriate authentication choices for your environment, within the section labeled Enter information to log on to the server.

14.  In the selector labeled Enter the initial catalog to use, select FoodMart 2000.

The Data Link Properties dialog – Connection tab appears as shown in Illustration 61.


Illustration 61: The Data Link Properties Dialog – Connection Tab

15.  Click the Test Connection button, in the bottom right corner of the dialog.

Assuming that our input is correct in the settings for the Data Link Properties dialog, we will receive a message box indicating a successful connection, as depicted in Illustration 62.


Illustration 62: Testing Positive for Connectivity via the Test Connection Button ...

16.  Click OK to dismiss the message box.

17.  Click OK on the Data Link Properties dialog to accept settings and close the dialog.

We are returned to the Data tab of the Report Designer, where we see the FoodMart_2000 appears in the Dataset selector.

We will now create a new Dataset, based upon the MDX query used to define the Dataset of our primary report RS022_MDX_DRILLTHROUGH. In effect, we will use an identical query, with the DRILLTHROUGH statement added above the SELECT statement. Because we will be passing parameters to this report from the primary report, however, we will need to place the ultimate query within a string, which, as we have seen in other OLAP reporting articles in this series, complicates the construction of the query to some extent.

First, we will populate the Fields list with the SELECT query from the primary report. Next, we will modify the query with the DRILLTHROUGH statement, and prepare it to handle the passed parameters from the primary report upon drillthrough action.

18.  Type (or cut and paste) the following syntax into the Query pane of the Data tab:


SELECT 
  {[Measures].[Store Sales]} ON COLUMNS, 
  {Descendants([Product].[All Products],[Product].[Product Name],LEAVES)} ON ROWS
FROM 
  [SALES]
WHERE
  ([Time].[Year].[1997])

The new Dataset query appears as shown in Illustration 63.

Click for larger image

Illustration 63: The New Dataset Query

19.  Click the Run (!) button on the toolbar to execute the query.

The new Dataset appears in the Results pane, below the query, as partially depicted in Illustration 64.


Illustration 64: Partial View of the Returned Dataset

20.  Substitute the following for the third line (the ON ROWS specification) of the existing query.


{[Product].[Drink].[Alcoholic Beverages].[Beer and Wine].[Beer].[Good].
   [Good Light Beer]} ON ROWS

The Dataset query appears, with modifications enclosed in a red rectangle, as shown in Illustration 65.


Illustration 65: The Modified Dataset Query

21.  Click the Run (!) button on the toolbar, once again, to execute the query.

The new Dataset appears in the Results pane, below the query, as depicted in Illustration 66.


Illustration 66: Returned Dataset for the Modified Query

Our purpose is to create a query that selects a single tuple on each axis, so that we can use the DRILLTHROUGH statement in a "dummy" query, just long enough to instantiate the Data Fields for the report before we parameterize the query and convert it to a string. All we are doing here is modifying the query to select a single, qualified Product Name in the ON ROW specification. This single Product Name will be replaced by a parameter placeholder, as we shall see, in subsequent steps.

22.  Add the DRILLTHROUGH statement (just the keyword DRILLTHROUGH) to the query above SELECT, as shown in Illustration 67.


Illustration 67: Add the DRILLTHROUGH Statement above the SELECT Keyword

23.  Click the Run (!) button on the toolbar, as before, to execute the query.

The new Dataset appears in the Results pane, below the query, as partially depicted in Illustration 68.


Illustration 68: Partial Results of the DRILLTHROUGH Query

We note that the fields that appear in the new Dataset are those that we requested in our Drillthrough Options settings within the Cube Editor for the Sales cube earlier. (While order of columns is rigidly dictated by Analysis Services, this does not present an arrangement obstacle within Reporting Services, as we shall see). We have thus achieved the Drillthrough effect for a single Product Name, once again with the purpose of populating the Fields list appropriately.

24.  Click the Refresh Fields button atop the Data tab, to the immediate left of the Run (!) button, as shown in Illustration 69.


Illustration 69: Refreshing the Data Fields ...

The FoodMart_2000 Dataset Fields list is populated to reflect the returned Dataset, and thus the fields specified within the Sales cube Drillthrough Options, as depicted in Illustration 70.


Illustration 70: The Populated Fields List

We have populated the Fields list, and can next fit the query into string format. This involves removing carriage return breaks and making the query a single string. We are also ready to insert a parameterization "placeholder" for the report parameter we will create next. Finally, also within the new string, we will include the DRILLTHROUGH statement to leverage MDX to provide, as part of the newly returned Dataset, the data underlying the initial query, within the columns that we specified in Drillthrough Options from the Cube Editor earlier.

25.  Replace the query with the following exact syntax:


="DRILLTHROUGH SELECT { [Measures].[Store Sales]} ON COLUMNS, {[" & 
    Parameters!pX_ProductName.value & "]} ON ROWS FROM [Sales] WHERE 
        ([Time].[Year].[1997]) "

NOTE: Do not insert carriage returns / "line breaks." The query must remain as a single string, preceded by an equals ("=") sign and enclosed in quotation marks.

The new Dataset query appears as shown in Illustration 71.


Illustration 71: The Data Tab Dataset Query Replacement

The query refers, at this point, to a report parameter that has yet to be created; the query is therefore not yet functional. We will add this parameter (pX_ProductName) next. This report parameter will act as the mechanism that allows filtering upon the specific Product whose Store Sales value we select for drillthrough in the primary report.

26.  Select Report --> Report Parameters from the main menu, as depicted in Illustration 72.


Illustration 72: Adding a New Report Parameter

The Report Parameters dialog appears.

27.  Click the Add button on the dialog.

28.  Type the following into the Name box:

pX_ProductName

29.  Type the following into the Prompt box:

Product Name:

30.  Remove the checkmark from the Allow blank value checkbox.

31.  Ensure that, in the Available values section, the radio button to the immediate left of Non-queried is selected.

32.  Ensure that, in the Default values section, the radio button to the immediate left of None is selected.

The Report Parameters dialog appears as shown in Illustration 73.


Illustration 73: The Report Parameters Dialog for the Product Name Prompt

33.  Click OK to accept our settings and to close the Report Parameters dialog.

We return to the Data tab.

34.  Click the Preview tab to attempt to execute the report in Preview mode.

The report begins to run, and presents the prompt for the Report Parameter we have created. This serves as a "pre-test" that the modified Dataset query we have constructed is syntactically sound; recall that, because we are forced to enclose the query string within parentheses, the capability to execute the query with the standard Run (!) button is disabled.

Now that we have the Dataset in place, let's begin working within the Layout tab for the new report.

35.  Click the Layout tab.

The blank Layout tab for the report appears.

36.  Open the Toolbox to expose its component selection. (If it is not docked in the development environment, open it by simply selecting View --> Toolbox from the main menu, as depicted in Illustration 74.)


Illustration 74: Select View --> Toolbox, as Required ...

37.  Click the Table item.

38.  Click the Layout area, at approximately the center of the canvas (the mouse pointer has become a small table icon with a "crosshairs" symbol).

The Table Data Region appears on the canvas, similar to that shown in Illustration 75.


Illustration 75: Creating the Table Data Region on the Canvas

39.  From the Fields list, drag the_date to the middle row of the leftmost column in the Table Data Region, as depicted in Illustration 76.


Illustration 76: Inserting a Data Field into the Table

40.  From the Fields list, drag store_state to the middle row of the second column from the left (dropping it to the immediate right of the cell in which we dropped the_date in the preceding step).

41.  From the Fields list, drag store_city to the middle row of the third column from the left (dropping it to the immediate right of the cell in which we dropped store_state in the preceding step).

42.  Right-click the gray bar atop the third column.

43.  Select Insert Column to the Right from the context menu that appears, as shown in Illustration 77.


Illustration 77: Adding an Additional Column ...

A fourth, empty column is added.

44.  From the Fields list, drag store_name to the middle row of the new fourth column from the left within the Table Data Region.

45.  Right-click the gray bar atop the fourth column.

46.  Select Insert Column to the Right from the context menu that appears, as before.

47.  From the Fields list, drag brand_name to the middle row of the new fifth column from the left within the Table Data Region.

48.  Right-click the gray bar atop the fifth column.

49.  Select Insert Column to the Right from the context menu that appears, once again.

50.  From the Fields list, drag product_name to the middle row of the new sixth column from the left within the Table Data Region.

51.  Right-click the gray bar atop the sixth column.

52.  Select Insert Column to the Right from the context menu that appears, as before.

53.  From the Fields list, drag store_sales to the middle row of the new seventh column from the left (and the rightmost column) within the Table Data Region.

Now, let's narrow the columns to make working with them easier in the next steps.

54.  Hold down the SHIFT key, and beginning with the gray bar atop the first column, click the bar atop each of the seven columns in the Table Data Region to select / highlight all seven columns.

55.  With all columns selected, type a "1" n the very bottom box ("Width" in the Layout section) of the Properties window, as depicted in Illustration 78.


Illustration 78: Adjusting All Columns to 1-Inch Width ...

56.  Click just below the Properties window, to accept the setting and compact the columns in the Table Data Region.

The columns of the Table Data Region are each compacted to 1-inch widths.

57.  Right-click the gray bar (click anywhere within the table to make it reappear, if necessary) to the left of the middle row in the Table Data Region.

58.  Select Insert Group from the context menu that appears, as partially shown in Illustration 79.


Illustration 79: Adding a Group to the Newly Compacted Table ...

The Grouping and Sorting Properties dialog appears, defaulted to the General tab.

59.  Type the following into the Name box of the dialog.

gP_ProductName

60.  Click the top row in the Group on list, just beneath the Expression label, to expose the selector.

61.  Using the selector, select the following from the picklist provided to populate the top row of the list:

=Fields!product_name.Value

The Grouping and Sorting Properties dialog - General tab, with our input, appears as depicted in Illustration 80.


Illustration 80: The Completed Grouping and Sorting Properties Dialog – General Tab

62.  Click OK to accept our input and close the Grouping and Sorting Properties dialog.

The new Group Header and Footer appear.

63.  Type the following expression into the footer within the column currently bearing the column heading brand name:

="Detail Total:  " 

64.  Type the following expression into the footer within the column currently bearing the column heading product name (in the cell to the immediate right of our addition in the previous step):

=Parameters!pX_ProductName.Value

65.  Type the following expression into the footer within the column currently bearing the column heading store sales (in the cell to the immediate right of our addition in the previous step):

=SUM( Fields!store_sales.Value)

The Table Data Region, with our modifications to this point, appears as shown in Illustration 81.

Click for larger image

Illustration 81: The Table Data Region with Our Modifications to this Point ...

66.  Highlight all columns of the Table Data Region, once again.

67.  Select "8" in the Font Size selector to the right of the Font Name selector in the Report Formatting toolbar, as depicted in Illustration 82, to reduce the font throughout the table for more compactness.


Illustration 82: Modifying the Font Size for the Table Data Range

68.  Click the gray bar to the left of the top row (containing the column headings) to select the entire row.

69.  Click the Bold button in the Report Formatting toolbar (to the immediate right of the Font Size setting we made earlier).

70.  Click the gray bar to the left of the new Group Footer row (in which we inserted expressions earlier) to select the entire row.

71.  Click the Bold button in the Report Formatting toolbar, once again.

72.  Change each column heading name from the respective "Current Name" to the "New Name," as presented in Table 2.

Current Name

New Name

the date

Date

store state

State

store city

City

store name

Store

brand name

Brand

product name

Product

store sales

Sales


Table 2: New Column Headings ...

The Table Data Region, with our modifications to this point, appears as shown in Illustration 83.


Illustration 83: The Table Data Region with Most Recent Modifications ...

73.  Add a Textbox above the Table Data Region, by clicking the Textbox item in the Toolbox, and then clicking the Layout tab about midway between the top of the table and the top of the Layout tab, aligning the Textbox with the left edge of the tab, as depicted in Illustration 84.


Illustration 84: Place a Textbox Between the Table and the Top of the Layout Tab

74.  Stretch the Textbox to approximately the beginning of the Table column with heading "Brand."

75.  Type the following expression into the Textbox:

="Product Name:  " & Parameters!pX_ProductName.Value

76.  Select the Textbox, once again, and click the Bold button, once more.

The Data Tab, with our new addition, appears as shown in Illustration 85.


Illustration 85: The New Label Textbox ...

We could add a formal report title as well, but we will leave the detail report as is, as it is close enough to the consumer specifications to begin testing its operation after connecting it to the primary report. We will accomplish these remaining steps in the next section.

77.  Right-click the cell in the Detail (third from the top) row, containing the expression for the date (=Fields!the_date.Value), in the Date column of the Table Data Region.

78.  Select Properties from the context menu that appears, as depicted in Illustration 86.


Illustration 86: Formatting the Date ...

The Textbox Properties dialog appears.

79.  Ensure that the Standard radio button in the Format section (top right half of the dialog) is selected.

80.  Click Date in the list box underneath Standard, to select it.

81.  Click the bottom option in the box to the right (10/12/05 is the example format that appears here), as shown in Illustration 87.


Illustration 87: Selecting a Compact Date Option

82.  Click OK to accept changes and to close the Textbox Properties dialog.

83.  Leaving the same cell (containing =Fields!the_date.Value) selected, left justify the value by clicking the Align Left button in the Report Formatting toolbar, as depicted in Illustration 88.


Illustration 88: Left Justify the Date Field

84.  Click the gray bar atop the column labeled Brand.

85.  In the Properties window for the selected column, give the column a width of "1.5" (bottom setting in the window), as we have modified column widths in preceding steps.

86.  Click the gray bar atop the column labeled Product.

87.  In the Properties window for the selected column, give the column a width of "2.25" (bottom setting in the window), as before.

88.  Select File --> Save All to save all work to the present.

Connect the Detail Report to the Primary Report

Having created a primary report and a target detail report, we are ready to interconnect the two. This final set of steps will align the two reports, so that clicking upon a select value within our primary report will pass the appropriate information to the detail report to present drillthrough detail.

1.  Re-open the primary report, RS022_MDX_DRILLTHROUGH, if necessary, which we created from a clone of FoodMart Sales sample report earlier.

2.  Right click the Store Sales value, just under the Store Sales column heading, which contains the expression "=Sum(Fields!Store_Sales.Value)."

3.  Select Properties from the context menu that appears, as shown in Illustration 89.

Click for larger image

Illustration 89: Select Properties for the "Drillthrough Trigger" Value

The Textbox Properties dialog appears.

4.  Click the Advanced button at the bottom of the dialog.

The Advanced Textbox Properties dialog appears.

5.  Click the Navigation tab.

6.  In the Hyperlink Action section of the dialog, click the Jump to report radio button to enable the selector underneath it.

7.  Select RS022_DRILLTHROUGH_TARGET report (our "target" detail report) in the selector.

8.  Click the Parameters button to the right of the selector.

The Parameters dialog appears.

9.  Select pX_ProductName in the Parameter Name selector, top row of the Parameters list.

10.  Select the following in the Parameter Value section:

=Fields!Product_Product_Name.Value

The Parameters dialog appears, with our input, as shown in Illustration 90.


Illustration 90: Parameters Dialog with Our Selections

11.  Click OK to accept our selections and close the Parameters dialog.

We return to the Advanced Textbox Properties dialog, which appears as depicted in Illustration 91.


Illustration 91: Advanced Textbox Properties Dialog

12.  Click OK to dismiss the Textbox Properties dialogs.

Our alignment of the primary and target reports is now accomplished.

Verification of Operation: Drillthrough from the Primary Report to a Targeted Detail Report

Let's test the drillthrough report pair we have created in the preceding steps, by taking the following actions:

1.  Click the Preview tab to execute the primary report, RS022_MDX_DRILLTHROUGH.

The report executes and returns data as partially presented in Illustration 92, where we notice that it has generated 35 pages.


Illustration 92: Primary Report, Indicating 35 Pages (Partial View)

NOTE: Allow the report to finish generating all pages before taking the next step.

2.  Click the Store Sales value for American Chicken Hot Dogs (a value of $411 in the report matrix), five lines from the top, in the American Product Brand group.

We see the mouse pointer turn into a "pointing hand" when positioned over the value. The detail report, RS022_DRILLTHROUGH_TARGET is initialized upon clicking the $411, and generates data.

We see the correct Product Name selection, American Chicken Hot Dogs has, indeed been passed to the detail report. Let's verify, too, that the list of detail values appears to be complete, by confirming our total of $411 at the bottom of the drillthrough report.

3.  Click the Last Page (>|) button to the right of the page total atop the Preview tab (just under the Product Name parameter box containing American Chicken Hot Dogs), as depicted in Illustration 93.


Illustration 93: Proceed to the Last Page in the Report ...

We arrive at the second (and final) page of the drillthrough report, where we see the total of $410.76 appear for the detail values underlying the total in our primary report. Illustration 94 shows the bottom right hand corner of Page 2 of the report, with this total.


Illustration 94: Corroborating Total from the Bottom of the Last Page ...

We therefore note that the amounts agree, as the value upon which we drilled in the primary report is rounded.

4.  Select File --> Save All to save all work to the present.

5.  Exit Visual Studio.NET when ready.

We have corroborated the accuracy and completeness of the drillthrough report. We present the results to the Purchasing consumers, noting any remaining cosmetic enhancements that remain, and combining any additional requests with those in our pending list.

Conclusion ...

In this article, we extended an exploration of the MDX DRILLTHROUGH statement that we began in our MDX Essentials series, Drilling Through with MDX: The DRILLTHROUGH Statement. Our purpose was to explore an approach to providing drillthrough capabilities from a Reporting Services OLAP report in a way that leveraged the DRILLTHROUGH statement in a target detail report, in combination with the Drillthrough Options settings within an Analysis Services cube. To set the stage for our discussion and practice session, we presented a business requirement based upon the needs of a hypothetical client, which we then addressed with a drillthrough capability we assembled in Reporting Services.

We prepared for our practice session by creating a project within Reporting Services, and then creating a "clone" report (based upon an existing sample OLAP report to save time), from which we established a "launch point" for drillthrough actions. We enabled Drillthrough for the sample Sales cube, upon which our client's reports were based, within Analysis Services, and then examined configuration of Drillthrough to present the appropriate transactional details to its intended users;

We briefly discussed the syntax surrounding the DRILLTHROUGH statement, referring to Drilling Through with MDX: The DRILLTHROUGH Statement for an in-depth examination. We next created a "target" report that leveraged DRILLTHROUGH, examining sample results of the data we might expect to be returned in an MDX query with the DRILLTHROUGH statement added.

We next linked the primary report to the new target report. We then verified the operation of our report by executing the drillthrough action in our working example set. Throughout the article, we briefly discussed relevant concepts, as well as the results we obtained within the various actions we took in our practice session.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Reporting Services Forum.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved