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"), presenting an overview of its features, with tips and techniques for real-world use. For more information on the series in general, please see my initial Database Journal article, A New Paradigm for Enterprise Reporting. For the software components, samples and tools needed to complete the hands-on portion of this article, see BlackBelt Administration: Linked Reports in Report Manager, another article within this series.

About the BlackBelt Articles ...

As I state in BlackBelt Components: Manage Nulls in OLAP Reports and other articles of this subs-series, the BlackBelt articles represent an attempt to minimize the setup required in simply getting to a point within an article where we can actually perform hands-on practice with the component(s) or method(s) under consideration. I typically accomplish this by using existing report samples or other "prefabricated" objects that either come along as part of the installation of the applications involved, or that are otherwise readily accessible to virtually any organization that has installed the Microsoft business intelligence solution. While we will often have to refine the sample involved (we will typically create a copy, to allow the original sample to remain intact), to provide the specific backdrop we need to proceed with the object or procedure upon which we wish to concentrate, we will still save a great deal of time and distraction in getting to our objective. In some cases, we will have to start from scratch with preparation, but my intention with the BlackBelt articles will be to avoid this, if at all possible.

For more information about the BlackBelt articles, see the section entitled "About the BlackBelt Articles" in BlackBelt Components: Manage Nulls in OLAP Reports.

Overview

In this article, we will get some hands-on exposure to conditional drillthrough. We will discuss the general concepts, and then set up a scenario within which we work with basic reports to expose the steps for establishing this capability. As a part of our examination of the steps involved in establishing conditional drillthrough within Reporting Services, we will:

• Open the sample Report Server project, AdventureWorks Sample Reports, and ascertain connectivity of its shared data source;
• Create three basic reports - a "Launch" report, and two "Target" drillthrough reports - with which to perform our practice exercise;
• Modify the reports to support drillthrough linkages between them;
• Make enhancements to the launch report to support conditional drillthrough to the two target reports;
• Preview the new report set in action to ascertain its fitness to demonstrate conditional drillthrough in meeting hypothetical business requirements.

Conditional Drillthrough in Reporting Services

Objective and Business Scenario

Among the numerous ways to design interactivity into our reports, we can add navigational links to allow consumers to open other reports (or web pages), to jump to another location within the same report. Reporting Services supports Bookmark links (which enable consumers to jump to other areas within a report), Drillthrough links (which support jumping to other reports), and Hyperlinks (which support jumps to Web pages from the report). The focus of this article is the Drillthrough variety of link, although the concepts we explore can be extrapolated to Hyperlinks of other types.

"Drillthrough reports" (to which I often refer as "target" reports) are opened when a consumer clicks a link within another report (a "launch" report). Drillthrough reports typically contain details (hence representing a conceptual "drillthrough") about an item that is contained in an original summary report. As an example, we might craft a report for a chain of dialysis centers that presents a list of patients, for whom it summarizes lab test results for a given treatment location over a given month. The report could be designed with drillthrough capability, so that when an information consumer clicks on a specific patient name, another report opens that displays historical monthly lab readings, as well as other relevant details, for the selected patient.

Let’s say that we have a client, the Adventure Works organization, who has contacted us with a drillthrough need that is slightly more sophisticated than their current level of experience will support. The team with whom we are working is composed of a group of report developers and authors, who state that they have recently received a requirement from the Marketing Department to enhance an existing report to allow for conditional drillthrough, based upon the data field value selected, to one of two possible target reports.

Because we seek to leverage the immediate need for a training opportunity, we suggest the creation of three basic reports, based upon OLAP data stored within the Adventure Works sample cube.

• a "launch" report that presents the annual customer growth factor by Sales Territory Group;

• a "target" report that opens when we click upon a Sales Territory Group (and presents Sales information grouped by "Sales Reason" information collected by the company about its transactions);

• and a simple "message report" (stating that information is currently unavailable – say, because the data is questionable for some reason, and is being examined before re-release at a later time) that appears when an information consumer clicks upon one Sales Territory Group in particular.

Our client colleagues suggest that we use the Pacific Sales Territory Group as a practice example for triggering the second target report.

Practice

Our first objective is to create three basic reports with which to conduct our working conditional drillthrough sample. Keep in mind that the focus of our efforts is creating the relationships between the reports – the linkages that make them work – and not the reports themselves. Because of time limitations, we will be working with very simple reports – in reality, the business environment will typically require significantly more sophistication. The process of setting up conditional drillthrough is essentially the same in the real world, with perhaps a more complex set of underlying expressions involved, and a greater number of parameters in place.

