BlackBelt Authoring: Conditional Drillthrough to Multiple Reports

Monday Sep 18th 2006 by William Pearson
Share:

Enable drillthrough to different reports, based upon the values in a given data field on a launch report. BI Architect Bill Pearson presents the creation of a working prototype, containing conditional drillthrough settings.

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.

1.  Click Start.

2.  Navigate to, and click, the SQL Server Business Intelligence Development Studio, as appropriate.

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


Illustration 1: Launching SQL Server Business Intelligence Development Studio

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

3.  Close the Start page, if desired.

4.  Select File -> Open from the main menu.

5.  Click Project / Solution ... from the cascading menu, as shown in Illustration 2.


Illustration 2: Selecting a Project ...

The Open Project dialog appears.

6.  Browse to the AdventureWorks sample reports.

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

7.  Select the AdventureWorks Sample Reports.sln file within the sample reports folder, as depicted (circled) in Illustration 3.


Illustration 3: The Open Project Dialog, with Our Selection Circled ...

8.  Click Open.

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


Illustration 4: The Solution Opens within BI Development Studio ...

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.

9.  Double-click AdventureWorksAS.rds, within the Shared Data Sources folder seen in Solution Explorer.

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


Illustration 5: The Shared Data Source Dialog with Default Settings ...

10.  Click the Edit button on the Shared Data Source dialog.

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


Illustration 6: The Connection Properties Dialog with Default Settings ...

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).

11.  If appropriate, type the correct server / instance name into the Server name box of the Connection Properties dialog. (Mine is MOTHER1\MSSQL2K5, as depicted in Illustration 7.)


Illustration 7: Example Connection Properties Dialog with Corrected Settings ...

12.  Ensure that authentication settings are correct for the local environment.

13.  Click the Test Connection button.

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.


Illustration 8: Testing Positive for Connectivity ...

14.  Click OK to dismiss the message box.

15.  Click OK to accept changes, as appropriate, and to dismiss the Connection Properties dialog.

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


Illustration 9: Example Shared Data Source Dialog with Modified Settings ...

16.  Click OK to close the Shared Data Source dialog, and to return to the development environment.

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.

1.  In Solution Explorer, right-click the Reports folder, within the AdventureWorks Sample Reports project that we have opened.

2.  Select Add from the context menu that appears.

3.  Select New Item ... from the context menu that cascades from the first, as shown in Illustration 10.


Illustration 10: Select Add -> New Item ...

The Add New Item dialog appears.

4.  Click Report in the Templates pane, as required, to select it.

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

RS033_LAUNCH_Country_Cust Base

6.  Click the Add button in the bottom right corner of the dialog, which should appear as depicted in Illustration 11.


Illustration 11: Creating a New, Blank Report

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.


Illustration 12: The New Report Appears within Solution Explorer

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

7.  Click the Data tab within Report Designer, to open the Data view, if it is not already open.

8.  Using the "down arrow" button to the right of the Dataset selector, atop the Data tab, click <New Dataset...>, as depicted in Illustration 13.


Illustration 13: Adding a New Dataset ...

The Dataset dialog appears, defaulted to the Query tab.

9.  Type the following into the Name box atop the tab:

RS033_LAUNCH_Data

10.  Select AdventureWorksAS (shared) in the Data source box just below the Name box on the tab.

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


Illustration 14: The Dataset Dialog for Our New Dataset

11.  Click OK to accept the new Dataset definition, and to dismiss the Dataset dialog.

The MDX Query Designer opens in Design view.

12.  Within the Metadata pane, to the left of the design area, expand the Sales Territory dimension by clicking the "+" sign to its immediate left.

13.  Expand the Sales Territory hierarchy that appears underneath the dimension (bottom item within the dimension).

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


Illustration 15: The Expanded Sales Territory Dimension and Hierarchy ...

14.  Drag the newly exposed Sales Territory Group into the Results pane of the Dataset design area, as shown in Illustration 16.


Illustration 16: Adding Sales Territory Group to the Dataset ...

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.

15.  Within the Metadata pane, once again, expand the Date dimension.

16.  Expand the Fiscal folder that appears within the Date dimension.

17.  Expand the Fiscal hierarchy that appears within the Fiscal folder (bottom item within the folder).

18.  Drag the newly exposed Fiscal Year into the Results pane area, dropping it to the right of the Sales Territory Group column, as depicted in Illustration 17.


Illustration 17: Adding Fiscal Year to the Dataset ...

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.

19.  Within the Metadata pane, expand Measures.

20.  Expand the Internet Sales folder that appears within Measures.

21.  Drag the Growth in Customer Base measure (actually a calculation), into the Results pane area, dropping it to the right of the columns already in place, as shown in Illustration 18.


Illustration 18: Adding The Measures ...

22.  In the Filter pane, located in the upper right corner of the Query Designer, select Date via the Dimension column selector button.

23.  Select Date.Fiscal Year in the Hierarchy column, to the immediate right of the Dimension column, within the Filter pane.

24.  Select Equal in the Operator column.

25.  Leave the selector for the Filter Expression column in its default condition of empty.

26.  Place a check (by clicking) in the checkbox within the Parameters column.

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


Illustration 19: The Query Designer with our Filter Settings

NOTE: If the Results pane has not populated, as shown in the illustration above, click the Execute Query ("!") button in the toolbar atop the Query Designer, shown circled in Illustration 20.


Illustration 20: The Execute Query Button in the Toolbar

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.

27.  Click the Layout tab.

28.  From the Toolbox, drag a matrix data region to the Layout tab, as depicted in Illustration 21.


Illustration 21: Adding a Matrix Data Region ...

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.


Illustration 22: The Matrix Appears on the Report Canvas

29.  Within the Datasets window, expand RS033_LAUNCH_Data by clicking the "+" sign to its immediate left.

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

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


Illustration 23: The Data Fields Appear within the Datasets Window

30.  From the Datasets window, drag the Sales_Territory_Group field into the bottom left corner of the new matrix data region (the box watermarked "Rows"), as shown in Illustration 24.


Illustration 24: Dragging the First Data Field into the Matrix Data Region

31.  Drag the Fiscal_Year field into the top right corner of the new matrix data region (the box watermarked "Columns").

32.  Drag the Growth_in_Customer_Base field into the bottom right corner of the new matrix (the box watermarked "Data").

33.  Click the upper left corner box of the matrix data region to make the gray column and row borders appear, if necessary.

34.  Right-click the upper left corner of the gray border, as depicted in Illustration 25.


Illustration 25: Right-click the Upper Left Corner of the Matrix Border

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

35.  Click Properties ... on the context menu, as shown in Illustration 26.


Illustration 26: Select Properties ... from the Context Menu

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

36.  Click the Groups tab.

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


Illustration 27: Two Default Groups Appear on the Group Tab

37.  Click OK.

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

38.  Click the upper left corner box of the matrix data region, to cause the gray column and row borders to appear, once again, if necessary.

39.  Click the gray bar atop the leftmost column of the matrix, to highlight the column.

40.  Holding down the SHIFT key, click the bar atop the second column, so as to highlight both columns.

41.  Click the Center button in the toolbar, atop the design environment.

42.  Select "8" in the Font Size selector in the toolbar, to the left of the "Center" button, as depicted in Illustration 28.


Illustration 28: Highlight Both Columns, Center Their Contents, and Change the Font to "8"

43.  Right-click the bottom right box in the matrix, currently containing the expression =Sum(Fields!Growth_in_Customer_Base.Value).

44.  Select Properties in the context menu that appears, as shown in Illustration 29.


Illustration 29: Select "Properties" from the Context Menu ...

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


Illustration 30: The Textbox Properties Dialog Opens

45.  Select the Format tab.

46.  Type a "P" in the Format code box, in the upper left section of the Format tab, as shown in Illustration 31.


Illustration 31: Type "P" into the Format Code Box

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

47.  Click OK to accept our input, and to dismiss the Textbox Properties dialog.

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.

48.  Click the Preview tab.

49.  Using the parameter selector box, labeled Date.Fiscal Year, place a check mark alongside FY2004, as depicted in Illustration 32.


Illustration 32: Select FY2004 as the Filter ...

50.  Click the View Report button to execute our new report.

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


Illustration 33: The Report Appears, Presenting FY2004 Data

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.

51.  Click the Layout tab to return the report to design mode.

52.  Leave the report open, for easy access in later steps.

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.

1.  Right-click the Reports folder, once again, within in the Solution Explorer.

2.  Select Add -> Existing Item ... as we did earlier.

The Add Existing Item – AdventureWorks Sample Reports dialog appears.

3.  Navigate to the actual location of the sample reports (we provided the default path earlier), if the dialog has not defaulted thereto already.

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.


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

4.  Right-click the Sales Reason Comparisons report inside the dialog.

5.  Select Copy from the context menu that appears, as shown in Illustration 35.


Illustration 35: Performing a Quick Copy of the Sales Reason Comparisons Report

6.  Right-click somewhere in the white space inside the Add Existing Item – AdventureWorks Sample Reports dialog.

7.  Select Paste from the context menu that appears, as depicted in Illustration 36.


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

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

8.  Right-click the new file.

9.  Select Rename from the context menu that appears.

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

RS033_TARGET_Country_Cust Sales Info.rdl

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


Illustration 37: The Clone Report, RS033_LAUNCH_Country_Cust Sales Info.rdl

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

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

13.  Click Add on the dialog box to add the new report to report project AdventureWorks Sample Reports.

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.


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

