Get Report Execution Summary

Applicability:

SQL Server 2000: Not Tested

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Not Tested

Credits:

Author: ChillyDBA

Date: 20 Dec 2010

Description

Retrieves a summary of executions for one or many reports. Executions are summarized by time period

NOTE: It is assumed that the Report Server Database has its default name of 'ReportServer'

Code

Stored Procedure:

DROP PROC usp_GetReportExecutionSummary

GO

CREATE PROC usp_GetReportExecutionSummary @ReportName NVARCHAR(850) = NULL

AS

/****************************************************

Purpose: To retrieve a summary of executions for one or

many reports. Executions are summarized by time period

Uses the Catalog, and ExecutionLogStorage tables

in the report server database (assumed to be called by its default

name of 'ReportServer')

NOTE: There is a time period called 'Not Run'

The original idea behind this report was to help identify obsolete reports

and I considered that not being run for over a year was a fairt indication

of obsolescence

Author: ChillyDBA

History: 20 Dec 2010

*****************************************************/

SELECT

x.ItemID AS ReportID,

x.Name AS ReportName,

x.Path AS ItemPath,

MAX(x.TimeStart) AS DateLastRun,

SUM(RunsinLastWeek) AS RunsinLastWeek,

SUM(RunsinLastMonth) AS RunsinLastMonth,

SUM(RunsinLast3Months) AS RunsinLast3Months,

SUM(RunsinLastYear) AS RunsinLastYear,

SUM(NotRun) AS NotRun,

MAX(CASE WHEN subs.report_oid IS NULL THEN 0 ELSE 1 END) AS HasSubscription

FROM

(

SELECT

c.ItemID,

c.Name,

c.Path,

e.TimeStart,

CASE WHEN ISNULL(e.TimeStart, '1 Jan 1900') BETWEEN DATEADD(ww, -1, GETDATE()) AND GETDATE() THEN 1 ELSE 0 END AS 'RunsinLastWeek',

CASE WHEN ISNULL(e.TimeStart, '1 Jan 1900') BETWEEN DATEADD(mm, -1, GETDATE()) AND DATEADD(ww, -1, GETDATE()) THEN 1 ELSE 0 END AS 'RunsinLastMonth',

CASE WHEN ISNULL(e.TimeStart, '1 Jan 1900') BETWEEN DATEADD(mm, -3, GETDATE()) AND DATEADD(mm, -1, GETDATE()) THEN 1 ELSE 0 END AS 'RunsinLast3Months',

CASE WHEN ISNULL(e.TimeStart, '1 Jan 1900') BETWEEN DATEADD(yy, -1, GETDATE()) AND DATEADD(mm, -3, GETDATE()) THEN 1 ELSE 0 END AS 'RunsinLastYear',

CASE WHEN ISNULL(e.TimeStart, '1 Jan 1900') < DATEADD(yy, -1, GETDATE()) THEN 1 ELSE 0 END AS 'NotRun'

FROM ReportServer..Catalog AS c (NOLOCK)

LEFT OUTER JOIN ReportServer..ExecutionLogStorage AS e (NOLOCK)

ON e.ReportID = c.ItemID

WHERE c.TYPE IN (2)

AND c.Name LIKE '%' + ISNULL(@ReportName, '') + '%'

) AS x

LEFT OUTER JOIN ReportServer.dbo.Subscriptions subs ON x.itemid = subs.report_oid

WHERE Name <> ''

GROUP BY

x.ItemID,

x.Name,

x.Path

ORDER BY

x.ItemID,

x.Path,

x.Name