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
|
 Updating... usp_GetReportExecutionSummary.sql (3k) Andy Hughes, Jul 28, 2012, 10:17 AM
|
