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.
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.
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
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.
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
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.
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
the Sample Report Server Project and Ascertain Connectivity of the Shared Data
begin, we will launch the SQL Server Business Intelligence Development Studio.
and click, the SQL
Server Business Intelligence Development Studio, as appropriate.
equivalent on my PC appears as depicted in Illustration 2.
Launching SQL Server Business Intelligence Development Studio
briefly see a splash page that lists the components installed on the PC, and
then Visual Studio opens at the Start page.
Close the Start
page, if desired.
-> Open from the main menu.
/ Solution ... from the cascading menu, as shown in Illustration 3.
Selecting a Project ...
Project dialog appears.
Browse to the AdventureWorks
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
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 ...
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 ...
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
within the Shared Data Sources folder seen in Solution Explorer.
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
Properties dialog opens, and appears with default settings shown in Illustration
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
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
15. Click OK to accept changes,
as appropriate, and to dismiss the Connection Properties dialog.
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
We are now ready to "clone" a sample report and
proceed with the practice exercise.