You have a SSRS instance on either live or production and you want to find out which reports and most / least used.
Luckily SQL Server maintains by default SSRS usage info in audit logs it maintains. Although you can go only as far back as 60 days but this is quite useful in many cases and this can be extended.
To be able to access this information you would have to used System view named ExecutionLog traditionally and for backward compatibility. ExecutionLog2 for SQL Server 2008 and ExecutionLog3 for SQL server 2008 R2. Each subsequent newer version added more info and renamed and few columns.
For detailed documentation checkout here in MSDN. To be able to extend the 60 days default limit, fiddle with ExecutionLogDaysKept property (The number of days to keep report execution information in the execution log. Valid values for this property include 0 through 2,147,483,647. If the value is 0 entries are not deleted from the Execution Log table.)
An example of TSQL to extend this limit is like this,
EXEC SetConfigurationInfo @Name=N'ExecutionLogDaysKept',@Value=N'90'
Below are some example queries to play around with.
SELECT ex.UserName, ex.Format, ex.TimeStart, cat.Name, ex.Parameters, CONVERT(nvarchar(10), ex.TimeStart, 101) AS rundate FROM ExecutionLog AS ex INNER JOIN Catalog AS cat ON ex.ReportID = cat.ItemID ORDER BY ex.TimeStart DESC select * from ExecutionLog3 select * from Catalog select cat.path + '/' + cat.name Report, max(TimeStart) LastRunTime, count(reportid) NoOfExec from ExecutionLog3 ex, Catalog cat where ex. = cat.ItemID group by cat.path , cat.name order by NoOfExec desc order by max(TimeStart) desc select name, CreationDate from Catalog cat where cat.itemid not in (select reportid from ExecutionLog ex ) order by CreationDate desc where ex.ReportID = cat.ItemID)