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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Sep 18, 2006

BlackBelt Authoring: Conditional Drillthrough to Multiple Reports - Page 8

By William Pearson

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.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




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


















Thanks for your registration, follow us on our social networks to keep up-to-date