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...
Andy Hughes, Jul 28, 2012, 10:17 AM
|