Linked Reports

In many cases, the security set up within Reporting Services restricts the folders a user can access. The sales department may be allowed to access one set of folders. The personnel department may be allowed to access another set of folders. The personnel department doesn’t want to see sales reports and, certainly, some personnel reports should not be seen by everyone in the sales department.

This works well—a place for everything and everything in its place—until you come to the report that needs to be used by both the sales department and the personnel department. You could put a copy of the report in both places, but this gets to be a nightmare as new versions of reports need to be deployed to multiple locations on the Report Server. You could put the report in a third folder accessed by both the sales department and the personnel department, but that can make navigation in the Report Manager difficult and confusing.

Fortunately, Reporting Services provides a third alternative: the linked report. With a linked report, your report is deployed to one folder. It is then pointed to by links placed elsewhere within the Report Catalog, as shown in Figure 10–23. To the user, the links look just like a report. Because of these links, the report appears to be in many places. The sales department sees it in their folder. The personnel department sees it in their folder. The fact of the matter is the report is only deployed to one location, so it is easy to administer and maintain.

Figure 10–23: A linked report

Creating a Linked Report

To demonstrate a linked report, we are going to make use of the InvoiceBatch Number Report from Chapter 4. This report shows the invoice amounts for companies in various cities. Galactic Delivery Services has sales offices in each of these cities and each sales office has its own folder within the GDS Report Catalog. A sales office should be able to access the Invoice-Batch Number Report in their own folder and see the invoices for customers in their city.

Deploying the Report to a Common Folder

We begin by deploying the report to a common folder. Here are the steps to follow:

1. Log in with a user name and password that has Content Manager rights in Reporting Services.

2. Start Visual Studio or the Business Intelligence Development Studio and open the Chapter04 project.

3. Modify the properties of the Chapter04 project as follows:

   Property	Value
   TargetDataSourceFolder	GalacticDeliveryServices/DataSources
   TargetServerURL	http://ServerName/ReportServer
   TargetReportFolder	Galactic Delivery Services/Shared Reports

   Replace ServerName with the appropriate server name or with localhost.

4. Deploy the Invoice-Batch Number Report.

5. Close the development environment.

Creating Linked Reports

Now that the report has been deployed to the Report Catalog, it is time to create our linked reports:

1. Open the Report Manager in your browser and navigate to the Galactic Delivery Services folder.

2. Create a new folder. Type Axelburg for Name and Axelburg Sales Office for Description.

3. Create another new folder. Type Utonal for Name and Utonal Sales Office for Description.

4. Navigate to the Shared Reports folder.

5. Click Show Details.

6. Click the icon in the Edit column next to the Invoice-Batch Number Report.

7. Click Create Linked Report. The Create Linked Report page appears.

8. Type Invoice-Batch Number Report for Name and Axelburg invoices in each batch for Description.

9. Click Change Location. The Folder Location page appears.

10. Select the Axelburg folder and click OK to return to the Create Linked Report page.

11. Click OK to create and execute this linked report in the Axelburg folder.

12. Type 01/01/2005 for Enter a Start Date and 12/31/2005 for Enter an End Date. Click View Report.

13. Click the link for the Axelburg folder at the top of the page.

14. Click Hide Details. You can see the linked report we just created looks like a regular report.

15. Navigate back to the Shared Reports folder.

16. Click Show Details.

17. Click the icon in the Edit column next to the Invoice-Batch Number Report.

18. Click Create Linked Report. The Create Linked Report page appears.

19. Type Invoice-Batch Number Report for Name and Utonal invoices in each batch for Description.

20. Click Change Location. The Folder Location page appears.

21. Select the Utonal folder and click OK to return to the Create Linked Report page.

22. Click OK to create and execute this linked report in the Utonal folder.

23. Select Utonal from the Select a City drop-down list. Type 01/01/2005 for Enter a Start Date and 12/31/2005 for Enter an End Date. Click View Report.

We have now successfully created and tested our two linked reports.

Managing Report Parameters in Report Manager

We have our linked reports, but we have not quite fulfilled all the business needs stated for these linked reports. The Axelburg sales office is supposed to be able to see only their own invoice data. The same is true for the Utonal sales office. We can meet these business needs by managing the report parameters right in the Report Manager. Here are the steps to follow:

1. Navigate to the Axelburg folder. Note the small chain links on the icon for the Invoice-Batch Number Report. This indicates it is a linked report.

2. Click the icon in the Edit column next to the Invoice-Batch Number Report.

3. Click Parameters on the left side of the screen. The Parameter Management page appears. Note, the City parameter has a default of Axelburg. Because this is the Axelburg folder, we leave that default alone. What we modify is the user’s ability to change this default value.

4. Uncheck the Prompt User check box in the City row. The user is no longer prompted for a city. Instead, the report always uses the default value. As you may have guessed, you can have a default value, you can prompt the user for the value, or you can do both. You must do at least one of these.

5. Check the Has Default check box in the StartDate row. Type 01/01/2005 for the default value for this row.

6. Check the Has Default check box in the EndDate row. Type 12/31/2005 for the default value for this row.

7. Click Apply to save your changes.

8. Select the View tab.

9. Notice you can no longer select a city. It is always Axelburg. Also, notice we now have default values for the date. Also worth noting is these default values are much easier to modify than the default values that are part of the report, because we can make changes without having to redeploy the report.

10. Navigate to the Utonal folder.

11. Click the icon in the Edit column next to the Invoice-Batch Number Report.

12. Click Parameters on the left side of the screen.

13. Change the City field’s default parameter to Utonal.

14. Uncheck the Prompt User check box in the City row.

15. Check the Has Default check box in the StartDate row. Type 01/01/2005 for the default value for this row.

16. Check the Has Default check box in the EndDate row. Type 12/31/2005 for the default value for this row.

17. Click Apply to save your changes.

18. Select the View tab.

Now we have the linked reports working just the way we need them. Not only did we simplify things by not deploying the report in multiple places, but we also were able to hardcode parameter values for each linked report.