We will perform our practice session from inside the MSSQL Server Business Intelligence Development Studio. For more exposure to the Business Intelligence Development Studio itself, and the myriad design, development and other evolutions we can perform within this powerful interface, see other articles in this series, as well as within my Database Journal series Introduction to MSSQL Server Analysis Services. In this article, we will be commenting only on the features relevant to our immediate practice exercise, to allow us to get to the focus of the article more efficiently.

Preparation: Create a Basic Set of Reports within the Reporting Services Development Environment

We will create a single "launch" report, and one "target" report, from scratch. In addition, we will "borrow" the design of a sample report to hasten the creation of a second "target" report. We will begin by opening the sample Report Server Project that ships with Microsoft Reporting Services 2005, to save more preparation time (you can create a new project if appropriate to your local environment).

Open the Sample Report Server Project and Ascertain Connectivity of the Shared Data Source

To begin, we’ll launch the SQL Server Business Intelligence Development Studio.

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

We briefly see a splash page that lists the components installed on the PC, and then Visual Studio opens at the Start page.

The Open Project dialog appears.

The reports are installed, by default (and, therefore, subject to reside in a different place on our individual machines), in the following location

C:\Program Files\Microsoft SQL Server\90\Samples\Reporting Services\Report Samples\AdventureWorks Sample Reports

The AdventureWorks Sample Reports solution opens, and we see the various objects within appear in Solution Explorer, as shown in Illustration 4.

Let’s first ensure we have a working shared data source. Many of us will be running "side-by-side" installations of MSSQL Server 2000 and MSSQL Server 2005. This means that our installation of the latter will need to be referenced as a server / instance combination, versus a server name alone.

The Shared Data Source dialog opens, and appears with default settings as depicted in Illustration 5.

The Connection Properties dialog opens, and appears with default settings shown in Illustration 6.

We note that the default Server name is "local." While this might prove an adequate setting for a PC with only MSSQL Server 2005 installed (default instance), in the case of many of our installations, the requirement here is for the server / instance combination that correctly identifies the correct MSSQL Server 2005 instance. (Clicking the Test Connection button at this point will provide confirmation whether we need to make this change).

A message box appears, indicating that the Test connection succeeded, assuming that our changes (or lack of same, as appropriate) are appropriate. The message box appears as shown in Illustration 8.

The Shared Data Source dialog appears, with our modified settings, similar to that depicted in Illustration 9.

We are now ready to create the reports we have mentioned, and to proceed with the practice exercise.

Create Basic "Launch" and "Target" Reports for the Practice Exercise

Create a Basic Launch Report from Scratch

Let’s create a basic OLAP report - a "launch" report that contains data upon which we will base conditional drillthrough - to fit the hypothetical business requirement we have outlined. We won’t spend a great deal of time with formatting and other nuances of presentation – the point here is to illustrate a conceptual option for conditional drillthrough.

The Add New Item dialog appears.

RS033_LAUNCH_Country_Cust Base

RS033_LAUNCH_Country_Cust Base.rdl, currently a blank canvas, opens within the design environment, and appears in the Solution Explorer, as shown in Illustration 12.

Let’s create a basic Dataset, upon which to base our new OLAP report.

The Dataset dialog appears, defaulted to the Query tab.

RS033_LAUNCH_Data

The Query tab of the Dataset dialog appears as shown in Illustration 14.

The MDX Query Designer opens in Design view.

The Sales Territory hierarchy expands, exposing its members within the Metadata tree, as depicted in Illustration 15.

Sales Territory Group appears as a column heading in the design area. Let’s add additional data fields that we will need in our query.

Fiscal Year now appears as a second column heading in the Results pane area. Let’s add the measure we need to meet the expressed business requirements.

The Query Designer appears, with our Filter settings at top, as depicted in Illustration 19.

We now have the data we need to support our simple report set requirements. Next, we will add a matrix data region, along with data, to the report canvas on the Layout tab.

NOTE: If the Toolbox is not visible, in the View menu, select Toolbox.

The matrix appears on the report canvas, as shown in Illustration 22.

The data fields within RS033_LAUNCH_Data appear in the Datasets window, as depicted in Illustration 23.

