BlackBelt Administration: Linked Reports in SQL Server Management Studio

Monday Jul 17th 2006 by William Pearson
Share:

Create Linked Reports from within SQL Server Management Studio. BI Architect Bill Pearson examines another approach for using Linked Reports to provide multiple "versions" of a single-source report.

About the Series ...

This article is a member of the series MSSQL Server Reporting Services. The series is designed to introduce MSSQL Server Reporting Services ("Reporting Services"), with the objective of presenting an overview of its features, together with tips and techniques for its real-world use. For more information on the series, please see my initial Database Journal article, A New Paradigm for Enterprise Reporting.

As I have stated since the charter article of the series, published about the time Reporting Services was first publicly released, my conviction is that Reporting Services will commoditize business intelligence, particularly in its role as a presentation component within an integrated Microsoft BI solution. Having been impressed from my first exposure to this exciting application, when it was in early beta, my certainty in its destiny grows stronger by the day, as I convert formerly dominant enterprise business intelligence systems, such as Cognos, Business Objects / Crystal, MicroStrategy, and others, to the Reporting Services architecture. I receive constant requests to conduct strategy sessions about these conversions with large organizations in a diverse range of industries – the interest grows daily as awareness of the solution becomes pervasive. Indeed, the five- to six-plus figures that many can shave from their annual IT budgets represent a compelling sweetener to examining this incredible toolset.

Note: To follow along with the steps we undertake within the articles of this series, the following components, samples and tools are recommended, and should be installed / accessible, according to the respective documentation that accompanies MSSQL Server 2005:

Server Requirements

  • Microsoft SQL Server 2005 Reporting Services

  • Microsoft SQL Server 2005 Database Services

  • The AdventureWorks sample databases (relational and Analysis Services)

  • Microsoft SQL Server 2005 Analysis Services

Client Requirements

  • Microsoft Internet Explorer 6.0 with scripting enabled

  • Business Intelligence Development Studio (optional)

Sample Files

For purposes of the practice exercises within this series, we will be working with samples that are provided with MSSQL Server 2005. The samples with which we are concerned include, predominantly, the Adventure Works DW sample databases and other samples. This database and companion samples are not installed by default in MSSQL Server 2005. The samples can be installed during Setup, or at any time after MSSQL Server has been installed.

The topics "Running Setup to Install AdventureWorks Sample Databases and Samples" in SQL Server Setup Help or "Installing AdventureWorks Sample Databases and Samples" in the Books Online (both of which are included on the installation CD(s), and are available from www.Microsoft.com and other sources, as well), provide guidance on samples installation. Important information regarding the rights / privileges required to accomplish samples installation, as well as to access the samples once installed, is included in these references.

Note: Current Service Pack updates are assumed for the operating system, along with the applications and components listed above and the related Books Online and Samples. Images are from a Windows 2003 Server environment, but the steps performed in the articles, together with the views that result, will be quite similar within any environment that supports MSSQL Server 2005 and its constituent applications.

About the BlackBelt Articles ...

As I have stated in earlier BlackBelt articles, one of the greatest challenges in writing tutorial / procedural articles is creating each article to be a freestanding document that is complete unto itself. This is important, because it means that readers can complete the lesson without reference to previous articles or access to objects created elsewhere. When our objective is the coverage of a specific technique surrounding one or more components of a report, a given administrative function surrounding reports in general, and other scenarios where the focus of the session is not the creation of reports, per se, challenges can arise because a report or reports often has to be in place before we can begin to cover the material with which the article concerns itself.

The BlackBelt articles represent an attempt to minimize the setup required in simply getting to a point within an article where we can actually perform hands-on practice with the component(s) or method(s) under consideration. We will attempt to use existing report samples or other "prefabricated" objects that either come along as part of the installation of the applications involved, or that are readily accessible to any organization that has installed the application. While we will often have to make modifications to the sample involved (we will actually create a copy, to allow the original sample to remain intact), to refine it to provide the specific backdrop we need to proceed with the object or procedure upon which we wish to concentrate, we will still save a great deal of time and distraction in getting to our objective. In some cases, we will have to start from scratch with preparation, but my intention with the BlackBelt articles will be to avoid this, if at all possible.

For more information about the BlackBelt articles, see the section entitled "About the BlackBelt Articles" in BlackBelt Components: Manage Nulls in OLAP Reports.

Overview

