Reports and SSRS - Get Report Notifications 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:    Sorna Kumar Muthuraj
Date:        9 Jul 2011

Description

Retrieves a summary of the subscription notifications sent for one or many reports.

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

Code

Stored Procedure:


DROP PROC usp_GetReportNotificationSummary
GO

CREATE PROC usp_GetReportNotificationSummary @ReportName NVARCHAR(850) = NULL
AS

/****************************************************
Purpose:   To retrieve a summary of the subscription notifications sent
           for one or many reports.
           Uses the Catalog, Notifications and ActiveSubscriptions tables
           in the report server database (assumed to be called by its default
           name of 'ReportServer')

Author:        Sorna Kumar Muthuraj
History:   9 Jul 2011 - Initial Issue
          
*****************************************************/


SELECT
  
c.ItemID                    AS ReportID,
  
C.Name                      AS ReportName,
  
C.Path                      AS ReportFolder,
    
S.Description              AS SubscriptionDescription,
    
N.NotificationEntered      AS NotificationEntered,
    
A.TotalNotifications       AS TotalNotifications,
    
A.TotalSuccesses           AS TotalSuccesses,
    
A.TotalFailures            AS TotalFailures
FROM ReportServer.dbo.Notifications N (NOLOCK)
INNER JOIN ReportServer.dbo.ActiveSubscriptions A
  
ON N.SubscriptionID = A.SubscriptionID
  
AND N.ActivationID = A.ActiveID
INNER JOIN ReportServer.dbo.Catalog C (NOLOCK)
  
ON C.ItemID = N.ReportID
INNER JOIN ReportServer.dbo.Subscriptions S (NOLOCK)
  
ON S.SubscriptionID = N.SubscriptionID
WHERE c.Name LIKE '%' + ISNULL(@ReportName, '') + '%'
ORDER BY
  
C.Name,
  
S.Description

ċ
usp_GetReportNotificationSummary.sql
(1k)
Andy Hughes,
Jul 28, 2012, 9:52 AM
Comments