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 Jul 17, 2006

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

By William Pearson

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.


Illustration 30: Opening SQL Server Management Studio

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.


Illustration 31: Opening SQL Server Management Studio

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.


Illustration 32: Exposing the Report Server Home Folders ...

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.


Illustration 33: Creating a New Report Manager Folder from the Management Studio ...

The New folder in / page opens.

11.  Type the following into the Name field:

Europe

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.


Illustration 34: The New Folder In / Page (Partial View), with Our Input

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:

North America

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:

Pacific

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.


Illustration 35: The New Folders within the Object Explorer ...

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.



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