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
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
I have blacked out the names to protect the innocent and my job.
Categories: 2010, SQL Server, SSRS