The gray borders become a lighter, opaque single-line border, as the context menu appears.

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

The Groups tab reveals the presence of two default Group templates, as depicted in Illustration 27.

The Matrix Properties dialog is dismissed and we return to Layout view.

The Textbox Properties dialog opens, defaulted to the General tab, as depicted in Illustration 30.

Here we are assigning a "percentage" format to the value within the textbox.

This is as far as we need to go for purposes of designing a basic launch report. Let’s preview the report, so as to get a feel for its fitness for our objectives.

RS033_LAUNCH_Country_CustBase.rdl executes, and appears as shown in Illustration 33.

We now have a basic OLAP report, which will act as the launch point for our drillthrough settings later. We might certainly "dress up" the report for deployment in a business scenario, but this design will serve us adequately for the subsequent exercises.

We will next create our target reports, and then modify all the reports as a group to enable them to interact via conditional drillthrough.

Create a Clone of the Sales Reason Comparison Report as One of Our Target Reports

As we mentioned earlier, we will base one of our two target reports upon a prefabricated sample report, a member of the sample set that can be installed along with Reporting Services 2005. We will first create a copy of the Sales Reason Comparison OLAP report, and then "prune it" a bit to meet our immediate needs.

The Add Existing Item – AdventureWorks Sample Reports dialog appears.

An example of the Add Existing Item – AdventureWorks Sample Reports dialog, having been pointed to the sample Reports folder (which contains the Sales Reason Comparisons report file we seek), appears as partially depicted in Illustration 34.

A copy of the Sales Reason Comparisons report appears within the dialog.

RS033_TARGET_Country_Cust Sales Info.rdl

The renamed copy of the Sales Reason Comparisons sample report appears as shown in Illustration 37.

RS033_LAUNCH_Country_Cust Sales Info appears in the Reports folder, within the AdventureWorks Sample Reports project tree in the Solution Explorer, as depicted in Illustration 38.

RS033_LAUNCH_Country_Cust Sales.rdl opens in Layout view, and appears as depicted in Illustration 40.

Let’s preview the report, so as to ensure that all is in working order before we begin our modifications to simplify it for our later use.

Execution begins (the report initially executes with the default parameter setting), and the report appears, as shown in Illustration 41.

Next, we’ll simplify the report to better suit it as a basic target report for our immediate needs. In essence, we will remove all except the primary dataset, as well as the parameters that currently exist in the report. Moreover, we will add new parameters via the Filter pane.

The dataset opens.

A Report Designer dialog appears, asking for confirmation of our wishes to delete the dataset.

The ProductList dataset disappears, as we are returned to the remaining ProductData dataset.

Although we have deleted the entry, which initially defined a Product Category parameter, we must also delete the related Report Parameter.

The Report Parameters dialog opens.

The Report Parameters dialog clears completely.

Having removed the parameter and extra dataset, we are ready to add two new parameters to customize the report to act as a target within our practice report set.

The Filter pane of the Query Designer appears, with our settings, as shown in Illustration 49.

Next, we will modify the new Report Parameters that were created when we established them within the Filter pane. Settings for Parameters are, in some cases, adequate as they are automatically created. However, the intended use of this report, as a drillthrough target, will drive a need to enable passthrough of parameters. This means we will need to adjust some of the pre-established settings.

The Report Parameters dialog opens, and appears, with the pre-defined settings for the initially selected parameter, as depicted in Illustration 50.

The relevant portion of the Report Parameters dialog for the SalesTerritorySalesTerritoryGroup parameter appears, with our adjustments, as shown in Illustration 51.

The relevant portion of the Report Parameters dialog for the DateFiscalYear parameter appears, with our adjustments, as depicted in Illustration 52.

We are now ready to move to the third, and last, report. This report will serve as a second target report.

Create a Basic Report from Scratch as Our Second Target Report

The last member of our practice report set will be a simple "message" report. This report will exist as a "second option" for drillthrough, and will not even require an underlying dataset. As we stated in our introductory comments, the point is to focus on the linkages between the launch and target reports, and to be distracted by peripheral report authoring, or other considerations, as little as possible. While the business environment would likely demand far more, we will design this report to simply generate a statement that "no data is available" for a given Sales Territory Group selection – a straightforward scenario with which to illustrate conditional drillthrough.

The Add New Item – AdventureWorks Sample Reports dialog appears.

RS033_TARGET_No_Data.rdl

The Add New Item – AdventureWorks Sample Reports dialog appears as shown in Illustration 55.

The dialog closes and the report file is created. The Report Designer opens, defaulting to the Data tab.

As we have learned from our client colleagues, only text is needed to support the simple target report requirement. We will add a textbox data region, along with text, to the report canvas on the Layout tab.

The textbox appears on the report canvas, as shown in Illustration 57.

Data is not currently available for selected year.

We now have a complete set of basic reports – enough to allow us to perform setup of conditional drillthrough in the next section. As an aside, within data regions we can typically use the NoRows property to perform the function of the simple second target report we have created in this procedure. Within the context of our specific example, we could likely have set this property for the launch report matrix; rather than having a "message report," such as our second target report, to return "no data," we might have placed the "data is not ... available" message within the NoRows property.

Our purposes here are simply to create a set of working reports quickly to allow us to focus on conditional drillthrough. The procedures we use to do so with the reports we have created, including this unlikely second target report, would be similar if each target report were a sophisticated, fully formatted report, with obvious report-specific differences in parameters to be passed, and so forth.

Procedure: Establish a Drillthrough Relationship between the Reports

We are ready to modify our report set to enable conditional drillthrough. We will assume that, for a given Sales Territory Group (the Pacific group), no data is available (although there may actually be data for same in the sample cube), so that when the consumers drill on that Group, they receive notification that no data exists (the message conveyed by the second of our target reports), instead of obtaining drillthrough to the data presented in the first target report.

Modify the Launch Report to Conditionally Drill Through to One of the Two Target Reports

Because we have already set our target report parameters to Non-queried in earlier steps, all that remains in establishing conditional drillthrough is setting properties within the launch report. We will perform these settings in this section, taking the following steps:

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

The report selector, where we would choose the target report in a simpler, single-target scenario, is activated. We will leave this blank, for now, and rely upon an expression to support conditional drillthrough.

The Expression Editor opens. Through this means, we will be supplying a conditional expression that will populate the report selector, versus a single report file name.

=IIF(Fields!Sales_Territory_Group.Value = "Pacific", "RS033_TARGET_No_Data",  

   

"RS033_TARGET_Country_Cust Sales Info")

The Expression Editor appears, with our expression in the code window, as shown in Illustration 63.

We see the report selector has been populated with our expression.

The Parameters dialog appears, displaying an empty Parameters list. This is where we specify the values that are passed from the launch report to the target report we have chosen in the Jump to report selector on the Navigation tab. Normally, we would select each of the target report parameters in the left column of the list, Parameter Name (the rows of the Parameter Name column would have a selector enabled, with which we could click a down arrow and see a picklist containing the report parameters detected within the target report we have designated). We would then match each target report parameter selection with the corresponding value in the launch report (parameter, field, or expression) that we wished to pass to the specified target report upon initiating a drillthrough action, using the Parameter Value column to the right of Parameter Name.

We note that the dropdown selectors are not automatically enabled in the present case. This is because we have populated the Jump to report selector on the Navigation tab with an expression, versus selecting a single report. This means we have to manually supply the information required in the Parameter Name column. We then have to supply the corresponding Parameter Values for the target report. An obvious question, in our case, is "what do we do when the first target report has multiple parameters, for which we need to supply values, but the second report has no parameters?"

Such scenarios are managed through the use of the Omit column in the Parameters list, as we shall see in the steps that follow.

SalesTerritorySalesTerritoryGroup

The Expression Editor opens.

="{[Sales Territory].[Sales Territory Group].["& 

   Fields!Sales_Territory_Group.Value &"]}"

Here, we are forming an MDX qualified name to pass. The relevant portion of the Expression Editor appears, with our expression in the code window, as shown in Illustration 65.

We see the Parameter Value has been populated with our expression. Next, we will populate the same columns in the row just beneath.

DateFiscalYear

The Expression Editor opens.

=Parameters!DateFiscalYear.Value

The relevant portion of the Expression Editor appears, with our expression in the code window, as depicted in Illustration 66.

We see that the Parameter Value has been populated with our expression. The Parameters list within the Parameters dialog appears, at this point, as shown in Illustration 67.