In BlackBelt Administration: Linked Reports in Report Manager, we introduced Linked Reports. We noted that, as administrators of enterprise reporting systems can attest, management of the business intelligence solution does not end when we deploy / publish the reports to the Report Server. We discussed the fact that, within Reporting Services, many properties and options can be set and maintained at the Report Server level, through the Report Manager, or an alternative interface. We also noted that, even in the simplest implementations, there is usually at least some need to exercise control over what information consumers can access. We then mentioned that this is often initially handled through the management of folder security, which comes "out of the box" with Reporting Services, and often provides a great starting point for new implementations. We related a reason that this is so: we can thus get started with the presentation layer of a business intelligence system, affording the organization the capability to gather business requirements and develop reports that consumers can "road test." In this manner, we reasoned, progress can be taking place toward the crafting of a report library, in the developmental meantime, regardless of the ultimate report delivery mechanism (dashboards, scorecards, portals, other customized interfaces, or combinations of these things). The idea, we stated, was that it is often to our advantage to begin development quickly, and to deliver reports that can be executed by users, who can be feeding them back with their input, as we continue to rapidly turn out the new report library.

As I stated in BlackBelt Administration: Linked Reports in Report Manager, a common scenario I come across in implementing Reporting Services lies in using folder security to control report access, within the development cycles and often beyond. In many cases, reports that reside in a given folder (say, a set of reports for the corporate finance department), are totally different from reports that are deployed within other folders (an example of which might be a folder dedicated to housing inventory management reports). Nevertheless, we often encounter situations where the same report may need to be placed within multiple folders. Placing duplicates of the same report in the folders involved might seem like a simple solution, but can lead to added overhead from an administration perspective when maintenance events, such as report modifications, occur.

As we learned in BlackBelt Administration: Linked Reports in Report Manager, Reporting Services offers a way to handle this without the overhead: Linked Reports. When we use Linked Reports, we create a single "master" report, and then deploy the .rdl file to a single folder. We then create Linked Reports within the other folders from which access to the report is required. We noted that the concept of Linked Reports becomes quite attractive when we come across needs to make either identical reports, or multiple similar versions of the same report, available in various folders. These capabilities have proven themselves quite popular with many of my clients who faced scenarios such as the need to provide an identical (in layout / format) sales report to numerous departments, regions or even individual salespersons, but with each report's contents specific to the department, region or salesperson accessing or receiving it. Linked Reports allow us to provide this capability to our clients and / or the consumers within our organizations with ease, while affording a single point of actual deployment and centralized administration.

In BlackBelt Administration: Linked Reports in Report Manager, we got some hands-on exposure to Linked Reports, performing most of the post-deployment setup at the Report Manager level. In this article, we will again discuss general concepts, to make it a "standalone procedure" for those who wish to perform many of the setup steps for Linked Reports from within SQL Server Management Studio, where many organizations choose to centralize BI maintenance and management functions, once they are deployed. We will set up a scenario, as we did in the previous article, within which we deploy a sample report, and then will perform the steps of setting up Linked Reports in multiple folders. Our intended results will be identical to those of the last article; we will simply accomplish much of the setup from SQL Server Management Studio instead of from the Business Intelligence Development Studio. As a part of our examination of the steps involved in establishing Linked Reports within Reporting Services, we will:

  • Open the sample Report Server project, AdventureWorks Sample Reports, and ascertain connectivity of its shared data source;
  • Create a clone of an existing sample OLAP report, with which to perform our practice exercise;
  • Make limited structural changes to the clone report, based upon a sample SQL Server Analysis Services cube, to meet the business requirement of a hypothetical group of information consumers to use it as a basis for Linked Reports;
  • Preview the clone report to ascertain its readiness for deployment;
  • Deploy the report to a common folder within Report Manager;
  • Create folders within SQL Server Management Studio, to house Linked Reports for different consumer groups;
  • Create Linked Reports (again from within SQL Server Management Studio), within the new consumer group folders, based upon the common source .rdl file;
  • "Hard code" a default, hidden parameter within each Linked Report to restrict the data to the intended consumer group audiences;
  • Preview a Linked Report from the presentation layer, to ascertain the effectiveness of our solution.

Linked Reports in Reporting Services 2005

Objective and Business Scenario

