BlackBelt Administration: Linked Reports in SQL Server Management Studio - Page 7

July 17, 2006

Customize a Report Parameter for Each Linked Report

Once we have created folders for each Sales Territory Group within SQL Server Management Studio, within which we also placed the Linked Reports, we have established a basis for at least rudimentary access control, to limit each Sales Territory Group to only the Linked Report that we wish for its members to see. Our objective is to allow each group to enter only its designated folder, containing a Linked Report whose definition will include a restriction that allows it to present only the data from the Sales Territory Group whose members will be allowed access the parent folder.

We will limit the data returned by each report via the filtering effects of the new Sales Territory Group parameter, by taking the following steps.

1.  Open an instance of Internet Explorer.

2.  Type in the URL of the Report Server once again, as required.

Report Manager appears, as we arrive on the Home page, where we see the new folders that we created within SQL Server Management Studio earlier.

3.  From the Home page of the Report Manager, re-enter the Europe folder we created earlier.

4.  Click the Show Details button in the upper right corner of the Contents tab, as shown in Illustration 42.


Illustration 42: Click Show Details ...

5.  Click the Properties icon, underneath the Edit column heading, to the left of the Sales Reason Comparison (Name column) Linked Report, as depicted in Illustration 43.


Illustration 43: Editing Linked Report Properties ...

The Properties page for the Sales Reason Comparison Linked Report opens.

6.  Click the Parameters link, on the left side of the page (where it resides with other links), as shown in Illustration 44.


Illustration 44: Click the Parameters Link on the Properties Page ...

The Properties – Parameters settings for the Sales Reason Comparison Linked Report appear.

7.  Within the row for the SalesTerritorySalesTerritoryGroup parameter, using the drop-down selector in the Default Value column, select [Sales Territory].[Sales Territory Group].&[Europe], as depicted in Illustration 45.


Illustration 45: Selecting a "Folder-Specific" Default Parameter

NOTE: If the selector does not appear to work – that is, it does not repopulate the Default Value box, then de-select the Has Default checkbox, click Apply, and then leave the folder by clicking the View tab, as if to execute the report. Return to the Parameters properties page (click the Properties tab and then the Parameters link, from the View tab), and re-select Has Default, which should be blank at this point. As if attempting to select a value in the Default Value box drop-down, click the downward arrow button. When the empty selector opens, type in the MDX qualified name ( [Sales Territory].[Sales Territory Group].&[Europe] , in this case), and it should repopulate the box when navigating out of the selector in the following step.

8.  Click the Hide checkbox to select it.

The checkmark appears in the Hide checkbox, and the Prompt User checkbox becomes automatically de-selected. Our new settings for the SalesTerritorySalesTerritoryGroup parameter appear as shown in Illustration 46.


Illustration 46: Our New Parameter Settings ...

The effect here is to force a default Sales Territory Group of Europe, with no consumer option of changing this filter, for the Europe Sales Reason Comparison report.

9.  Click the Apply button to accept and save the modifications to the parameter.

We will perform this parameter modification for the other two Linked Reports next, within the context of their respective Sales Territory Groups.

10.  Return to the Home page via the breadcrumb trail in the upper left corner of Report Manager, once again.

11.  From the Home page of the Report Manager, enter the North America folder we created earlier.

12.  Click the Properties icon, underneath the Edit column heading, to the left of the Sales Reason Comparison (Name column) Linked Report, as we did within the Europe folder earlier.

The Properties page for the Sales Reason Comparison Linked Report opens.

13.  Click the Parameters link on the Sales Reason Comparison Linked Report Properties page that appears next, on the left side of the page (where it resides with other links), as we did within the Europe folder before.

The Properties – Parameters settings for the North America Sales Reason Comparison Linked Report appear.

14.  Within the row for the SalesTerritorySalesTerritoryGroup parameter, using the drop-down selector in the Default Value column, select [Sales Territory].[Sales Territory Group].&[North America] (or use the procedure we discussed in my NOTE above, if issues arise).

15.  Click the Hide checkbox to select it, as we did earlier.

The checkmark appears in the Hide checkbox, and the Prompt User checkbox becomes automatically de-selected, as before. With this Linked Report, we are forcing a default Sales Territory Group of North America, with no consumer option of changing this filter, for the North America Sales Reason Comparison report.

16.  Click the Apply button to accept and save the modifications to the parameter properties.

We have only remaining Pacific Sales Reason Comparison report to modify, at this point.

17.  Return to the Home page via the breadcrumb trail in the upper left corner of Report Manager, as before.

18.  From the Home page of the Report Manager, enter the Pacific folder we created earlier.

19.  Click the Properties icon, underneath the Edit column heading, to the left of the Sales Reason Comparison (Name column) Linked Report, as we did within the Europe and North America folders earlier.

The Properties page for the Sales Reason Comparison Linked Report opens.

20.  Click the Parameters link on the Sales Reason Comparison Linked Report Properties page that appears next, on the left side of the page (where it resides with other links), as we did within the Europe and North America folders before.

The Properties – Parameters settings for the Pacific Sales Reason Comparison Linked Report appear.

21.  Within the row for the SalesTerritorySalesTerritoryGroup parameter, using the drop-down selector in the Default Value column, select [Sales Territory].[Sales Territory Group].&[Pacific].

22.  Click the Hide checkbox to select it, as we did earlier.

The checkmark appears in the Hide checkbox, and the Prompt User checkbox becomes automatically de-selected, as we saw for the two Linked Reports earlier. With this Linked Report, we are forcing a default Sales Territory Group of Pacific, again leaving no consumer option of changing this filter, for the Pacific Sales Reason Comparison report.

23.  Click the Apply button to accept and save the modifications to the parameter properties.

24.  Return to the Home page via the breadcrumb trail in the upper left corner of Report Manager, once again.

We have completed setup of the Linked Reports, establishing folders to which we sill limit access based upon the criteria of Sales Territory Group membership. In addition, we have restricted the data that each Linked Report retrieves and displays, through the addition of a hidden, default parameter filter to each, based, again, upon the intended Sales Territory Group audience. We will verify operation for expected results in the section that follows.








The Network for Technology Professionals

Search:

About Internet.com

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