Microsoft does not document or support querying the ReportServer database, but it contans a lot of useful information about SSRS reports. A previous article, Dynamic SSRS report documentation via a ReportServer.dbo.Catalog query, demonstrated how to query the ReportServer.dbo.Catalog to generate live documentation of SSRS reports.

In this article we will query the ReportServer.dbo.ExecutionLog table to determine which reports have been executed in the past month and which have not.

By default, SQL Server only stores 30 days of data in the ReportServer.dbo.ExecutionLog table. For more information on how to configure execution logging see How to Audit Report Execution in SSRS by Peter Avila.

Reports Executed During Past 30 Days

Paste the following query of the ReportServer.dbo.ExecutionLog into a SQL Server Managment Studio query panel...

BEGIN TRY
DROP TABLE #temp01
END TRY
BEGIN CATCH
END CATCH
SELECT DISTINCT
SUBSTRING(t2.Path,1,LEN(t2.Path)-LEN(t2.Name)) AS Folder
,t2.Name
,REPLACE(t1.UserName,'JACKPOT\','') AS UserName2
,MAX(t1.TimeStart) AS LastExecuted
INTO #temp01
FROM ReportServer.dbo.ExecutionLog t1
JOIN ReportServer.dbo.Catalog t2 ON t1.ReportID = t2.ItemID
GROUP BY t2.Path,t2.Name,t1.UserName
ORDER BY Folder,UserName2 --,t1.UserName
SELECT DISTINCT
Folder AS ReportFolder
,Name AS ReportName
,COUNT(Name) AS TimesExecuted
,MAX(LastExecuted) AS LastExcecutedDate
,ExecutedBy = STUFF((
   SELECT ', ' + UserName2 FROM #temp01
WHERE Name = x.[Name]
FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, '')
FROM #temp01 x
WHERE Name <> ''
GROUP BY Folder,Name
ORDER BY TimesExecuted DESC,ReportFolder,Name

...and execute it. The query returns all the reports that have been executed during the past 30 days, how many times they were executed, and who ran them.

Download the attached Reports Executed During Past 30 Days.rdl file, open it in development studio, add credentials for a user with permission to read the ReportServer database and run it.

Reports Not Executed During Past 30 Days

Paste the following query of the ReportServer.dbo.ExecutionLog into a SQL Server Managment Studio query panel...

BEGIN TRY
  DROP TABLE #temp01
END TRY
BEGIN CATCH 
END CATCH
BEGIN TRY
DROP TABLE #temp02
END TRY
BEGIN CATCH
END CATCH
GO
WITH RankedReports
AS
(SELECT ReportID,
        TimeStart,
        UserName, 
        RANK() OVER (PARTITION BY ReportID ORDER BY TimeStart DESC) AS iRank
   FROM ReportServer.dbo.ExecutionLog t1
        JOIN 
        ReportServer.dbo.Catalog t2
          ON t1.ReportID = t2.ItemID AND t2.Type <> 1
)
SELECT 
DISTINCT     t1.UserName,t2.Name AS ReportName
,SUBSTRING(t2.Path,2,LEN(t2.Path)-LEN(t2.Name)-1) AS Folder,t2.Type
INTO #temp01
FROM RankedReports t1
INNER JOIN ReportServer.dbo.Catalog t2 ON t1.ReportID = t2.ItemID
WHERE t1.iRank = 1
AND t2.Type <> 1
ORDER BY t1.UserName,t2.Name;
SELECT 
  SUBSTRING(Path,2,LEN(Path)-LEN(Name)-1) AS ReportFolder
  ,Name AS ReportName
  ,CreationDate
  ,ModifiedDate
  ,Type
  INTO #temp02
FROM ReportServer.dbo.Catalog 
WHERE Name NOT IN (SELECT ReportName FROM #temp01) 
AND Path <> '' 
ORDER BY Path
SELECT * FROM #temp02 WHERE ReportFolder <> '' AND Type = 2 ORDER BY ModifiedDate DESC

...and execute it. The query returns all the reports that have not been executed during the past 30 days.

Download the attached Reports Not Executed During Past 30 Days.rdl file, open it in development studio, add credentials for a user with permission to read the ReportServer database and run it.

Conclusion

This article has presented two reports that can be used to detect SSRS reports that are being used extensively and SSRS reports that are no longer being used at all.