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.

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.

MSSQL Server Reporting Services
Introducing the Tablix Data Region: Basic Grouping Concepts
Introducing the Tablix Data Region in Reporting Services 2008
100% Stacked Column Chart for Analysis Services Data
XY (Scatter) Chart for Analysis Services Data
Simple Doughnut Chart for Analysis Services Data
Exploded Pie Chart for Analysis Services Data
Stacked Bar Chart for Analysis Services Data
Line Chart for Analysis Services Data
Stacked Column Chart for Analysis Services Data
A More Advanced Pie Chart for Analysis Services Data
Simple Pie Chart for Analysis Services Data
Simple Bar Chart for Analysis Services Data
Simple Column Chart for Analysis Services Data
Introducing Reporting Services Charts for Analysis Services
Mastering OLAP Reports: Parameterized Grouping
Mastering OLAP Reports: Parameterizing Number of "Top" Items with the MDX TopCount() Function, Part II
Mastering OLAP Reports: Parameterizing Number of "Top" Items with the MDX TopCount() Function, Part I
Mastering OLAP Reports: Parameterizing Number of "Look Back" Periods with the MDX LastPeriods() Function, Part II
Mastering OLAP Reports: Parameterizing Number of "Look Back" Periods with the MDX LastPeriods() Function, Part I
Support Parameterization from Analysis Services - Parameter Defaults
Parameterization from Analysis Services - Cascading Picklists
Support Parameterization from Analysis Services
Parameter Support Objects, Pt II: Support OLAP Parameter Defaults with Datasets
Reporting Services: Customize Automatically Created Parameter Support Objects
Snapshot Reports II: SQL Server Management Studio Perspective
Snapshot Reports I: Report Manager Perspective
Report Execution Caching II: Report Manager Perspective
Report Execution Caching I: SQL Server Management Studio Perspective
Report Session Caching in Reporting Services 2005
Black Belt Administration: Reporting Services Configuration Manager
Intelligent Layering: Leverage Conditional Formatting Logic from Analysis Services
Black Belt Administration: Performance Dashboard for Microsoft SQL Server, Part II
Black Belt Administration: Performance Dashboard for Microsoft SQL Server, Part I
Mastering OLAP Reports: Extend Reporting Services with Custom Code
Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. II
Black Belt Components: Support Simple Navigation with a Document Map
Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. I
Black Belt Components: Interactive Sorts within a Matrix Data Region
BlackBelt Authoring: Conditional Drillthrough to Multiple Reports
Mastering OLAP Reporting: Prototype KPIs in Reporting Services
BlackBelt Administration: Linked Reports in SQL Server Management Studio
BlackBelt Administration: Linked Reports in Report Manager
Mastering OLAP Reporting: Reporting with Analysis Services KPIs
Report Builder: Creating a Report Model
Mastering OLAP Reporting: Meet Business Needs with Matrix Dynamics, Part II
Mastering OLAP Reporting: Meet Business Needs with Matrix Dynamics, Part 1
Mastering OLAP Reporting: Display a Dataset Field in a Report Page Header
Interactive Sorting Within Reporting Services
MSSQL Server Reporting Services: Mastering OLAP Reporting: Multiple Value Selection in a Parameter Picklist
MSSQL Server Reporting Services : Mastering OLAP Reporting: Drilling Through Using MDX
MSSQL Server Reporting Services: Mastering OLAP Reporting: Relationally-Based Picklists for OLAP Reporting
MSSQL Server Reporting Services: Mastering OLAP Reporting: Extending Conditional Formatting: SWITCH and Drilldown Defaults
MSSQL Server Reporting Services: Mastering OLAP Reporting: Percent of Total - Chart Presentation Nuances
MSSQL Server Reporting Services: Mastering OLAP Reporting: Percent of Total - Two Perspectives
MSSQL Server Reporting Services : Mastering OLAP Reporting: Ad Hoc TopCount and BottomCount Parameters
MSSQL Server Reporting Services: Black Belt Components: Ad Hoc Sorting with Parameters
MSSQL Server Reporting Services: Black Belt Administration: "Governor" Capabilities: Report Execution Timeout
MSSQL Server Reporting Services : Black Belt Administration: Execution Log Performance and Audit Reports
MSSQL Server Reporting Services: Black Belt Administration: Prepare the Execution Log for Reporting
MSSQL Server Reporting Services: Black Belt Components: Ad Hoc Conditional Formatting for OLAP Reports
MSSQL Server Reporting Services : Black Belt Components: Manage Nulls in OLAP Reports
MSSQL Server Reporting Services: Reporting Services Basics: Create a Reusable Template Report
MSSQL Server Reporting Services: Master Chart Reports: Track Exchange Rates in a Line Chart
MSSQL Server Reporting Services: Master Chart Reports: Pie Charts in Reporting Services
MSSQL Server Reporting Services: Mastering OLAP Reporting: Cascading Prompts
MSSQL Server Reporting Services: Managing Reporting Services: Data-driven Subscriptions, and External Data Sources for Subscriber Data
MSSQL Server Reporting Services: Managing Reporting Services: Report Execution and Standard Subscriptions
MSSQL Server Reporting Services: Managing Reporting Services: Data Connections and Uploads
MSSQL Server Reporting Services: The Authoring Phase: Overview Part II
MSSQL Server Reporting Services: The Authoring Phase: Overview Part I
MSSQL Server Reporting Services: A New Paradigm for Enterprise Reporting








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers