BlackBelt Administration: Linked Reports in Report Manager

Monday Jun 19th 2006 by William Pearson
Share:

Use Linked Reports to provide multiple "versions" of a single source report, as a mechanism for restricting consumer groups to their respective data in accordance with a "need to know."

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 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 component 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 virtually 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

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. 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. Even in the simplest implementations, there is typically at least some need to exercise control over what information consumers can access. 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 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." Progress can thus 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). 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.

A common scenario I come across in implementing Reporting Services lies in using folder security to control report access, at least within the development cycles, and sometimes beyond. In many cases, the reports that reside in a given folder, say for the corporate finance department, are totally different from reports that are deployed within, for instance, the folder designated to inventory management. However, 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 events, such as report modifications, occur.

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. The concept of Linked Reports becomes quite attractive when we come across needs to make either identical or multiple versions of the same report available in various folders – a capability that has proven itself 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 this article, we will get some hands-on exposure to Linked Reports. We will discuss the general concepts, and then set up a scenario within which we deploy a sample report, and then perform the steps of setting up Linked Reports in multiple folders. 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 in Report Manager, to house Linked Reports for different consumer groups;

  • Create Linked Reports 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 to ascertain the effectiveness of our solution.

Linked Reports in Reporting Services 2005

Objective and Business Scenario

Linked Reports 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 deploy a simple OLAP report with which we can apply the principals of Linked Reports to custom-filter the report for different consumer groups. 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, 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 BI Development Studio, which makes its home within Visual Studio .NET 2005. We will then deploy the report file, and undertake our work with Linked Reports from Report Manager.

NOTE: For more exposure to the MSSQL 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 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 correctly 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:

RS030_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, RS030_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.

RS030_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 RS030_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 ...

RS030_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 RS030_Linked Report.rdl.

Execution begins (the report initially executes with the default parameter setting).

The report executes, and 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 RS030_Linked Report.rdl, once again.

9.  Select North America 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 our setup for Linked Reports will take place.

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 within Report Manager. We deploy the report to a common folder, taking the following steps, assuming that we are logged in as a user with the appropriate Report Manager rights within Reporting Services.

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 RS030_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) ...

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

Having deployed our sample report, we are ready to create Linked Reports at the Report Manager level. We will get some exposure to this process in the section that follows.

Procedure: Create Linked Reports

As we discussed in the introduction, 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. As we noted earlier, 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 one file, while updating all the "child" reports simultaneously.

Create New Folders in Report Manager to House the Linked Reports

We are ready to move to Report Manager to create and work with Linked Reports.

1.  Open an instance of Internet Explorer.

2.  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 earlier on the AdventureWorks Sample Reports Properties Pages dialog, 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

Let's create three folders in Report Manager to house our Linked Reports. This way, we can manage security by controlling access to the folders, limiting the groups of users to, in our example, Sales Territory Groups. (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).

3.  Click the New Folder button atop the Report Manager Home page, as shown in Illustration 30.


Illustration 30: Click New Folder ...

The New Folder page opens.

4.  Type the following into the Name field:

Europe

5.  Type the following into the Description field:

European Sales Territory Reports

The New Folder page appears, with our input, as depicted in Illustration 31.


Illustration 31: The New Folder Page, with Our Input

6.  Click OK to create the folder and return to the Home page.

The Europe folder appears, with a "!New" icon alongside, on the Home page.

7.  Click the New Folder button, once again.

8.  Type the following into the Name field of the New Folder page:

North America

9.  Type the following into the Description field:

North American Sales Territory Reports

10.  Click OK to create the folder and return to the Home page, as before.

The North America folder appears, with a "!New" icon alongside, on the Home page.

11.  Click the New Folder button, once more.

12.  Type the following into the Name field of the New Folder page:

Pacific

13.  Type the following into the Description field:

Pacific Sales Territory Reports

14.  Click OK to create the folder and return to the Home page, as before.

The Pacific folder appears, with a "!New" icon alongside, on the Home page. The Home page appears, with all new folders, as shown in Illustration 32.


Illustration 32: The New Folders on the Home Page ...

15.  Click the AdventureWorks Sample Reports folder to enter it.

The folder opens.

16.  Click the Show Details button, on the right of the toolbar, atop the AdventureWorks Sample Reports page, as depicted in Illustration 33.


Illustration 33: Click Show Details ...

17.  Click the Properties icon, underneath the Edit column heading, to the left of RS030_Linked Report (Name column), as shown in Illustration 34.


Illustration 34: Editing the Report Properties (Partial Page View)

The Properties page for RS030_Linked Report opens.

18.  Click the Create Linked Report button, toward the bottom of the page, as depicted in Illustration 35.


Illustration 35: Click Create Linked Report

19.  Type the following into the Name field of the next Properties page that opens next.

Sales Reason Comparison

20.  Type the following into the Description field:

European Territory Sales Reason Comparison

21.  Click the Change Location button, as shown in Illustration 36.


Illustration 36: Specifying the Location of the Linked Report ...

The Folder Location page opens.

22.  Click the Europe folder in the folder tree.

The Location box reflects our selection of Europe, as depicted in Illustration 37.


Illustration 37: Assigning the Linked Report to the Europe Folder

23.  Click OK to accept the location modification and return to the previous Properties page.

The new location appears, to the left of the Change Location button, as shown in Illustration 38.


Illustration 38: The New Location Appears ...

24.  Click OK once again, to finalize creation of the new Linked Report within the new location

The report generates and displays. (Note that, at this stage, it displays data for all three Sales Territory Groups, just as the source report did in earlier Previews.)

NOTE: Data Source and other error messages here may indicate inadequate permissions / security setup for the user within which we are working. For instruction in setting up security, see the Books Online, together with local policy documentation, etc., as appropriate).

25.  Once the report generates, return to the Home page using the breadcrumb trail atop the page, as depicted in Illustration 39.


Illustration 39: Returning to the Home Page ...

We arrive at the Report Manager Home page, once again.

Let's create the second Linked Report of our practice exercise, this time for the North American Sales Territory users, next.

26.  Click the AdventureWorks Sample Reports folder to re-enter it.

The folder opens.

27.  Click the Show Details button, once again, if necessary.

28.  Click the Properties icon, underneath the Edit column heading, to the left of RS030_Linked Report (Name column), as we did earlier.

The Properties page for RS030_Linked Report opens, once again.

29.  Click the Create Linked Report button, toward the bottom of the page, as we did before.

30.  Type the following into the Name field of the next Properties page that opens next.

Sales Reason Comparison

31.  Type the following into the Description field:

North American Territory Sales Reason Comparison

32.  Click the Change Location button, as we did earlier.

The Folder Location page opens, once again.

33.  Click the North America folder in the folder tree.

The Location box reflects our selection of North America.

34.  Click OK to accept the location modification and return to the previous Properties page.

35.  Click OK once again, to finish creating the new Linked Report within the new location, as we did for the first Linked Report.

36.  Once the report generates, return to the Home page using the breadcrumb trail atop the page, as we did after creating the first Linked Report.

Let's create the last Linked Report of our practice exercise, for the Pacific Sales Territory users, next.

37.  Click the AdventureWorks Sample Reports folder to re-enter it, once more.

The folder opens.

38.  Click the Show Details button, once again, if required.

39.  Click the Properties icon, underneath the Edit column heading, to the left of RS030_Linked Report (Name column), as before.

The Properties page for RS030_Linked Report opens, once again.

40.  Click the Create Linked Report button, as we did to begin creation of the previous two Linked Reports.

41.  Type the following into the Name field of the next Properties page that opens next.

Sales Reason Comparison

42.  Type the following into the Description field:

Pacific Territory Sales Reason Comparison

43.  Click the Change Location button, as we did earlier.

The Folder Location page opens, once again.

44.  Click the Pacific folder in the folder tree.

The Location box reflects our selection of the Pacific folder.

45.  Click OK to accept the location modification and return to the previous Properties page.

46.  Click OK once again, to finalize creation of the new Linked Report within the new location, as we did for the first and second Linked Reports.

47.  Once the report generates, return to the Home page using the breadcrumb trail atop the page, as we did after creating each of the first two Linked Reports.

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

Having created folders for each Sales Territory Group, 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.  From the Home page of the Report Manager, re-enter the Europe folder we created earlier.

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


Illustration 40: Editing Linked Report Properties ...

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

3.  Click the Parameters link, on the left side of the page (where it resides with other links), as depicted in Illustration 41.


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

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

4.  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 shown in Illustration 42.


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

NOTE: If you notice that 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.

5.  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 depicted in Illustration 43.


Illustration 43: 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.

6.  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.

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

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

9.  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.

10.  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 page for the North America Sales Reason Comparison Linked Report opens.

11.  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].

12.  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.

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

We have only to repeat similar steps for the remaining Pacific Sales Reason Comparison report.

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

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

16.  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.

17.  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 page for the Pacific Sales Reason Comparison Linked Report opens.

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

19.  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.

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

21.  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 will 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 shown in Illustration 44.


Illustration 44: 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 depicted in Illustration 45.


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

As is apparent, the report is restricted, as expected, 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, each of which will be contained within a folder that will be restricted 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 then set a default parameter (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