How to know what Reporting Services reports are being used

By:   |   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.

  1. 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.
  2. 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.
  3. 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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Rob Fisch 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



Comments For This Article




Tuesday, September 16, 2014 - 6:14:02 AM - Chandra Back To Top (34541)

Looks like it does not work fo rsub report. I found the Sub reports execution is not being tracked in the ExecutionLog table.

Could you plesae let us know if the sub reports execution is tracked by report server.


Monday, March 25, 2013 - 1:04:13 PM - Juniper Back To Top (23004)

Can you update for 2012?


Friday, July 24, 2009 - 1:35:08 PM - smcneal Back To Top (3776)

Hello,

 This was helpful. How can tell how many times a user runs a specific report as well? Especially, over a year period of time?















get free sql tips
agree to terms