Linked Reports, as we stated in BlackBelt Administration: Linked Reports in Report Manager, represent a compelling option when we wish to afford the same report format to various consumers, or consumer groups, while restricting each of the groups to only the data that they need to see to perform their functions. Examples might include a general patient report that gives key diagnostic data, and which appeals to all the doctors within a large clinic. Because certain patients are assigned to certain doctors, we might wish to provide all doctors the same report body, but only make data available to each doctor for the patients that are assigned to his care. With Linked Reports we might make these "multiple versions" of the same patient report available in various folders – with each doctor having access to his own folder, and the patient report in each folder (among other reports, perhaps) restricted to the delivery of information relating to those patients assigned to the doctor only. Linked Reports would answer this need well, while affording a single point of actual deployment and centralized administration, as we shall see in our hands-on practice session.

In the following sections, we will perform the steps required to deploy a simple OLAP report, with which we can apply the principals of Linked Reports to custom-filter the report for different consumer groups, just as we did in BlackBelt Administration: Linked Reports in Report Manager. To provide a report upon which we can practice the steps of our hands-on exercise, we will begin with the Sales Reason Comparisons sample report, based upon the Adventure Works cube contained within the Analysis Services database, Adventure Works DW, which is available with the installation of the MSSQL Server 2005 samples. The Sales Reason Comparisons report is intended to present comparison summary data from the Adventure Works cube. For the purposes of our article, we will say that we are working with developers and report authors within the Office of the Senior Vice President of Sales of our client, the Adventure Works organization.

