Saturday, September 2, 2017

How to show list of unused SSRS reports for given number of days

USE ReportServer
GO
DECLARE @NotUsedDays INT

SELECT @NotUsedDays = 30

SELECT Name,Path,LastUsedDate,NotUsedsince=DATEDIFF(DD,LastUsedDate,GETDATE())
  FROM dbo.catalog C
  JOIN (
 SELECT ReportID,LastUsedDate= MAX(timestart)
FROM dbo.executionlog
  GROUP BY ReportID
  ) E
ON C.ItemID = E.ReportID
 WHERE DATEDIFF(DD,LastUsedDate,GETDATE()) >= @NotUsedDays
 ORDER BY NotUsedsince ASC