Friends,
We all know about our managers, they are like piles, both wont allow us to sit peacefully for 10 mins. I worked under many managers and not even one allowed me to take rest when I don’t have any work. One guy given me a shit work i.e “Finding out the usage of reports deployed in server”.
If we jump into story, We worked hard day and night and deployed reports in server after proper testing. So, few days no work and once day manager called me and asked me to give the usage of EACH report in server. I asked Why and for that he replied because WE both doesn’t have any work.
After some goggling I found a way to get that report in one shot and I am sharing the same for you guys. All the report related information will be stored in “ExecutionLog” table in “Report Server” Database. PFB the query that pulls the Report usage information of SSRS Reports.
SELECT
UserName, Format, TimeStart, Name,
Parameters,
CONVERT(nvarchar(10), TimeStart, 101) AS rundate
FROM ExecutionLog, Catalog
where ReportID = Catalog.ItemID
ORDER BY TimeStart DESC
If you wish to see how many times a report was run then you can use the below code
SELECT
Name,
COUNT(*)
FROM ExecutionLog, Catalog
where ReportID = Catalog.ItemID
Group by Name
Hope this will help you guys .. Happy Coding !!
Regards,
Roopesh Babu V
