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)