Home
Do you know which reports are being used?
  v1.0 Posted at 12/09/2016 11:06 AM by Eric Phan
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
AS
(SELECT ReportID,
        TimeStart,
        UserName, 
        RANK() OVER (PARTITION BY ReportID ORDER BY TimeStart DESC) AS iRank
   FROM dbo.ExecutionLog t1
        JOIN 
        dbo.Catalog t2
          ON t1.ReportID = t2.ItemID
)
SELECT t2.Name AS ReportName,
       MAX(t1.TimeStart) LastAccessed,
       --t1.UserName,
       t2.Path,   
       SUBSTRING(t2.Path, 0, CHARINDEX('/', t2.Path, 2)) ParentFolder,
       t1.ReportID
  FROM RankedReports t1
       JOIN 
       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)​)

Related rules

    Do you feel this rule needs an update?

    If you want to be notified when this rule is updated, please enter your email address:

    Comments:

    Note: Social Media login for Yotpo is not working in IE or Safari, please use Chrome. We are waiting for Yotpo to fix it.