SSRS Report Usage Statistics

The first job I got out of college was what introduced me to SQL Server Reporting Services. Initially I thought the job was a less technical reporting analyst position with a focus on competitive/business intelligence.

I found myself loving the technical work more and more; my concentration was in DBMS in college so this just built upon that love. Although I don’t work with SSRS as much anymore, I still love going back and doing report development, maintaining the reporting environment, and looking at report usage stats.

The initial reason I started collecting report usage statistics was because many of our users kept asking for report changes but they would never use the reports! This can help evaluate which reports are being used and which are being neglected and maybe help prioritize fixes/enhancements to already existing reports.

I put some explaination in the comments of what each query does, feel free to customize as necessary.

Click Here for the code in .sql file format. File name is Report Usage Information for SSRS.sql and is located under the “Code for Posts” folder. It looks much nicer in Management Studio 🙂


--—-Last Time a Report was used (by execution start date/time
SELECT DISTINCT C.NAME,MAX(EL.TIMESTART)
FROM REPORTSERVER.DBO.EXECUTIONLOG (NOLOCK) EL INNER JOIN
REPORTSERVER.DBO.CATALOG (NOLOCK) C ON EL.REPORTID=C.ITEMID
--–WHERE USERNAME = ''
GROUP BY C.NAME
ORDER BY C.NAME

/*–—Report usage Stats by Date
–—How many reports were viewed on a specific day
–—For the where clause, it might be advisable to insert the name of the account that runs SSRS.
–—The reason being, if you have a report that is run off of cache or has subscription, the counts will show here.
–—So if you want to see reports that have been run by users only then filter out the account that runs the SSRS service.*/

SELECT CONVERT(VARCHAR(25),TIMESTART,101), COUNT(*)
FROM REPORTSERVER.DBO.EXECUTIONLOG (NOLOCK)
–WHERE USERNAME NOT IN
GROUP BY CONVERT(VARCHAR(25),TIMESTART,101)
ORDER BY CONVERT(VARCHAR(25),TIMESTART,101)

–—Report usage Hourly Stats
–—When the report server is used during the day

SELECT DATEPART(HOUR,TIMESTART) AS HOUR,COUNT(*)
FROM REPORTSERVER.DBO.EXECUTIONLOG (NOLOCK)
–—WHERE USERNAME NOT IN
GROUP BY DATEPART(HOUR,TIMESTART)
ORDER BY DATEPART(HOUR,TIMESTART)

–—Detailed
SELECT EL.USERNAME,C.NAME,EL.TIMESTART,EL.TIMEEND, EL.PARAMETERS, EL.SOURCE
FROM REPORTSERVER.DBO.EXECUTIONLOG (NOLOCK) EL INNER JOIN
REPORTSERVER.DBO.CATALOG (NOLOCK) C ON EL.REPORTID=C.ITEMID
––WHERE EL.USERNAME NOT IN
ORDER BY TIMESTART DESC

–—Reports used by user
SELECT EL.USERNAME,C.NAME, COUNT(1)
FROM REPORTSERVER.DBO.EXECUTIONLOG (NOLOCK) EL INNER JOIN
REPORTSERVER.DBO.CATALOG (NOLOCK) C ON EL.REPORTID=C.ITEMID
GROUP BY EL.USERNAME,C.NAME
ORDER BY EL.USERNAME,C.NAME

––Usage by Report
–—Shows how many times a report has been executed in the past 90 days

SELECT C.NAME, COUNT(1)
FROM REPORTSERVER.DBO.EXECUTIONLOG (NOLOCK) EL INNER JOIN
REPORTSERVER.DBO.CATALOG (NOLOCK) C ON EL.REPORTID=C.ITEMID
––WHERE EL.USERNAME NOT IN
GROUP BY C.NAME
ORDER BY COUNT(1) DESC

–—Recent usages per user per report
SELECT EL.USERNAME,C.NAME,EL.TIMESTART,EL.TIMEEND
FROM REPORTSERVER.DBO.EXECUTIONLOG (NOLOCK) EL INNER JOIN
REPORTSERVER.DBO.CATALOG (NOLOCK) C ON EL.REPORTID=C.ITEMID
––WHERE USERNAME = ”
ORDER BY TIMESTART DESC

––Specific Reports
–—Enter name of specific report that you would like information about
SELECT EL.USERNAME,C.NAME,EL.TIMESTART,EL.TIMEEND
FROM REPORTSERVER.DBO.EXECUTIONLOG(NOLOCK) EL INNER JOIN
REPORTSERVER.DBO.CATALOG (NOLOCK) C ON EL.REPORTID=C.ITEMID
WHERE PATH LIKE ‘%%’
ORDER BY TIMESTART DESC