It is at this point that we must make accommodations for the fact that our drillthrough target report selection is conditional. This means that the Parameter Values we pass through must not only accommodate the first of our target reports (the Parameter Values we inserted into the respective columns in the Parameter list are specifically for the first target), but must make provision for the second target report. The second report, as we recall, does not contain parameters, and so we must provide for the passage of no parameter values in situations where the second report is conditionally chosen as the Jump to target. We will do so by leveraging the Omit column (whose values have assumed the default of False), within which we will place the respective expressions, themselves conditional, to force override of the Parameter Values that are in place, as appropriate.

The Expression Editor opens.

=IIF(Fields!Sales_Territory_Group.Value = "Pacific", 

   True, False)

The relevant portion of the Expression Editor appears, with our expression in the code window, as depicted in Illustration 68.

The Expression Editor opens.

=IIF(Fields!Sales_Territory_Group.Value = "Pacific", 

   True, False)

(The expression is identical to the one that we input to the Omit column for the SalesTerritorySalesTerritoryGroup parameter in the top row.)

The Parameters list on the Parameters dialog appears, with our expression in the code window, as shown in Illustration 69.

We return to the Navigation tab, where we see only a portion of the conditional expression we have inserted into the Jump to report selector, as depicted in Illustration 70.

We return to the Layout tab for the launch report.

Having established a conditional drillthrough relationship between the members of the report set, we are ready to test our reports to verify the effectiveness of our design.

Verification: Test Conditional Drillthrough for Expected Operation

We now have a launch report with two target reports in place, with conditional drillthrough established between them. Recall that our client colleagues have asked that we establish drillthrough from the launch report, enacted by clicking one of the Sales Territory Groups, to a customized version of the Sales Territory Group report (which we renamed RS033_TARGET_Country_Cust Sales Info). We have also designed the drillthrough mechanism to work in a conditional manner per the directions of the author / developer group. We have designed an "exception" into the drillthrough mechanism, such that clicking on the Pacific Sales Territory Group will return a different report (for purposes of our practice exercise, a simple "message" report that bears text stating that no data is available for the current year).

We will next look at the results of our handiwork. Let’s execute the launch report, and then test the conditional drillthrough mechanism we have employed.

The launch report executes momentarily, and then returns data, as shown in Illustration 71.

We can see that the report appears to execute as expected. Let’s attempt a drillthrough on the first Sales Territory Group, Europe.

The first of the target reports, RS033_TARGET_Country_Cust Sales Info, executes, and then returns data. We can see the Parameter Values that have been passed in the Report Parameter type-in boxes atop the report in Preview. (We can hide these from the end consumers; I typically do so, with most clients, for several reasons.)

Let’s take a spin with the drillthrough aligned to the second report, by choosing the Pacific Sales Territory Group as our point of drillthrough in the launch report.

We return to the launch report.

The launch report executes momentarily, and then opens, displaying the message as expected, and as shown in Illustration 73.

As we can see, the second target report is now executed and displayed, based upon the conditional drillthrough we have established within the Jump to report settings of the launch report. While we can devise far more sophisticated scenarios to accomplish conditional drillthrough to each of multiple reports, the concepts that we have shared in our practice session, that of implanting complementary conditional logic at both the Report and Parameters settings (within the launch report), can be extrapolated to meet the business needs of our clients and employers. And while there are multiple other approaches to the same end, the direct, out-of-the box capability of Reporting Services to handle conditional logic at this level represents yet another improvement over the capabilities of most of the once-dominant enterprise reporting applications that remain on the market today.

Conclusion ...

In this article, we obtained hands-on exposure to conditional Drillthrough within a Reporting Services report set. We discussed the general concepts, and then presented a scenario within which we could perform the steps of our practice session to meet the hypothetical business requirements of a client.

We prepared for our practice exercise by opening the sample Report Server project, AdventureWorks Sample Reports, after which we ascertained connectivity of its shared data source. We next created three basic reports - a "launch," and two "target," drillthrough reports - with which to perform our practice exercise. We then moved into the procedure for modifying the reports to support drillthrough linkages.

We primarily made enhancements to the launch report to support conditional drillthrough to the two target reports. We inserted conditional logic to select the target report and to determine the respective handling of parameter passage between the launch and selected target reports. Finally, we previewed the new report set in action to ascertain its fitness to demonstrate conditional drillthrough in meeting hypothetical business requirements. Throughout the steps of our practice session, we discussed, at appropriate junctures, various settings and techniques involved in achieving our objectives.

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

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