SSRS Usage Reporting — with SSRS!

A task I’ve recently needed to perform was to migrate our company SSRS portal from a SQL Server 2008 R2 Native Mode portal to a shiny new SQL Server 2012 Sharepoint Integrated Mode portal.

Additionally, during this process, I had a desire to clean up the organization of the reports and potentially retire some of the older reports which were no longer used.  The issue that I immediately encountered, however, was a lack of readily accessible information about which reports were frequently (or infrequently) used, by whom, and how often.

With a little bit of research on the topic, I found that SSRS does indeed track report usage metrics within the ReportServer database, however, there aren’t any built-in mechanisms to easily review that data.

My research first took me to a Microsoft Technet posting with the following T-SQL to collect some basic usage statistics from the ReportServer database:


SELECT 
Cat.[Path], 
Cat.Name, 
Ex.[Format], 
Ex.Parameters, 
Ex.UserName, 
Ex.TimeStart, 
CONVERT(nvarchar(10), 
Ex.TimeStart, 101) AS rundate,  
1 AS [Counter] 
FROM ExecutionLog AS Ex INNER JOIN [Catalog] AS Cat ON (Ex.ReportID = Cat.ItemID)
ORDER BY Ex.TimeStart DESC


This was a great indicator of the possibilities for reporting on SSRS usage.  A little further research turned me onto the following Wrox article by Jim Minatel with some additional sample queries and some great ideas for what I’d like to include in the SSRS Usage Report I was creating.

Within the ReportServer database (both for SSRS Native Mode and SSRS SharePoint Integrated Mode), the most interesting tables when it comes to SSRS usage statistics are:

  • ExecutionLog – The most useful table, this is a log of all SSRS component executions as well as the status of the execution.
  • Catalog – Equally useful, this is a full listing of all SSRS assets (report RDL files, data sources, datasets, and even Power View reports) which are needed to identify in a friendly manner the names of the items in the ExecutionLog.
  • Users – A listing of all users with their unique IDs and authentication types.  This table proved to be the least useful, as usernames are stored in a readable form in the above tables.
Performing T-SQL queries to join at least two of these tables allowed for me to create a base detail query which would provide the foundation for data shown in my SSRS Usage Report:


SELECT 
Cat.[Name] AS ReportName, 
Cat.[Path] AS ReportPath, 
Ex.[Format] AS ReportFormat, 
Ex.[UserName] AS RunBy, 
Ex.[TimeStart] AS RunDateTime, 
CONVERT(nvarchar(10), Ex.[TimeStart], 101) AS RunDate,  
CASE(Ex.[Source])
WHEN 1 THEN ‘Live’
WHEN 2 THEN ‘Cache’
WHEN 3 THEN ‘Snapshot’ 
WHEN 4 THEN ‘History’ 
WHEN 5 THEN ‘AdHoc’
WHEN 6 THEN ‘Session’
WHEN 7 THEN ‘Rdce’
ELSE ‘Unknown’
END AS [DataSource],
CASE(Ex.[RequestType])
WHEN 0 THEN ‘Interactive’
WHEN 1 THEN ‘Subscription’
WHEN 2 THEN ‘Refresh Cache’
ELSE ‘Unknown’
END AS RequestType,
1 AS [NumRuns], 
Ex.[TimeDataRetrieval],
Ex.[TimeProcessing],
Ex.[TimeRendering],
Ex.[ByteCount],
Ex.[RowCount]
FROM ExecutionLog AS Ex INNER JOIN [Catalog] AS Cat ON (Ex.ReportID = Cat.ItemID)
WHERE Cat.Type = 2
AND Cat.[Path] NOT LIKE ‘%Report Components%’
AND Ex.[TimeStart] BETWEEN @StartDate AND DATEADD(“d”,1,@EndDate)
ORDER BY Ex.TimeStart DESC


This query provided me with a listing of all reports executed within a variable time frame, including the report name, path, user, time, and how it was run.  Additionally, it excludes anything from the “Report Components” directory which is a repository in which I store non-user facing items, such as data sources and datasets that I do not want included in usage statistics.  Minor variations of this query taking advantage of different GROUP BY clauses were also used to produce various TOP stats (top users, top reports, etc).

One of the primary goals of my reporting was to tell me which reports were not used at all.  To accomplish this, I performed a variation of the above query which looks for items in the Catalog table which are not found in the ExecutionLog table:


SELECT 
cat.[Name] AS ReportName, 
cat.[Path] AS ReportPath, 
u.[UserName] AS ReportAuthor
FROM [Catalog] cat INNER JOIN dbo.Users u ON (cat.CreatedByID = u.UserID)
WHERE cat.[Type] = 2
AND Cat.[Path] NOT LIKE ‘%Report Components%’
AND cat.[Path] NOT LIKE ‘%template%’
AND cat.ItemID NOT IN
    (
SELECT ExecutionLog.ReportID
FROM ExecutionLog
WHERE ExecutionLog.TimeStart BETWEEN @StartDate AND DATEADD(“d”,1,@EndDate)
)
ORDER BY [Name], [Path]


For the final SSRS report, which includes a number of “interesting” statistics regarding report trends, top reports, top users, as well as a full listing of reports run and not run within the period, see below.

SSRS Usage Report

The full RDL of the usage report, which includes all queries within is available here.

Each report name includes a hyper link which, if correct, will open that report.  This is the one item I found which varies greatly between SSRS Native Mode and SSRS Integrated Mode — the base path for reports is very different, so the base URL may need to be modified to match your environment.

With the report created, I was able to get an idea of which reports were popular and which ones weren’t.  I performed the SharePoint migration, reorganized reports, and ultimately retired a few on the reports which were no longer used or required.

Additionally, I used a slightly modified version of the report showing only the “Top Reports” section and placed that within a page in our SharePoint BI portal.  This allows for report users to very quickly access whichever reports happen to be the most popular at the time.