Get Report Notifications Summary

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