BlackBelt Administration: Linked Reports in SQL Server Management Studio - Page 5
July 17, 2006
Procedure: Create Linked Reports
As we discussed in the introduction, and in our previous article BlackBelt Administration: Linked Reports in Report Manager, a Linked Report acts as a duplicate of a "common" report file. This original, source report can have as many "children" (the Linked Reports) as we need, all of which share the .rdl of the common source report. The differences in the Linked Reports are varying parameters or other properties we assign to them, to "custom fit" them, for instance, to restrict the data they return to the specific audiences for whom we are providing the Linked Reports. Moreover, as we also emphasized before, the power of Linked Reports lies in the fact that they rely upon a single source .rdl file. This means a central point of maintenance that enables us to make enhancements or other modifications in a single file, while updating all the "child" reports simultaneously.
The creation of Linked Reports involves similar concepts and steps, whether we undertake the operation from Report Manager (as we did in BlackBelt Administration: Linked Reports in Report Manager), or from MSSQL Server Management Studio (our point of approach within this article). The differences in the mechanisms with which we accomplish our ends justify some hands-on exposure to the latter, however, and so we will examine the steps involved in the procedure that follows. Perhaps most important of all is to consider each approach, together with its nuances, efficiencies, security requirements and associated attributes, and to determine which fits best within out local environments for routine operations, as well as to be aware of a secondary, or "backup" approach to the method we initially chose as the best general procedure.
Create New Folders in SQL Server Management Studio to House the Linked Reports
Having deployed our sample report, we are ready to create Linked Reports within MSSQL Server Management Studio. First, we will create folders to house them; just as we created new folders at the Report Manager level in BlackBelt Administration: Linked Reports in Report Manager, we can create them within Management Studio. We will get some exposure to this process in the section that follows.
First, let's open SQL Server Management Studio.
1. Click the Start button.
2. Select Microsoft SQL Server 2005 within the Program group of the menu.
3. Click SQL Server Management Studio, as shown in Illustration 30.
The Connect to Server dialog appears, after the brief Management Studio splash screen.
4. Select Reporting Services in the Server type selector.
5. Type / select the server name (server name / instance, if appropriate) in the Server name selector.
6. Supply authentication information, as required in your own environment.
The Connect to Server dialog appears, with local settings, similar to that depicted in Illustration 31.
7. Click the Connect button to connect with the specified Report Server.
The SQL Server Management Studio opens.
8. In the Object Explorer pane (it appears by default on the left side of the Studio), expand the Home folder (click the "+" sign to its immediate left), appearing underneath the Report Server with which we are working.
The Home folder opens, exposing the folders that appear on the Home page (the folders we saw in our verification steps earlier), as shown in Illustration 32.
Let's create three folders in MSSQL Server Management Studio to house our Linked Reports. This way, we can manage security by controlling access to the folders, limiting each group of users, in our example, to its respective Sales Territory Group's folder. (We could also assign different roles to the Linked Reports, as well as taking other approaches, but we will keep things simple for our immediate purposes).
9. Right-click the Home Folder within the Object Explorer.
10. Select New Folder from the context menu that appears, as depicted in Illustration 33.
The New folder in / page opens.
11. Type the following into the Name field:
12. Type the following into the Description field:
European Sales Territory Reports
The relevant portion of the New folder in / page appears, with our input, as shown in Illustration 34.
13. Click OK to create the folder and to close the New folder in / page.
The Europe folder appears within the Home folder, within the Object Explorer.
14. Right-click the Home Folder within the Object Explorer, as we did before.
The New folder in / page opens.
15. Type the following into the Name field:
16. Type the following into the Description field:
North American Sales Territory Reports
17. Click OK to create the folder and to close the New folder in / page, once again.
18. Right-click the Home Folder within the Object Explorer, once more.
19. Select New Folder from the context menu that appears, as we did for the first two new folders.
The New folder in / page opens, once again.
20. Type the following into the Name field:
21. Type the following into the Description field:
Pacific Sales Territory Reports
22. Click OK to create the folder and to close the New folder in / page, as we did for our first two folder additions earlier.
The Object Explorer appears, with our newly added folders, as depicted in Illustration 35.
Having created folders to house the Linked Reports for each of the Sales Territory Groups, we are ready to create Linked Reports within those folders. We will accomplish this within the next section.