By: Rob Fisch | Last Updated: 2007-08-10 | Comments (3) | Reporting Services Monitoring
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?
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.
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.
- 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
Last Updated: 2007-08-10
About the author
View all my tips