Pages Menu

Posted by on Jan 6, 2011 in Development, Everything Else | 0 comments

SQL Server Reporting Services: recently executed/modified reports

Some quick SQL statements to easily find out what your most recently accessed reports, and your recently modified reports are, on your SQL Server Reporting Services instance.These can be useful if you need to tidy up your Report Server, and want to know which reports haven’t been used for a long time. Alternatively, if you’re experience speed issues, this might be a good place to start to see if the speed issues coincide with execution of a particular report.

Lots of useful information is stored in the Report Server tables – a list of all executions (report views) together with parameters, users, timing information, row counts etc. Just don’t touch anything – looking only! Check out the views as well.

Reports ordered by last executed:

SELECT A.ItemPath,A.TimeStart,A.UserName
FROM ExecutionLog3 A
(Select ItemPath, MAX(TimeStart) as [LastExecuted]
FROM ExecutionLog3 GROUP BY ItemPath) as B
ON A.ItemPath = B.ItemPath AND A.TimeStart = B.LastExecuted
ORDER BY TimeStart desc

Reports ordered by last modified (with modifying user):

SELECT Name, Path, Description, ModifiedDate, Users.UserName AS [ModifiedBy]
FROM [Catalog]
INNER JOIN Users ON Catalog.ModifiedByID = Users.UserID
WHERE type = 2
ORDER BY ModifiedDate DESC

Type=2 should include just reports. If you leave it out you can see Data Sources, folders, everything.

You should point these statements at your Report Server database for best effect. I am indebted to this Stack Overflow post for reminding me how to include the username on the recently executed report.

Written by Tom Morgan

Tom is a Microsoft Teams Platform developer and Microsoft MVP who has been blogging for over a decade. Find out more.
Tom is currently writing a book: Building and Developing Apps & Bots for Microsoft Teams. Register your interest today and be the first to know when it's ready to read.

Post a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.