Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

How to know what Reporting Services reports are being used


By:   |   Updated: 2007-08-10   |   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


Last Updated: 2007-08-10


get scripts

next tip button



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.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

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

Can you update for 2012?


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

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?


Learn more about SQL Server tools