BlackBelt Authoring: Conditional Drillthrough to Multiple Reports - Page 9

September 18, 2006

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.