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

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.

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.

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.

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

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

The Open Project dialog appears.

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

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

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.

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

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

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

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.

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

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