To illustrate the somewhat basic business requirement (we'll use the same one we presented in BlackBelt Administration: Linked Reports in Report Manager, in order to support easy comparability of the steps we take here with those of the previous article – where we create Linked Reports within Report Manager, versus SQL Server Management Studio), let's say that the developers / authors have expressed the need to allow members of each of the organization's three Sales Territory Groups, (Europe, North America, and Pacific), to continue to use a report (the Sales Reason Comparisons report) that has met with their acceptance since AdventureWorks' migration to the integrated Microsoft BI solution. We had prepared this report for them in an earlier engagement, where we converted many existing reports from the predecessor enterprise reporting application, as a part of unifying many disparate – and expensive – applications within the Microsoft solution. Because the conversion saved the organization six figures in licensing costs annually, they were able to retain the employees already in place and avoid an alternative proposal to offshore the business intelligence operation (using the previously existing reporting application) in an attempt to meet budgetary challenges.

The client representatives have asked us to present a means whereby they can restrict the data presented by the Sales Reason Comparisons report to the respective Sales Territory Group to which a given consumer belongs. In this way, they would like a simple means of preventing access, say, of a member of one group to the report data relating to another group. They realize that they can create three copies of the same report, and then filter each for the respective group's data, but are concerned about having to maintain three copies of the same report, as they fear that the "versions" may soon become "out of sync" with independent changes.

The Sales Reason Comparisons report, originally created to present the data for all Sales Territory Groups, currently appears as depicted in Illustration 1.


Illustration 1: Original Sales Reason Comparisons Report

As part of our typical business requirements gathering process, we listen attentively to the details, formulating, in the background, an idea of the steps we need to take in modifying a copy of the report to produce the desired results. Once we grasp the stated need, and confirm our understanding with the intended audience, we begin the process of modifying the Sales Reason Comparisons report to satisfy the information consumers. Because the authors are not certain that they will discard the original report completely (it may continue to be used for various higher-level executive meetings, for example), we will make these modifications to a copy we independently create from the original.

Considerations and Comments

If the sample Adventure Works DW Analysis Services database was not created as part of the initial MSSQL Server 2005 installation, or was removed prior to your beginning this article, please see the MSSQL Server 2005 Books Online or other documentation for the procedure to put the database in place, together with the sample reports. As of this writing, a copy of the samples can be obtained from the installation CD or via download from the appropriate Microsoft site(s).

Practice

Our first objective is to create a copy of the Sales Reason Comparisons sample report, with which we can implement the minimal enhancements we will determine to be appropriate from discussions with the author / developer group. We will perform this portion of our practice session from inside the Business Intelligence Development Studio (where we would initially design and test a report in most environments), which makes its home within Visual Studio .NET 2005. We will then deploy the report file, and undertake our work with Linked Reports from SQL Server Management Studio.

NOTE: For more exposure to the SQL Server Business Intelligence Development Studio itself, and the myriad design, development and other evolutions we can perform within this powerful interface, see articles in this and my other Database Journal series, Introduction to MSSQL Server Analysis Services. In this article, we will be commenting only on the features relevant to our immediate practice exercise, to allow us to get to the focus of the article more efficiently.

Preparation: Create a Clone Report within the Reporting Services Development Environment

For purposes of our practice session, we will create a copy of the Sales Reason Comparisons report, one of several samples that are available with (and installable separately from) the Microsoft SQL Server 2005 integrated business intelligence solution. Creating a "clone" of the report means we can make changes to our report while retaining the original sample in a pristine state – perhaps for other purposes, such as using it to accompany relevant sections of the Books Online, and other documentation, in learning more about Reporting Services in general.

Taking preparatory steps, and then making the enhancements to the report to add the functionality to support the subject of our lesson, can be done easily within the Business Intelligence Development Studio environment. Working with a copy of the report will allow us the luxury of freely exploring our options, and leave us a working example of the specific approach we took, to which we can refer in our individual business environments.

Open the Sample Report Server Project and Ascertain Connectivity of the Shared Data Source

To begin, we will launch the SQL Server Business Intelligence Development Studio.

1.  Click Start.

2.  Navigate to, and click, the SQL Server Business Intelligence Development Studio, as appropriate.

The equivalent on my PC appears as depicted in Illustration 2.


Illustration 2: Launching SQL Server Business Intelligence Development Studio

We briefly see a splash page that lists the components installed on the PC, and then Visual Studio opens at the Start page.

3.  Close the Start page, if desired.

4.  Select File --> Open from the main menu.

5.  Click Project / Solution ... from the cascading menu, as shown in Illustration 3.


Illustration 3: Selecting a Project ...

The Open Project dialog appears.

6.  Browse to the AdventureWorks sample reports.

The reports are installed, by default (and, therefore, subject to be installed in a different location on our individual machines), in the following location

C:\Program Files\Microsoft SQL Server\90\Samples\Reporting Services\Report Samples\AdventureWorks Sample Reports

7.  Select the AdventureWorks Sample Reports.sln file within the sample reports folder, as depicted (circled) in Illustration 4.


Illustration 4: The Open Project Dialog, with Our Selection Circled ...

8.  Click Open.

The AdventureWorks Sample Reports solution opens, and we see the various objects within appear in Solution Explorer, as shown in Illustration 5.


Illustration 5: The Solution Opens within BI Development Studio ...

Let's first ensure we have a working shared data source. Many of us will be running "side-by-side" installations of MSSQL Server 2000 and MSSQL Server 2005. This means that our installation of the latter will need to be referenced as a server / instance combination, versus a server name alone.

9.  Double-click AdventureWorksAS.rds, within the Shared Data Sources folder seen in Solution Explorer.

The Shared Data Source dialog opens, and appears with default settings as depicted in Illustration 6.


Illustration 6: The Shared Data Source Dialog with Default Settings ...

10.  Click the Edit button on the Shared Data Source dialog.

The Connection Properties dialog opens, and appears with default settings shown in Illustration 7.


Illustration 7: The Connection Properties Dialog with Default Settings ...

We note that the default Server name is "local." While this might prove an adequate setting for a PC with only MSSQL Server 2005 installed (default instance), in the case of many of our installations, the requirement here is for the server / instance combination that identifies the correct MSSQL Server 2005 instance. (Clicking the Test Connection button at this point will provide confirmation whether we need to make this change).

11.  If appropriate, type the correct server / instance name into the Server name box of the Connection Properties dialog. (Mine is MOTHER1\MSSQL2K5, as depicted in Illustration 8.)


Illustration 8: Example Connection Properties Dialog with Corrected Settings ...

12.  Ensure that authentication settings are correct for the local environment.

13.  Click the Test Connection button.

A message box appears, indicating that the Test connection succeeded, assuming that our changes (or lack of same, as appropriate) are appropriate. The message box appears as shown in Illustration 9.


Illustration 9: Testing Positive for Connectivity ...

14.  Click OK to dismiss the message box.

15.  Click OK to accept changes, as appropriate, and to dismiss the Connection Properties dialog.

The Shared Data Source dialog appears, with our modified settings, similar to that depicted in Illustration 10.


Illustration 10: Example Shared Data Source Dialog with Modified Settings ...

16.  Click OK to close the Shared Data Source dialog, and to return to the development environment.

We are now ready to "clone" a sample report and proceed with the practice exercise.

Create a Copy of the Sales Reason Comparisons Report

As we have noted, we will begin with a copy of the Reporting Services 2005 Sales Reason Comparisons OLAP report, which we will use for our practice exercise in meeting the business requirements of the Adventure Works developers / authors.

1.  Right-click the Reports folder underneath the Shared Data Sources folder, in the Solution Explorer.

2.  Select Add --> Existing Item ... from the cascading context menus that appear, as shown in Illustration 11.


Illustration 11: Adding the Report to the Project ...

The Add Existing Item – AdventureWorks Sample Reports dialog appears.

3.  Navigate to the actual location of the sample reports (we provided the default path earlier), if the dialog has not defaulted thereto already.

An example of the Add Existing Item – AdventureWorks Sample Reports dialog, having been pointed to the sample Reports folder (which contains the Sales Reason Comparisons report file we seek), appears as partially shown in Illustration 12.


Illustration 12: Navigating to the Sample Reports Folder ...

4.  Right-click the Sales Reason Comparisons report inside the dialog.

5.  Select Copy from the context menu that appears, as depicted in Illustration 13.


Illustration 13: Performing a Quick Copy of the Sales Reason Comparisons Report

6.  Right-click somewhere in the white space inside the Add Existing Item – AdventureWorks Sample Reports dialog.

7.  Select Paste from the context menu that appears, as shown in Illustration 14.


Illustration 14: Select Paste within the New Folder ...

A copy of the Sales Reason Comparisons report appears within the dialog.

8.  Right-click the new file.

9.  Select Rename from the context menu that appears.

10.  Type the following name in place of the highlighted existing name:

RS031_Linked Report.rdl

NOTE: Be sure to include the .rdl extension in the file name.

The renamed copy of the Sales Reason Comparisons sample report appears as depicted in Illustration 15.


Illustration 15: The New Report File, RS031_Linked Report.rdl

11.  Click the white space to the right of the file name, to accept the new name we have assigned.

12.  Re-select the new file by clicking it.

13.  Click Add on the dialog box to add the new report to report project AdventureWorks Sample Reports.

RS031_Linked Report.rdl appears in the Reports folder, within the AdventureWorks Sample Reports project tree in the Solution Explorer, as shown in Illustration 16.


Illustration 16: The New Report Appears in Solution Explorer – Report Folder

14.  Right-click the new RS031_Linked Report report within the Solution Explorer.

15.  Select Open from the context menu that appears, as depicted in Illustration 17.


Illustration 17: Opening the New Report ...

RS031_Linked Report.rdl opens in Layout view, and appears as shown in Illustration 18.


Illustration 18: Our Report Opens in Layout View ...

Let's preview the report, so as to get a feel for its general operation prior to performing our enhancements.

16.  Click the Preview tab to execute RS031_Linked Report.rdl.

Execution begins (the report initially executes with the default parameter setting). Once executed, the report appears as depicted in Illustration 19.


Illustration 19: The Report Appears with Default Parameter Selection

We now have a basic OLAP report file within our Reporting Services 2005 Project. We need to make an alteration to the report, the addition of a report parameter for Sales Territory Group, so as to afford ourselves the ability to customize the Linked Reports within Report Manager in a way that allows each Sales Territory Group's member consumers to see only the data relating to their own Sales Territory Group. We will accomplish this modification within the next section.

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:

OverwriteDataSources:

False (Default)

TargetDataSourceFolder:

Data Sources (Default)

TargetReportFolder:

AdventureWorks Sample Reports (Default)

TargetServerURL:

http://temple001/reportserver


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.

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.

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.

Customize a Report Parameter for Each Linked Report

Once we have created folders for each Sales Territory Group within SQL Server Management Studio, within which we also placed the Linked Reports, we have established a basis for at least rudimentary access control, to limit each Sales Territory Group to only the Linked Report that we wish for its members to see. Our objective is to allow each group to enter only its designated folder, containing a Linked Report whose definition will include a restriction that allows it to present only the data from the Sales Territory Group whose members will be allowed access the parent folder.

We will limit the data returned by each report via the filtering effects of the new Sales Territory Group parameter, by taking the following steps.

1.  Open an instance of Internet Explorer.

2.  Type in the URL of the Report Server once again, as required.

Report Manager appears, as we arrive on the Home page, where we see the new folders that we created within SQL Server Management Studio earlier.

3.  From the Home page of the Report Manager, re-enter the Europe folder we created earlier.

4.  Click the Show Details button in the upper right corner of the Contents tab, as shown in Illustration 42.


Illustration 42: Click Show Details ...

5.  Click the Properties icon, underneath the Edit column heading, to the left of the Sales Reason Comparison (Name column) Linked Report, as depicted in Illustration 43.


Illustration 43: Editing Linked Report Properties ...

The Properties page for the Sales Reason Comparison Linked Report opens.

6.  Click the Parameters link, on the left side of the page (where it resides with other links), as shown in Illustration 44.


Illustration 44: Click the Parameters Link on the Properties Page ...

The Properties – Parameters settings for the Sales Reason Comparison Linked Report appear.

7.  Within the row for the SalesTerritorySalesTerritoryGroup parameter, using the drop-down selector in the Default Value column, select [Sales Territory].[Sales Territory Group].&[Europe], as depicted in Illustration 45.


Illustration 45: Selecting a "Folder-Specific" Default Parameter

NOTE: If the selector does not appear to work – that is, it does not repopulate the Default Value box, then de-select the Has Default checkbox, click Apply, and then leave the folder by clicking the View tab, as if to execute the report. Return to the Parameters properties page (click the Properties tab and then the Parameters link, from the View tab), and re-select Has Default, which should be blank at this point. As if attempting to select a value in the Default Value box drop-down, click the downward arrow button. When the empty selector opens, type in the MDX qualified name ( [Sales Territory].[Sales Territory Group].&[Europe] , in this case), and it should repopulate the box when navigating out of the selector in the following step.

8.  Click the Hide checkbox to select it.

The checkmark appears in the Hide checkbox, and the Prompt User checkbox becomes automatically de-selected. Our new settings for the SalesTerritorySalesTerritoryGroup parameter appear as shown in Illustration 46.


Illustration 46: Our New Parameter Settings ...

The effect here is to force a default Sales Territory Group of Europe, with no consumer option of changing this filter, for the Europe Sales Reason Comparison report.

9.  Click the Apply button to accept and save the modifications to the parameter.

We will perform this parameter modification for the other two Linked Reports next, within the context of their respective Sales Territory Groups.

10.  Return to the Home page via the breadcrumb trail in the upper left corner of Report Manager, once again.

11.  From the Home page of the Report Manager, enter the North America folder we created earlier.

12.  Click the Properties icon, underneath the Edit column heading, to the left of the Sales Reason Comparison (Name column) Linked Report, as we did within the Europe folder earlier.

The Properties page for the Sales Reason Comparison Linked Report opens.

13.  Click the Parameters link on the Sales Reason Comparison Linked Report Properties page that appears next, on the left side of the page (where it resides with other links), as we did within the Europe folder before.

The Properties – Parameters settings for the North America Sales Reason Comparison Linked Report appear.

14.  Within the row for the SalesTerritorySalesTerritoryGroup parameter, using the drop-down selector in the Default Value column, select [Sales Territory].[Sales Territory Group].&[North America] (or use the procedure we discussed in my NOTE above, if issues arise).

15.  Click the Hide checkbox to select it, as we did earlier.

The checkmark appears in the Hide checkbox, and the Prompt User checkbox becomes automatically de-selected, as before. With this Linked Report, we are forcing a default Sales Territory Group of North America, with no consumer option of changing this filter, for the North America Sales Reason Comparison report.

16.  Click the Apply button to accept and save the modifications to the parameter properties.

We have only remaining Pacific Sales Reason Comparison report to modify, at this point.

17.  Return to the Home page via the breadcrumb trail in the upper left corner of Report Manager, as before.

18.  From the Home page of the Report Manager, enter the Pacific folder we created earlier.

19.  Click the Properties icon, underneath the Edit column heading, to the left of the Sales Reason Comparison (Name column) Linked Report, as we did within the Europe and North America folders earlier.

The Properties page for the Sales Reason Comparison Linked Report opens.

20.  Click the Parameters link on the Sales Reason Comparison Linked Report Properties page that appears next, on the left side of the page (where it resides with other links), as we did within the Europe and North America folders before.

The Properties – Parameters settings for the Pacific Sales Reason Comparison Linked Report appear.

21.  Within the row for the SalesTerritorySalesTerritoryGroup parameter, using the drop-down selector in the Default Value column, select [Sales Territory].[Sales Territory Group].&[Pacific].

22.  Click the Hide checkbox to select it, as we did earlier.

The checkmark appears in the Hide checkbox, and the Prompt User checkbox becomes automatically de-selected, as we saw for the two Linked Reports earlier. With this Linked Report, we are forcing a default Sales Territory Group of Pacific, again leaving no consumer option of changing this filter, for the Pacific Sales Reason Comparison report.

23.  Click the Apply button to accept and save the modifications to the parameter properties.

24.  Return to the Home page via the breadcrumb trail in the upper left corner of Report Manager, once again.

We have completed setup of the Linked Reports, establishing folders to which we sill limit access based upon the criteria of Sales Territory Group membership. In addition, we have restricted the data that each Linked Report retrieves and displays, through the addition of a hidden, default parameter filter to each, based, again, upon the intended Sales Territory Group audience. We will verify operation for expected results in the section that follows.

Verification: Test the Linked Reports for Expected Operation

As we have mentioned multiple times, we can restrict access to the folders with the most basic security available in Report Manager. This will then mean that, given access to only a single Sales Territory Group folder, members of a given Sales Territory Group can only see data that relates to their own respective group, thanks to the default parameter that we have "hard coded" into the associated Linked Report.

Far more elaborate strategies can, of course, be envisioned and enacted within an application as flexible as Reporting Services. This is even more the case within a scenario where we employ the integrated Microsoft BI solution as a whole, and can deploy security and myriad other features at various layers within the suite. I have recently deployed systems where reports are handled for large numbers of individual salespersons, as an example, using a similar approach, with Linked Reports at its heart, together with data-driven subscriptions, e-mail delivery, notification of various types, and other innovations that are supported within Microsoft enterprise BI. The possibilities are virtually limitless.

NOTE: For a hands-on introduction to data-driven subscriptions, see Managing Reporting Services: Data-driven Subscriptions, and External Data Sources for Subscriber Data, a member of my MSSQL Server Reporting Services series at Database Journal.

Let's do a quick inspection of the operation of our Linked Reports as we have deployed them.

1.  From the Home page of the Report Manager, re-enter the Europe folder we created earlier.

2.  Within the Europe folder, click the Sales Reason Comparison Report, as depicted in Illustration 47.


Illustration 47: Click the Report to Execute from Inside the Folder ...

The Sales Reason Comparison Report for the European Territory Sales Group executes, and returns data as shown in Illustration 48.


Illustration 48: The Sales Reason Comparison Report – Filtered for Europe

As is apparent (and expected), the report is restricted, through the use of the defaulted, hidden parameter that filters it to show only the data relevant to the European Territory Sales Group consumers. We have achieved the desired results and met the business requirements through the use of Linked Reports, which we have created and placed from within the SQL Server Management Studio. Each Sales Territory Group's report (displaying the respective Group's data only) is contained within a folder to which access is restricted, in turn, to the member consumers of the respective Sales Territory Group.

