The ExecutionLogStorage table in the ReportServer database contains a wealth of information to help manage reports. This table contains who, what, when, and how long a report was used. Even reports that were used in subscriptions are listed in this table, along with failed reports.

The ExecutionLogStorage table contains only 60 days' worth of data. This is not enough to determine trends of report use. There may be end of quarter reports that are heavily used only a couple of days a quarter.

There are two approaches for increasing how many days of data are stored in this table. One is to increase the setting for how many days are maintained in the ExecutionLogStorage table. If this technique is used, then make sure to keep an eye on the growth of the table. To increase the number of days that are saved, follow these steps:

The second approach would be to have the data in the ExecutionLogStorage table, summarized and placed in a data warehouse. This will allow you to see trends over time, without causing the ExecutionLogStorage table to grow too quickly.

Instead of querying the ExecutionLogStorage table directly, you can use a view that is provided with Reporting Services called ExecutionLog3. This is a great view to use, since numeric flags have been changed to text for you. The following query summarizes how many reports were run each month by user:

SELECT
  ItemPath
   ,CONVERT(varchar(2), MONTH(TimeStart)) + '/' + CONVERT(varchar(4), YEAR(TimeStart)) AS ExecutionMonth
   ,UserName
   ,COUNT(ExecutionId) AS CountExecutions
   ,COUNT(DISTINCT ExecutionId) AS CountDistinctExecutions
   ,AVG(TimeDataRetrieval) AVG_TimeDataRetrieval
   ,MAX(TimeDataRetrieval) AS MAX_TimeDataRetrieval
   ,AVG(TimeProcessing) AS AVG_TimeProcessing
   ,MAX(TimeProcessing) AS MAX_TimeProcessing
   ,AVG(TimeRendering) AS AVG_TimeRendering
   ,MAX(TimeRendering) AS MAX_TimeRendering
   ,Status
FROM
  ReportServer.dbo.ExecutionLog3
WHERE
  RequestType = 'Interactive'
  AND [format] = 'RPL'
  AND ItemPath <> 'Unknown'
GROUP BY
  Itempath
   ,CONVERT(varchar(2), MONTH(TimeStart))
   ,CONVERT(varchar(4), YEAR(TimeStart))
   ,UserName
   ,Status
ORDER BY
  Itempath
   ,UserName  

The TimeDataRetrieval, TimeProcessing, and TimeRendering fields are given in milliseconds. These three fields breakdown how long it takes to create a rendered report and can be very useful to pinpoint which part of the process is causing a slow running report.

COUNT(DISTINCT ExecutionId) was used, since the log contains each time the user clicked on the View Report button. By counting the distinct values, you will get how many times they opened the report as opposed to how many times they clicked on the button.

By viewing these metrics, you can determine the usage of the reports over time, which will help you determine reports that need improvement and the reports that should be archived because of disuse.