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
|