Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Sep 18, 2006

BlackBelt Authoring: Conditional Drillthrough to Multiple Reports - Page 9

By William Pearson

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.

1.  Click the launch report tab to return to the report in the design environment.

2.  From the Layout view of the RS033_LAUNCH_Country_Cust Base report, click the Preview tab.

3.  Select FY 2004 in the Date.Fiscal Year parameter selector atop the report, when Preview opens and the report begins to initialize.

4.  Click the View Report button in the upper right corner.

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

Illustration 71: Basic Launch Report in Preview ...

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

5.  Click Europe, on the left side of the report.

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.

6.  Click the Back to Parent Report arrow atop the Preview, depicted circled in Illustration 72.

Illustration 72: Basic Launch Report in Preview ...

We return to the launch report.

7.  Click Pacific, on the left side of the report.

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

Illustration 73: Basic Launch Report in Preview ...

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.

8.  Select File -> Save All on the main menu, to save our work to this point.

9.  Select File -> Exit, to leave the Business Intelligence Development Studio, when ready.

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.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM