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
Advertisements

One thought on “Who’s running your reports?

  1. Pingback: Reporting on report usage revisited | David Wimbush

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s