By: Rob Fisch | Comments (3) | Related: > Reporting Services Monitoring
Problem
SQL Server Reporting Services is a great tool to give your users access to commonly run reports. There is still the effort of developing the reports, but with parameter driven reports you can give your end users a lot of power very quickly without much development effort. The one problem with reports though is that someone always wants something new and therefore more and more reports get created and deployed. Eventually there are many reports on the server and no one is quite sure what is being used and what is not being used. So based on this is there any easy way to figure out what reports are being utilized?
Solution
Luckily with SQL Server 2005 Reporting Services this is automatically tracked every time a report is run. Each time a report is run, Reporting Services tracks which reports are being used, when, how often and with what parameters.
The data for all report runs is stored in the ExecutionLog table in the ReportServer database. With the data in this table along with the data that is stored in the Catalog table we can easily answer these questions.
The following statement can be used to gather execution stats from these two tables.
| SELECT ex.UserName, ex.Format, ex.TimeStart, cat.Name, ex.Parameters, CONVERT(nvarchar(10), ex.TimeStart, 101) AS rundate FROM ExecutionLog AS ex INNER JOIN Catalog AS cat ON ex.ReportID = cat.ItemID ORDER BY ex.TimeStart DESC |
With the above query, you can use this as the basis for various Reporting Services reports.
Try some of the following reports.
- Create a report grouped by rundate, then grouped by user, with a condition that limits data within the last X number of days. Try starting with the most recent 21 days and adjust as needed.
- Create a report grouped by Name (that's the name of the report), sort your groupings by a COUNT of each name in descending order.
- Create a report grouped by Username, then grouped by either (report) Name or rundate. (Why not try both as separate reports?)
With reports like these, you will soon learn all about report utilization on your report server. This is both helpful for keeping your report server clean by removing unused reports as well as getting you an idea of which reports are most utilized and whether these should be setup as scheduled reports vs. on demand reports.
With SQL Server 2000 Reporting Services this data is not as easily accessible, but the data is available. Take a look at this article that explains how to get report execution data for SQL Server 2000 Reporting Services.
Next Steps
- Now that you know where to find this data, build some reports to help you better manage your Reporting Services environment
- Take a look at these other Reporting Services tips
About the author
Rob Fisch has worked with SQL Server since version 6.5 as a dba, developer, report writer and data warehouse designer.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
