Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jul 17, 2006

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

By William Pearson

Add a New Report Parameter upon Which to Base Customization of the Linked Reports

1.  From our current position (Preview tab) within the report, click the Data tab.

2.  Within the Filter pane (atop the MDX Query Designer, to the right of the Metadata pane), select Sales Territory via the Dimension column drop-down selector.

3.  Select Sales Territory Group via the Hierarchy column drop-down selector.

4.  Select Equal to the immediate right, in the Operator column.

5.  Select the Europe, North America and Pacific checkboxes under the (expanded) All Sales Territories level within the selector underneath the Filter Expression column (to the immediate right of the Operator column, as shown in Illustration 20.

Illustration 20: Select Europe, North America, and Pacific within Filter Expression Selector

6.  Click OK to accept selections.

7.  Click the checkbox in the Parameters column to enable parameterization of the Sales Territory Group in the report.

The Filter pane appears, with the new row we have added for the Sales Territory Group parameter, as depicted in Illustration 21.

Illustration 21: Filter Pane with the Row We Have Added ...

Let's preview the report, once again, to verify operation of the parameter we have added.

8.  Click the Preview tab to execute RS031_Linked Report.rdl, once again.

9.  Select North America (deselect any other defaults that exist), within the new parameter picklist labeled Sales Territory Group, atop the report, as shown in Illustration 22.

Illustration 22: Select North America within the New Parameter Picklist

10.  Click View Report to execute the report.

The report executes, returning a properly filtered preview, showing data for the North America Sales Territory Group only, depicted in Illustration 23.

Illustration 23: The New Sales Territory Group Parameter in Action ...

Let's save our work to this point.

11.  From the main menu in the design environment, select File ---> Save All, as shown in Illustration 24.

Illustration 24: Select File --> Save All to Save Our Work So Far ...

We now have an OLAP report file within our Reporting Services 2005 Project, with which we can proceed to examine the establishment of a Linked Report scenario. Our final preparatory step will be to deploy the report to Report Manager, where it will inhabit a common folder, and which we will then access from SQL Server Management Studio to set up our Linked Reports.

Deploy the Report to a Common Folder in Report Manager

While we can create a Linked Report within either SQL Server Management Studio or Report Manager, the focus of this practice session will be to perform the setup from within SQL Server Management Studio. (For a step-by-step procedure for doing this from Report Manager, see my article BlackBelt Administration: Linked Reports in Report Manager, within the MSSQL Server Reporting Services series.) We will align and deploy the report to a common folder on the Report Server, assuming that we are logged in as a user with the appropriate Report Manager rights within Reporting Services, taking the following steps:

1.  Right-click the AdventureWorks Sample Reports project within the Solution Explorer.

2.  Select Properties from the context menu that appears, as depicted in Illustration 25.

Illustration 25: Select Properties ...

The AdventureWorks Sample Reports Properties Pages dialog opens.

3.  Ensure that the entries similar to those in Table 1 (and appropriate for your own environment) appear within the associated input boxes, within the Deployment section of the dialog:

In this Input Box:

Ensure the following:


False (Default)


Data Sources (Default)


AdventureWorks Sample Reports (Default)



Table 1: Deployment Section – Project Properties Pages Dialog

NOTE: While all of the above can reflect local settings of choice, the TargetServerURL must be a correct URL for the server upon which Report Manager is installed. The above reflects a name for one of my lab servers (TEMPLE001).

The AdventureWorks Sample Reports Properties Pages dialog, Deployment section, appears as shown in Illustration 26.

Illustration 26: The Project Properties Pages Dialog

4.  Click OK to accept settings and to dismiss the dialog.

5.  Right-click the new RS031_Linked Report within the Solution Explorer, once again

6.  Select Deploy from the context menu that appears, as depicted in Illustration 27.

Illustration 27: Begin Report Deployment ...

Deployment begins. We can observe the events of the process, through completion, within the Output window of the design environment, as shown in Illustration 28.

Illustration 28: Report Deployment Complete (Output Window) ...

We can verify the presence of the report we have deployed, along with the project data sources and the related containing folders, by simply going to the Report Manager Home page.

7.  Open an instance of Internet Explorer.

8.  Type in the URL of the Report Server (the server we input above, within the TargetServerURL property of the AdventureWorks Sample Reports Properties Pages dialog).

Report Manager appears, with the folders in evidence (depending on the settings made on the AdventureWorks Sample Reports Properties Pages dialog earlier, and, obviously, depending upon our local environments prior to our deployment steps), similar to those depicted in Illustration 29.

Illustration 29: Example View of Report Manager from Internet Explorer

While we might certainly create and work with Linked Reports in Report Manager (as we did in BlackBelt Administration: Linked Reports in Report Manager, we will now go to SQL Server Management Studio to perform the steps involved.

9.  Close Internet Explorer, and leave Report Manager.

10.  Select File --> Exit, to leave the Business Intelligence Development Studio, when ready.

MS SQL Archives

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