Reporting Services Content Management

Monday Jul 27th 2009 by Marcin Policht

Marcin Policht presents the remaining features of content management in Reporting Services as well as reviews its relationship with other Reporting Services components hosted on the SQL Server 2005 Express Edition platform.

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

  • ReportingServicesService.exe.config in the Microsoft SQL server\MSSQL.x\Reporting Services\Report Server\bin folder
  • Web.config in the Microsoft SQL server\MSSQL.x\Reporting Services\Report Server\ReportManager folder
  • Web.config in the Microsoft SQL server\MSSQL.x\Reporting Services\Report Server\ReportServer folder

where x designates an integer assigned the SQL Server 2005 Express Edition Reporting Services installation on the local computer. (For details regarding this subject, refer to the Reporting Services Trace Logs article in the SQL Server 2005 Books Online).

In the next article of our series, we will focus on access control management in SQL Server 2005 Express Edition-based implementation of Reporting Services

» See All Articles by Columnist Marcin Policht

Mobile Site | Full Site