How to get SSRS Report Usage from Sharepoint in Sharepoint Integrated Mode

SQL Reporting Services in Sharepoint Integrated Mode (MOSS 2007) has always been a little quirky to install, but can be very convenient to organize reports, by leveraging all the Sharepoint metadata, permissions, grouping, filtering and other user interface features.

Reporting Services defaults to 60 days for the execution log.  To change it, go to Central Administration, Application Management, Reporting Services, Set Server Defaults and change the Report Processing Log.

To find out how often reports are being used in your sharepoint installation, you can query the RSSharepoint.Catalog table, join on the Execution Log, and then join against the SP databases to get the metadata for the reports. 

Below is a sample query: (replace list ID with your Report Library list ID)

 –Gets metadata from SP database
Use WSS_Content_XX_DB1
SELECT  A.tp_leafname As RDL, A.ntext2 as Description, A.ntext3 as Business_Group, A.ntext4 as Application
INTO #Temp1
FROM AllUserData A
INNER JOIN AllLists L
ON A.tp_ListID = L.TP_ID
WHERE L.tp_ID = ‘1a98d11d-dc3f-4e3d-acf6-d0499019beb9’
AND A.tp_IsCurrentVersion = 1
–gets Report data from RS database

USE RSSHAREPOINT

SELECT      C.Name, MAX (EL.TimeStart) As TimeStart, CountOfLogRecords = count(el.ReportID), MAX (EL.UserName) AS LastUser
INTO #Temp2
FROM         Catalog As C WITH (NOLOCK)
left JOIN ExecutionLog As EL
ON
C.ItemID = EL.ReportID
Group By C.Name
–JOIN 2 TEMP TABLES
SELECT  t2.*,  t1.Business_Group, t1.Application, t1.Description
FROM #TEMP1 AS T1
JOIN #TEMP2 AS T2
ON T1.Rdl = t2.name

Order by TimeStart DESC

–Clear temp table
Drop Table #Temp1
Drop Table #Temp2

This will output something like this:

Keep in mind, the TimeStart is the last start time, and count of records is number of times ran within the period (default is 60 days).

This is a great way to find out who is running reports and which reports are not even being used.