Who's running your reports?
Reporting Services doesn't give you any tools to monitor report usage. Who's running these reports? How often? When did they last use them? Here's how I find out. (Surprise surprise - it's another report!)
I've developed over 130 Reporting Services reports and I want to know how much they're being used and by whom. They run off a dedicated reporting database that contains summarised data extracted from the OLTP databases so I'm reasonably well insulated from schema changes. (I highly recommend this approach - it's saved me so much trouble). But whenever there's a significant change to our systems I have to assess the impact on the reports. I need to know who to talk to when I need a decision from the business on how to handle a change, and I don't want to waste time maintaining reports that aren't being used.
I have two reports, one showing the last use of each report and the other to drill into the usage history of a specific report. Here are the queries I use:
I use this one to provide the values for the next query's @DataSourceID parameter:
create proc dbo.rpt_ListReportingDataSources
as
/*
----------------------------------------------------------------------
Version: 1.0
Date: 14/05/2008
----------------------------------------------------------------------
*/
set nocount on
select cast(null as uniqueidentifier) as DataSourceID
, 'All' as DataSourceName
union all
select ItemID
, [Name]
from ReportServer.dbo.Catalog dscat with (nolock)
where [Type] = 5
go
This one lists who last ran each report and when:
create proc dbo.rpt_ITReportsSummary
(
@DataSourceID uniqueidentifier = null
)
as
/*
----------------------------------------------------------------------
Version: 1.2
Date: 14/05/2008
Purpose: List all reports with who last ran each one and when.
----------------------------------------------------------------------
*/
set nocount on
select r.Path as FullName
, r.Name as [Name]
, dscat.[Name] as DataSource
, runcount.RunCount
, runcount.FirstRun
, lastrun.LastRun
, lastrun.LastRunBy
, mostrun.MostRunBy
, r.ItemID as ReportID
from ReportServer.dbo.Catalog r with (nolock)
inner join ReportServer.dbo.DataSource ds with (nolock) on ds.ItemID = r.ItemID
inner join ReportServer.dbo.Catalog dscat with (nolock) on dscat.ItemID = ds.Link
left join
(
select ReportID
, count(*) as RunCount
, min(TimeStart) as FirstRun
from ReportServer.dbo.ExecutionLog with (nolock)
where UserName <> '<domain-name>\davidwimbush'
group by ReportID
) runcount on runcount.ReportID = r.ItemID
left join
(
select ReportID
, TimeStart as LastRun
, UserName as LastRunBy
, row_number() over (partition by ReportID order by ReportID asc,TimeStart desc) as SeqNo
from ReportServer.dbo.ExecutionLog with (nolock)
where UserName <> '<domain-name>\davidwimbush'
) lastrun on lastrun.ReportID = r.ItemID
left join
(
select ReportID
, UserName as MostRunBy
, count(*) as UserRunCount
, row_number() over (partition by ReportID order by ReportID asc, count(*) desc) as SeqNo
from ReportServer.dbo.ExecutionLog with (nolock)
where UserName <> '<domain-name>\davidwimbush'
group by ReportID
, UserName
) mostrun on mostrun.ReportID = r.ItemID
where r.Type = 2 -- Report
and (@DataSourceID is null or ds.Link = @DataSourceID)
and isnull(lastrun.SeqNo,1) = 1
and isnull(mostrun.SeqNo,1) = 1
order by r.Name
go
This one lists everyone who ran a specific report and when, using the report ID from the previous query:
create proc dbo.rpt_ITReportHistory
(
@ReportID uniqueidentifier = null
)
as
/*
----------------------------------------------------------------------
Version: 1.2
Date: 14/05/2008
Purpose: List who ran this report and when.
----------------------------------------------------------------------
*/
set nocount on
select el.TimeStart as WhenRun
, el.UserName as RunBy
from ReportServer.dbo.ExecutionLog el with (nolock)
where el.ReportID = @ReportID
order by el.TimeStart desc
go