Do you know which reports are being used?
Updated by Brady Stroud [SSW] 1 year ago. See history
123
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 RankedReportsAS(SELECT ReportID,TimeStart,UserName,RANK() OVER (PARTITION BY ReportID ORDER BY TimeStart DESC) AS iRankFROM dbo.ExecutionLog t1JOINdbo.Catalog t2ON 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.ReportIDFROM RankedReports t1JOINdbo.Catalog t2ON t1.ReportID = t2.ItemIDWHERE t1.iRank = 1GROUP BY t2.Name, Path, ReportIDORDER 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))