Do you know which reports are being used?

Last updated by Brady Stroud [SSW] about 2 months ago.See history

SSRS keeps track of each report that gets executed and records useful information like:

  • How long did the report take to generate
  • Who requested the report
  • When was the report generated
  • Report Parameters used

So it's quite simply a matter of querying the ReportServer database for information in the ExecutionLog table.

WITH RankedReports
        RANK() OVER (PARTITION BY ReportID ORDER BY TimeStart DESC) AS iRank
   FROM dbo.ExecutionLog t1
        dbo.Catalog t2
          ON t1.ReportID = t2.ItemID
SELECT t2.Name AS ReportName,
       MAX(t1.TimeStart) LastAccessed,
       SUBSTRING(t2.Path, 0, CHARINDEX('/', t2.Path, 2)) ParentFolder,
  FROM RankedReports t1
       dbo.Catalog t2
         ON t1.ReportID = t2.ItemID
 WHERE t1.iRank = 1
GROUP BY t2.Name, Path, ReportID
ORDER BY MAX(t1.TimeStart) DESC;

The query above gives you the last reports that were accessed (Credit to Eric Phan - SSRS - Find out which reports are being used (handy for migrating only the useful reports to a new server))

Eric Phan
We open source. Powered by GitHub