Get more out of your reports using these techniques and sample reports
Applications 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.
Let’s take a look at the sample reports.
Figure 2 shows a portion of the Execution Summary report.
(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.
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.
(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.