Get Report Execution Summary

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