February 24, 2014

How to get SSRS Usage Report

In my personal experience, there will be number of report published in reporting services for the business analysis. Though, SQL Server Reporting Service has made life easy to pull the data for the analysis purpose, developer has to work hard to do information engineering and to make worthy looking for doing the analysis. There will be number of parameterized KPI reports which keeps changing and accordingly new reports will be deployed in the report server. It becomes necessary to know what reports and how often reports are being used.

As there will be many reports deployed in the report server, it is mandatory to know execution time of each report and at what time the report server takes maximum amount of time to run the report. Based on above mentioned things maintenance of the report can be done.

SQL Server Reporting Services captures all the necessary data related to the each report execution on a table called "ExecutionLog" in the ReportServer database. There is another table named "Catalog" which is associated with the table ExecutionLog, these two table will help to get the information of each report. You can get as much as information you want to get using these tables.

Below is the example:

Cat.Name AS Report_Name, 
AVG(Ex.TimeDataRetrieval) AS AVGQueryTime, 
MIN(Ex.TimeDataRetrieval) AS MINQueryTime, 
MAX(Ex.TimeDataRetrieval) AS MAXQueryTime, 
STDEV(Ex.TimeDataRetrieval) AS SDQueryTime, 
AVG(Ex.TimeProcessing) AS AVGProcessTime, 
MIN(Ex.TimeProcessing) AS MINProcessTime, 
MAX(Ex.TimeProcessing) AS MAXProcessTime, 
STDEV(Ex.TimeProcessing) AS SDProcessTime, 
AVG(Ex.TimeRendering) AS AVGRenderTime, 
MIN(Ex.TimeRendering) AS MINRenderTime, 
MAX(Ex.TimeRendering) AS MAXRenderTime, 
STDEV(Ex.TimeRendering) AS SDRenderTime, 
AVG(Ex.ByteCount) AS AVGReportrSize, 
AVG(Ex.[RowCount]) AS AVGRowCount, 
MIN(Ex.[RowCount]) AS MINRowCount, 
MAX(Ex.[RowCount]) AS MAXRowCount, 
COUNT(Ex.ReportID) AS [TotalExecutions],
Ex.Format, Ex.TimeStart
FROM ExecutionLog as EX 
INNER JOIN Catalog as Cat ON Ex.ReportID = Cat.ItemID 
GROUP BY Cat.Name, Ex.Format, Ex.TimeStart

No comments: