It’s probably safe to say, that most data professionals have written a report at some point in their life. And depending on the complexity of the report, it may have involved considerable time and effort. And the last thing someone who wrote a report wants to hear is that no one is using their report.
But, how can we tell for sure what the usage of reports on our instance of SQL Server Reporting Services (SSRS) really is?
Examining usage data from Reporting Services
One of the nice things about SSRS is that it provides us with information regarding report usage (http://technet.microsoft.com/en-us/library/ms159110.aspx), which is stored in the ReportServer database. And although the tables in that database are not that complicated, I would suggest that it is best to use the view, ExecutionLog3. Here is an example of the report usage data that can be retrieved using that view:
ExecutionId, -- Internal identifier associated with a request.
-- Requests on the same user sessions share the same execution id.
RequestType, -- Interactive or Subscription
[Format], -- Rendering format.
[Source], -- Source of the report execution.
ByteCount, -- Size of rendered reports in bytes.
[RowCount], -- Number of rows returned from queries.
ORDER BY TimeStart
If you examine the output for the code above, it is probably easy to see how a usage report could be made directly from this view or this data could be exported to another database and reported on from there. Exporting the data would be especially useful if you need to keep more data than is allowed by the retention period option or if you need to do further manipulations or analysis of it.
Examining usage data using a third-party tool
Another option for examining the usage of reports within SSRS is to look at something pre-built, such as SummitCloud’s SQL Scrubs, which is an SSRS Report Management solution (http://www.summitcloud.com/solutions/scrubs/default.aspx). The nice thing about this solution is there is a premium and free community edition (http://scrubs.codeplex.com/). One drawback about the free community edition, is that I don’t think it supports SQL Server 2012 at this time.
I have found this solution to be useful because it uses a SSIS package to pull the usage data into a separate database, which has a very user-friendly schema. This allows you to easily be able to create your own reports or do your own analysis of your report usage. This SSIS package also works regardless of whether you are using Reporting Services in native or SharePoint integrated mode. Additionally, there are some reports included, such as “Top Reports”, “Report Summary Generation Statistics”, and “Report Execution Detail”. The premium edition includes many more reports.
So, whether you do it yourself or use someone else’s solution, I think it is important to keep track of report usage because it may help you to determine which reports are most valuable to the business and even how they perform.