RSS

SSRS Report Usage Statistics

09 Jul

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

 

Advertisements
 

Tags: , , ,

5 responses to “SSRS Report Usage Statistics

  1. Feodor

    November 21, 2012 at 9:11 AM

    I actually wrote a SSRS Performance Dashboard: http://sqlconcept.com/tools/ssrs-performance-dashboard/
    It is a good tool to automate your monitoring of the SSRS.

     
  2. Chris Brewer

    September 19, 2017 at 11:30 AM

    Thanks for posting this…good stuff!

     
  3. Arun

    December 4, 2017 at 2:12 PM

    Hi All

    Below script gives me total no.of hits per day , but i want to find total number of users hits per day

    SELECT CONVERT(VARCHAR(25),TIMESTART,101), COUNT(*)
    FROM EXECUTIONLOG2 (NOLOCK)
    WHERE TIMESTART>=DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)
    GROUP BY CONVERT(VARCHAR(25),TIMESTART,101)
    ORDER BY CONVERT(VARCHAR(25),TIMESTART,101)

     

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: