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