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

July 17, 2006

Create Linked Reports within the SQL Server Management Studio

In our present practice example, we will create an individual Linked Report for each Sales Territory Group, within the respective folder we have created for that group of consumers. This will allow us to both filter the report to show the data for the Sales Territory Group to which the report belongs, and to restrict access to the report to the intended group through folder security (which the organization will establish at a later time). The end result will be that only members of a given Sales Territory Group will be able to access the report for that group – a report that is physically filtered to restrict the data it returns to that of the designated Sales Territory Group.

1.  In the Object Explorer pane, expand the AdventureWorks Sample Reports folder (click the "+" sign to its immediate left), appearing underneath the Home folder.

The AdventureWorks Sample Reports folder opens, exposing RS031 Linked Report, the report file that we modified and deployed to the Report Server in earlier sections.

2.  Right-click RS031 Linked Report within the Object Explorer.

3.  Select New Linked Report ... from the context menu that appears, as shown in Illustration 36.


Illustration 36: Creating the First Linked Report in MSSQL Server Management Studio ...

The New Linked Report page opens.

4.  Replace the default Name, Linked to RS031 Linked Report, in the Name box with the following name:

Sales Reason Comparison

5.  Click the ellipses (...) button to the right of the third input box from the top, labeled Create the linked report in this folder.

The Select item dialog appears.

6.  Click Europe within the dialog, to highlight / select it.

The Select item dialog appears, with our selection of the Europe folder, as depicted in Illustration 37.


Illustration 37: Assigning the New Linked Report to the Europe Folder

7.  Click OK to accept the folder selection modification, and to dismiss the Select item dialog.

8.  Type the following into the Description field:

European Territory Sales Reason
Comparison

The New Linked Report page appears, with our input, as shown in Illustration 38.


Illustration 38: The New Linked Report Page – with Our Input

9.  Click the OK button, to finalize creation of the new Linked Report within the new folder, and to dismiss the New Linked Report page.

Having created a Linked Report for Europe, we will repeat the steps immediately above, and create Linked Reports within the North America and Pacific folders next.

10.  Right-click RS031 Linked Report within the Object Explorer, once again.

11.  Select New Linked Report ... from the context menu that appears, as we did in the previous steps.

12.  Within the New Linked Report page that appears next, replace the default Name, Linked to RS031 Linked Report, in the Name box with the following name:

Sales Reason Comparison

13.  Click the ellipses (...) button to the right of the third input box from the top, labeled Create the linked report in this folder.

The Select item dialog appears.

14.  Click North America within the dialog, to highlight / select it.

15.  Click OK to accept the folder selection modification, and to once again dismiss the Select item dialog.

16.  Type the following into the Description field:

North American Territory Sales Reason Comparison

17.  Click the OK button, to again finalize creation of the new Linked Report within the new folder, and to dismiss the New Linked Report page.

All that remains, within our Linked Report creation requirements, is to add a Linked Report within the Pacific folder.

18.  Right-click RS031 Linked Report within the Object Explorer, once more.

19.  Select New Linked Report ... from the context menu that appears, as we did in the previous steps.

20.  Within the New Linked Report page that appears next, replace the default Name, Linked to RS031 Linked Report, in the Name box with the following name:

Sales Reason Comparison

21.  Click the ellipses (...) button to the right of the third input box from the top, labeled Create the linked report in this folder.

The Select item dialog appears.

22.  Click Pacific within the dialog, to highlight / select it.

23.  Click OK to accept the folder selection modification, and to once more dismiss the Select item dialog, as we did when assigning folders for the previous two Linked Reports.

24.  Type the following into the Description field:

Pacific Territory Sales Reason Comparison

25.  Click the OK button, to finalize creation of the new Linked Report within the new folder, and to dismiss the New Linked Report page, once again.

26.  Right-click the Europe folder in the Object Explorer.

27.  Select Refresh from the context menu that appears, as depicted in Illustration 39.


Illustration 39: Refresh the New Folder ...

28.  Expand the new Europe folder by clicking the "+" sign to its left.

The new Linked Report, Sales Reason Comparison, appears (its icon is a report image with a section of chain appearing at its bottom), as shown in Illustration 40.


Illustration 40: The New Sales Reason Comparison Linked Report for Europe Appears

29.  Repeat the Refresh procedure for the North America and Pacific folders, as necessary.

30.  Expand each of the North America and Pacific folders, to expose their contents, as we did for the Europe folder above.

The new Linked Reports appear, within their respective parent Sales Territory Group folders inside the Object Explorer tree, as depicted in Illustration 41.


Illustration 41: The New Linked Reports Appear in Object Explorer ...

We have finished our work within the SQL Server Management Studio for this session. We can now exit the Studio, and continue the remaining steps within Report Manager.

31.  Select File --> Exit, to leave the SQL Server Management Studio, when ready.

Having created the Linked Reports, we are now positioned to customize each to limit the data it returns to the respective Sales Territory Group users. We will handle this in our next section, and then verify the operation of the Linked Reports.








The Network for Technology Professionals

Search:

About Internet.com

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