At SSW, we are currently migrating our SQL Server Reporting Services 2008 R2 reports to our new SQL Server Reporting Services 2016 server.

If you haven't decided to make the move to SSRS 2016 you should check out What's New in Reporting Services (SSRS). The main features we care about are:

  • Better Portal
  • Mobile Reports
  • PowerBI integration
  • Better HTML5 report rendering

As part of the migration process, we needed to assess which reports were actively being used so that we only migrated the useful reports and not the legacy baggage.

Thankfully, this is easily done as SSRS keeps an execution log for all the reports that it renders. The only thing you'll need to consider is how long that execution log tracks data for. The default is 60 days. If you want to update this to a longer period then the rule Do you keep track of which reports are being executed?. Thankfully at SSW, this was already set to retain the logs for 365 days.

So all that's left to do is write a query against the ExecutionLog table in the ReportServer database

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;

Credit goes to Russell Fox for this query (I've modified this to give distinct reports)

Comment