Reporting Services Content Management

July 27, 2009

In the previous installment of our series, we presented basic deployment and
content management features of Reporting Services software implemented as part
of SQL Server 2005 Express Edition with Advanced Services. As we have
demonstrated, there are several possible methods for uploading reports to the
Report Server database (which effectively publishes them on the Report Manager
Web site). Once published, they subsequently can be arranged in an arbitrary
manner within the site's hierarchy. Besides the more intuitive methods of
facilitating such arrangements that we already have covered, there are some
additional methods, which warrant extra explanation. However, before we proceed
with their description, it is worthwhile to review a correlation between
different components that comprise Reporting Services hosted on the SQL Server
2005 Express Edition platform.

Report Manager (implemented as an ASP.NET-based application and exposed as a
Web Service) constitutes the primary mechanism for content (dealing with
hierarchy and storage of reports) and system (resource configuration, security,
and operational model) management of Reporting Services. It also serves as the
primary Web interface, allowing end-user to access and execute reports. Its
administrative capabilities are supplemented by Reporting Service Configuration
Manager (as discussed in one
of our earlier articles, its primary responsibility is to facilitate such
tasks as creating and modifying virtual directories utilized by both Report Manager
and Report Server, managing their respective Web and Windows services, or
setting up the report database and maintaining its encryption keys) and a set
of command-line utilities, such as rsconfig
(customizing connection information used by Report Server component to connect
to its database), rs
(intended for executing VB.NET scripts used primarily to publish reports), and rskeymgmt (managing symmetric encryption
keys and encrypted data residing in the Reporting Services database). Report
Server, operating also as a Web Service, handles the core reporting
functionality, including access control, report processing and rendering, as
well as interaction between the management utilities (including Report Manager,
Reporting Services Configuration Manager, command-line utilities or any
third-party programs) and the database layer (where data referenced by reports,
report definitions, and Report Server metadata reside). Keep in mind that it is
not possible to administer reporting functionality in SQL Server 2005 Express
Edition via its Management Studio (unlike its full-fledged counterparts).

Following this high-level overview of Reporting Services architecture, let's
turn our attention to some of its specifics. In addition to the standard
content components we have presented so far, such as folders, reports, and data
sources, you also have the ability to create linked reports, (identifiable by a
distinct icon in the Report Manager interface), which facilitate customizing
some aspects of existing reports without the need for modifying their
definitions (i.e. the underlying .rdl
files). In particular, it is possible to modify basic Report Manager-based
properties (name, description, or location), parameters (if applicable), data
sources, or security (full-featured SQL Server 2005 editions also allow changes
to execution and subscription characteristics). Note that despite these
modifications, linked reports retain a relationship to their source, so they
are impacted by any changes to their original definition files (on the other
hand, modifying or even deleting a linked report does not affect its base
report in any way). To generate a link to an existing report, use the Create Linked Report command button
available on its Properties
tab (you can also point an existing linked report to a different source with the
Change Link command button).

Linked reports are frequently used to facilitate rendering of reports
according to the intended target audience. If a base report has been designed
to accept parameters, you have an option to assign desired values via the Parameters section on the Properties tab of its linked report.
Within that section, it is also possible to specify their defaults (using Has Default and Default Value textboxes). Furthermore,
you are also able to either prevent parameters from being displayed (via Hide checkbox), or prompt users for
their values (via Prompt User
checkbox). As your Report Manager site grows (to which linked reports tend to
contribute), you might want to keep in mind the search feature, exposed via the
Search for: textbox located
in the upper right corner of SQL Server Reporting Services pages (allowing you
to locate an arbitrary item based on its name or description - provided that
you have at least read permissions to it).

My Reports is another
content management feature that is worth mentioning. Its primary purpose is to
simplify management of reports, by organizing them according to their
ownership, such that individual users have their own, personalized view of
reports along with exclusive access to them. To enable it, click on the Site Settings link in the upper right
corner of the SQL Server Reporting Services Web site. Within the Settings section, mark the Enable My
Reports to support user-owned folders for publishing and running personalized
reports checkbox. Also ensure that the My Reports entry appears in
the listbox labeled Choose the role to apply to each user's My Reports
folder: in order to allow all users that are able to access the Report
Manager site to independently administer their own My Reports folder within the site
hierarchy (we will be discussing Reporting Services roles in the next article
of our series). This will result in the creation of a user-specific folder
called My Reports, located directly on the SQL Server Reporting Services
Home page, with permissions granting that user exclusive access to its content.

In addition to the configuration options intended for setting up the My Reports feature, the Site Settings page contains a number of
other entries. Unfortunately, in the case of SQL Server 2005 Express Edition,
only some of them are actually functional (even though they not only appear to
be available but also can be modified without generating an error message
indicating failure). In particular, you will find there the following:

default settings for report history, which either Keep an
unlimited number of snapshots in report history or Limit the copies of report
history: to an abitrary
value. Since SQL Server 2005 Express Edition does not support report history,
they have no impact on the behavior of Reporting Services.

Report Execution Timeout with two options that either Do
not timeout report execution or Limit report execution to the following
number of seconds. While neither one of them seems to have any relevance,
the resulting behavior still remains to be a subject to restrictions imposed by
ASP.NET Web Services configuration.

settings to Enable report execution logging and Remove
log entries older than this number of days (set by default to 60) constitute an exception in this
list, allowing you to capture report execution events in the dbo.ExecutionLog table of the ReportServer database. While Microsoft
provides Execution Log Sample Reports (as part of Server Management
Sample Reports covered in one of the earlier article of this series), which
automate creation of a separate database where you can transfer execution logs
for rendering through pre-defined reports, their dependency on the SQL Server
Integration Services package makes them unsuitable for the SQL Server 2005
Express Edition environment. (For more information regarding this subject,
refer to the Querying
and Reporting on Report Execution Log Data article in the SQL Server 2005
Books Online).

It is important to realize that, in addition to tracking report execution in
the ReportServer database, Reporting Services also tends to generate a large
volume of log files (especially when errors are encountered). To avoid disk
space issues, you should consider adjusting the level of logging according to
your requirements by modifying the value assigned to the DefaultTraceSwitch parameter
in the following configuration files used by Reporting Services components. (With
0 disabling tracing
completely, 1 recording
exceptions and restarts, 2
keeping track of exceptions, restarts, and warnings, 3 including additional status messages,
and 4 designating verbose
mode, which should be turned on only temporarily during troubleshooting):