Reports and SSRS - 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

ċ
usp_GetReportExecutionSummary.sql
(3k)
Andy Hughes,
Jul 28, 2012, 10:17 AM
Comments