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
Let's do a quick
inspection of the operation of our Linked Reports as we have deployed
From the Home
page of the Report Manager, re-enter the Europe folder we created earlier.
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 ...
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
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.
Test the other
Manager when ready.
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
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
See All Articles by Columnist William E. Pearson, III
Discuss this article in the MSSQL Server 2000 Reporting Services Forum.