14.  Right-click the new RS033_LAUNCH_Country_Cust Sales report within the Solution Explorer.

15.  Select Open from the context menu that appears, as shown in Illustration 39.



Illustration 39: Opening the New Report ...

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


Illustration 40: Our Report Opens in Layout View ...

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.

16.  Click the Preview tab to execute the RS033_LAUNCH_Country_Cust Sales Info report.

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


Illustration 41: The Report Appears with Default Parameter Selection

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.

17.  Click the Data tab to access the report’s datasets.

18.  Click the dropdown arrow on the right side of the Dataset selector, in the upper left corner of the Data tab.

19.  Click the ProductList dataset (the lower of the two) within the selector, as depicted in Illustration 42.


Illustration 42: Selecting the Unwanted Dataset ...

The dataset opens.

20.  Click the Delete Selected Dataset button, as shown in Illustration 43, to eliminate the dataset.


Illustration 43: Deleting the Dataset ...

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

21.  Click OK to confirm intentions, and to dismiss the dialog, as depicted in Illustration 44.


Illustration 44: Confirm Intent to Delete the Dataset

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

22.  In the Filter pane, click the Dimension box of the sole entry row (currently containing Product), to select it.

23.  Click the Delete button, as shown in Illustration 45, to eliminate the Filter pane entry.


Illustration 45: Deleting the Filter Pane Entry ...

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

24.  Select Report -> Report Parameters ... from the main menu, as depicted in Illustration 46.


Illustration 46: Accessing Report Parameters ...

The Report Parameters dialog opens.

25.  Select the sole Report Parameter that appears within the Parameters list, ProductCategory.

26.  Click Remove in the bottom left corner of the dialog, as shown in Illustration 47.


Illustration 47: Removing the Report Parameter ...

The Report Parameters dialog clears completely.

27.  Click OK to accept our deletion, and to dismiss the Report Parameters dialog.

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.

28.  In the Filter pane, select Sales Territory via the Dimension column selector button.

29.  Select Sales Territory Group in the Hierarchy column, to the immediate right of the Dimension column, within the Filter pane.

30.  Select Equal in the Operator column.

31.  Select All Sales Territories, via the checkbox within the selector for the Filter Expression column, as depicted in Illustration 48.


Illustration 48: Select All Sales Territories ...

32.  Click OK to accept the selection.

33.  In the Parameters column, to the immediate right of the Filter Expression column, click the checkbox to check it.

34.  Click the Dimension column box (which currently displays "<Select dimension>") in the next row, underneath the box where we selected Sales Territory earlier.

35.  Select Date via the Dimension column selector button that appears.

36.  Select Date.Fiscal Year in the Hierarchy column, to the immediate right of the Dimension column, within the Filter pane.

37.  Select Equal in the Operator column.

38.  Leave the box in the Filter Expression column blank.

39.  Place a check in the checkbox for the Parameters column, as we did for the box in the row above it.

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


Illustration 49: The Filter Pane with our Settings

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.

40.  Select Report -> Report Parameters ... from the main menu, as we did earlier.

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


Illustration 50: Pre-Defined Report Parameter Settings

41.  Ensure that the top Parameter (named SalesTerritorySalesTerritoryGroup by default), is selected.

42.  Within the Prompt section of the dialog, uncheck the Multi-value checkbox, which will leave all the checkboxes in the section de-selected.

43.  Within the Available values section, click the Non-queried radio button.

44.  In the Default values section at the bottom of the dialog, click the Null radio button.

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


Illustration 51: Affected Report Parameters Settings for SalesTerritorySalesTerritoryGroup

45.  Click the second Parameter (named DateFiscalYear by default) to select it.

46.  Within the Prompt section of the dialog, uncheck all checkboxes, as necessary.

47.  Within the Available values section, click the Non-queried radio button.

48.  In the Default values section at the bottom of the dialog, ensure that the Null radio button is selected.

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


Illustration 52: Affected Report Parameters Settings for DateFiscalYear

49.  Click OK to accept our modifications and to close the Report Parameters dialog.

50.  Select File -> Save All from the main menu, as shown in Illustration 53, to save our work to this point.


Illustration 53: Select File -> Save All

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.

1.  Right-click the Reports folder, once again, within in the Solution Explorer.

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


Illustration 54: Select Add -> New Item ...

The Add New Item – AdventureWorks Sample Reports dialog appears.

3.  Select Report within the Templates section of the dialog.

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

RS033_TARGET_No_Data.rdl

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


Illustration 55: Add New Item – AdventureWorks Sample Reports Dialog

5.  Click the Add button.

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.

6.  Click the Layout tab.

7.  From the Toolbox, drag a textbox data region to the Layout tab, as depicted in Illustration 56.


Illustration 56: Adding a Textbox Data Region ...

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


