4/27/2011

ReportServer ExecutionLog LINK

Here's a good link to create a quick Stored Procedure that will allow you to query the reports by report name or user.

http://www.sqlservercentral.com/scripts/Miscellaneous/31516/

I modified the SProc to roll up reports by ReportName and count(*) to see which reports are the heavy hitters and which ones never get run.

Here's the modified SProc:


USE [ReportServer]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE proc [dbo].[report_usage_grouped_by_report] @report varchar (75), @user varchar (15)
as
select  -- report path and name
      C.path,
      count(*) ReportCount
from         reportserver.dbo.ExecutionLog E
            join reportserver.dbo.catalog C on E.reportid = C.itemid

where       (C.name = @report or @report = '')
          and (right(E.username,3) = @user or @user = '')
group by C.path
order by  2 desc

--execute report_usage '',''
/*
--to feed your report parameter @report
select name from reportserver.dbo.catalog where  type = 2
union
select ''

--to feed your report parameter @user
select distinct username  from reportserver.dbo.ExecutionLog
union
select ''
*/

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.

Thoughts to Ponder