Archive

Archive for the ‘SSRS’ Category

Who ran what?

June 10, 2010 2 comments

Request came in from above to see how many times the Finance Reports have been run and who ran them.

I took a look a look at the ReportServer database and started looking at the tables until I found the two tables that gave me what I needed.  Found that I could get this data out of the Executionlog table and join it to the Catalog table to get what I needed.

The following Script give me a list of of each report, when it was run, and who ran it.

SELECT C.NAME as ReportName, E.UserName, E.TimeStart, E.Format, E.Status
FROM EXECUTIONLOG E JOIN CATALOG C ON (E.ReportID = C.ItemID)
WHERE PATH LIKE '%Finance%'
ORDER BY [ByteCount] DESC

Report view 1

This is good, but the follow up question will be, can you get me a list of how many times a report has been run?  Why yes, I can do that too.

SELECT COUNT(Name) AS [Report Count], Name AS [Report Name]
FROM Catalog C JOIN ExecutionLog E ON C.ItemID = E.ReportID
WHERE PATH LIKE '%Finance%'
GROUP BY Name
ORDER BY COUNT(Name) DESC, Name

Report view 2

I have blacked out the names to protect the innocent and my job.

Advertisements
Categories: 2010, SQL Server, SSRS