Illustration 57: The Textbox Appears on the Report Canvas

8.  Click inside the Textbox, to ensure the cursor is resident there.

9.  Type the following text into the textbox.

Data is not currently available for selected year.

10.  With the cursor still inside the textbox, click the Bold button in the toolbar.

11.  Click the Italics button next, as depicted in Illustration 58.



Illustration 58: Applying Formatting to the Newly Added Text ...

12.  Click the border of the textbox, to select the textbox itself.

13.  Placing the cursor over the left edge of the textbox (the cursor turns into a multi-directional arrows icon), grab the textbox and drag it to the left edge of the canvas, as shown in Illustration 59.


Illustration 59: Align the New Textbox with the Left Edge of the Canvas

14.  Placing the cursor over the right edge of the textbox (the cursor turns into a double-headed arrows icon), grab the edge of the textbox and widen it until the text inside it is fully visible, as depicted in Illustration 60.


Illustration 60: Widen the Textbox Until the Text Inside is Fully Visible

15.  Select File -> Save All from the main menu, as we did earlier.

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:

1.  Click the RS033_LAUNCH_Country_Cust Base.rdl report tab, atop the design environment, to return to the report.

2.  Within the launch report, click the Layout tab, if necessary.

3.  Right-click the bottom left-hand corner box in the matrix (which currently contains "=Fields!Sales_Territory_Group.Value".

4.  Select Properties from the context menu that appears, as shown in Illustration 61.


Illustration 61: Select Properties from the Context Menu

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

5.  Click the Navigation tab.

6.  Click the radio button to the immediate left of Jump to report, in the Hyperlink action section of the lower half of the Navigation 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.

7.  Click the Expression button ("fx"), as depicted in Illustration 62.


Illustration 62: Opening the Expression Editor ...

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.

8.  Type the following expression into the code window in the top pane:


=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.


Illustration 63: Conditional Expression in the Code Window of the Expression Editor

9.  Click OK to accept the expression and to return to the Navigation tab of the Textbox Properties dialog.

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

10.  Click the Parameters ... button to the right of the Expression button.

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.

11.  Type (or cut and paste) the following into the top row of the Parameter Name column in the Parameters list:

SalesTerritorySalesTerritoryGroup

12.  Click the top row of the Parameter Value column, the box to the immediate right of the box that we filled in the preceding step.

13.  Click the downward-pointing arrow that is enabled on the right side of the Parameter Value box.

14.  Select <Expression ...> from the dropdown picklist that appears, as depicted in Illustration 64.


Illustration 64: Opening the Expression Editor

The Expression Editor opens.

15.  Type the following expression into the code window in the top pane:


="{[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.


Illustration 65: Expression in the Code Window

16.  Click OK to accept the expression and to return to the Parameters list of the Parameters dialog.

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

17.  Type (or cut and paste) the following into the top row of the Parameter Name column in the Parameters list:

DateFiscalYear

18.  Click the second row of the Parameter Value column, the box to the immediate right of the box that we filled in the preceding step.

19.  Click the downward-pointing arrow that is enabled on the right side of the Parameter Value box, as we did earlier.

20.  Select <Expression ...> from the dropdown picklist that appears.

The Expression Editor opens.

21.  Type the following expression into the code window in the top pane:

=Parameters!DateFiscalYear.Value

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


Illustration 66: Expression in the Code Window

22.  Click OK to accept the expression and to return to the Parameters list of the Parameters dialog.

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.


Illustration 67: The Parameters List with Our Insertions

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.

23.  Click the top row within the Omit column, the box to the immediate right of the Parameters Value box.

24.  Click the downward-pointing arrow that is enabled on the right side of the Omit box, as we did with the Parameters Value box earlier.

25.  Select <Expression ...> from the dropdown picklist that appears.

The Expression Editor opens.

26.  Type the following expression into the code window in the top pane:


=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.


Illustration 68: Expression in the Code Window

27.  Click OK to accept the expression and to return to the Parameters list of the Parameters dialog.

28.  Click the second row within the Omit column, the box immediately beneath the Omit box we populated in the last step.

29.  Click the downward-pointing arrow that is enabled on the right side of the Omit box, as we did earlier.

30.  Select <Expression ...> from the dropdown picklist that appears.

The Expression Editor opens.

31.  Type the following expression into the code window in the top pane:


=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.)

32.  Click OK to accept the expression and to return to the Parameters list of the Parameters dialog.

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


Illustration 69: The Parameters List with Our Insertions

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

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.


Illustration 70: The Navigation Tab, with a Portion of Our Efforts in Evidence ...

34.  Click OK to accept our work, and to close the Textbox Properties dialog altogether.

We return to the Layout tab for the launch report.

35.  Select File -> Save All from the main menu, as we did earlier.

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.

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.

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