Download the Code iconApplications tend to generate a lot of data—data that end users, developers, and DBAs need to turn into information to answer questions. SQL Server Reporting Services (SSRS) helps address this need. However, SSRS is itself an application that generates a lot of data. In this article, you’ll see how to leverage sample reports and techniques to answer common questions related to SSRS reports. Note that all of the sample reports and techniques discussed in this article work with both SSRS 2008 and SSRS 2005. You can download the samples for this article by clicking the Download the Code link at the top of the article page.

Which Reports Are Being Run, How Long Are They Taking, and Who Is Viewing Them?

SSRS logs every report request to an internal table in the Report Server database. This logging behavior is documented in the "Report Server Execution Log" section of SQL Server Books Online (BOL). Direct querying of this table, however, isn’t recommended, and the data in this table is removed after 60 days by default. Instead, Microsoft provides, as part of a larger set of SSRS samples, a SQL Server Integration Services (SSIS) package (RSReportExecutionLog_Update.dtsx) that extracts the log data into a separate database, as well as several reports for viewing the log data. You can download the SSRS 2008 and 2005 samples from www.codeplex.com/MSFTRSProdSamples. Once you’ve installed the samples, navigate to the /Samples\Reporting Services\Report Samples\Server Management Sample Reports folder. I’m not going to spend time reviewing the SSIS package responsible for the data extraction, but you can see a diagram of the resulting schema in Figure 1.

Figure 1: Execution log schema

Let’s take a look at the sample reports.

Figure 2 shows a portion of the Execution Summary report.

Figure 2: Report Execution Summary

(For space purposes, I removed the graphs that show report executions by day of the month and day of the week, as well as a Top 10 Largest Reports table.) The Execution Summary lets you see, for a given date range, general statistics (e.g., total number of executions), a list of the 10 most popular reports, a list of the 10 longest running reports, and a list of the top 10 users for the SSRS instance. After you click a report name in any of the Top 10 tables, the Report Summary report, shown in Figure 3, is displayed.

Figure 3: Report Summary Report

This report provides, for a given date range, detailed report execution information, including the number of successful and failed executions, report status codes, and a list of parameters used.

The last sample report, Execution Status Codes, summarizes successful and failed report executions, and offers a detailed list of all report success/failure counts. Looking back at Figure 1, you might consider creating additional reports, such as a User Detail report, to display a list of the reports a particular user has executed for a given date range.

How Many Report Subscriptions Have Been Created and Did Any of Them Fail?

It’s often useful to know the overall count, status, and the cause of errors (if applicable) with respect to report subscriptions. I recently met with a business analyst who was given the responsibility of monitoring, troubleshooting, and restarting report subscriptions. When she asked me how to perform this task in SSRS, I realized a custom report would be needed. (SSRS provides out-of-the-box functionality within Report Manager to view status information for a single subscription, but it doesn’t provide an aggregated view across multiple subscriptions.)

As with report execution, SSRS stores subscription information in an internal table; additional tables are used for the applicable subscription schedule(s). Microsoft doesn’t officially support direct querying of these tables, because they can change from version to version, but you can query for subscription information via the Report Server Web Service. (Note that SSRS supports Web Services as a data source.)

If you haven’t already done so, download the samples for this article. Then open up the SubscriptionReports.rptproj project in Business Intelligence Development Studio (BIDS). (I’d like to say a special thanks to Lukasz Pawlowski, a program manager on Microsoft’s SSRS team, for providing these reports.)

There are two main reports in this project: SubscriptionsList (shown in Figure 4, with some information removed for space purposes) provides a summary of subscription executions and errors and a detailed list of subscriptions for a given month or year.

Figure 4: SubscriptionsList report

(This report will return an error if there isn’t a least one subscription defined on the server.) I added a table at the bottom of the report that displays a list of any subscriptions that have yet to be executed. The other report, SubscriptionsByExtension, displays a list of all subscriptions broken out by extension type (standard or data driven). SubscriptionsByExtension relies on two subreports, StandardSubscriptionsExtension and DataDrivenSubscriptionExtension, which are also included in the project.

Even if monitoring subscriptions isn’t required in your organization, I encourage you to review these reports because they contain some interesting features, including dynamic data connections, the use of XML Web Services as a data source, show/hide/visibility control, inline code, and subreports used as text boxes within a table.

Where Did This Report Come From?

A report can sometimes lead to further questions regarding the validity of the report itself. Consider a scenario in which an end user is viewing a report that someone else executed and exported to Microsoft Excel. Before the user will trust the information in the report, he or she might want to know where the report came from, when it was run, who ran it, what filters were applied, and so on. Many of these questions can be addressed simply by taking advantage of the built-in fields available in the Report Designer.

If you look at the page footer in Figure 4, you can see I added several fields, including the report name, path, and server, as well as the date and user responsible for executing the report. You should consider including this information across all the reports in your environment. In the body of the report, you might also want to provide a list of any parameter values. In the sample code, I’ve provided a blank report template that you can reuse in your environment. (Note: I’m using the term "template" to simply describe a report that other reports can be based upon.) An interesting way to make use of templates in BIDS is to place the report’s .rdl file in the \Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject subfolder, so the report appears as an additional option when adding a new report.

Use SSRS to Address Questions About Reports

As a distributed application, SSRS will likely have multiple "administrators" including DBAs, web administrators, and the developers and power users responsible for report generation and maintenance. These administrators will have questions related to usage, performance, and troubleshooting. By extracting information from SSRS, and in some cases querying SSRS directly, a wealth of information can be provided via custom reports. Additionally, end users might occasionally question the validity of reports. SSRS’s built-in fields, when placed directly within a report, can help address many of these questions.