3.  Test the other Linked Reports as desired.

4.  Exit Report Manager when ready.

Conclusion ...

In this article, we introduced Linked Reports, a powerful administrative feature within MSSQL Server 2005 Reporting Services. We discussed the purpose and uses of Linked Reports, describing examples where they might provide versatile, yet conveniently maintained, "versions" of a core report to meet the requirements of multiple users or groups, allowing each to see only the data that management has deemed relevant to their functions. We discussed the general concepts, presented a basic business need for a hypothetical client, and then set about using Linked Reports to answer the requirements in a straightforward manner.

We accessed the sample Report Server project, AdventureWorks Sample Reports, and ascertained connectivity of the relevant shared data source. Next, we created a clone of an existing sample OLAP report, with which to perform our practice exercise, as a means of saving time. After making a small change to the clone report (adding a parameter for the Sales Territory Groups), we previewed the clone report to ascertain its readiness for deployment, before "publishing" it to a common folder within Report Manager.

Next, we created folders in Report Manager, to house Linked Reports for different consumer groups, and then created Linked Reports within each of the new consumer group folders, based upon the common source .rdl file. We performed these steps from within SQL Server Management Studio (in contrast to performing them from the Report Manager, the details of which we outlined in BlackBelt Administration: Linked Reports in Report Manager.)

We then set a default parameter (to be hidden to the consumer at runtime) within each Linked Report to restrict the data returned and presented to the intended consumer group audience. Finally, we previewed a Linked Report to ascertain the effectiveness of our solution. Throughout the steps of our practice session, we discussed, at appropriate junctures, various settings and techniques involved in achieving our objectives.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Reporting Services Forum.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved