As a part of my series leading up to Christmas 2012, I shared a script to Report on SSRS Subscriptions. It was soon found to have a bug with SQL Server 2008 R2 SP2. IN the comments on that post, I promised to post an updated script. Here is that update – without the bug.
DECLARE @ReportName VARCHAR(100)
SET @ReportName = NULL;
CREATE TABLE #morepower (MonthDate BIGINT,N BIGINT,PowerN BIGINT PRIMARY KEY CLUSTERED
,NameofMonth VARCHAR(25),WkDay VARCHAR(25))
;
WITH powers(powerN, n) AS (
SELECT POWER(2,number), number
FROM master.dbo.spt_values
WHERE type = 'P' AND number < 31)
INSERT INTO #morepower ( MonthDate ,N,PowerN ,NameofMonth ,WkDay)
SELECT ROW_NUMBER() OVER (ORDER BY N) AS MonthDate,N,PowerN
,CASE WHEN N BETWEEN 0 AND 11
THEN DateName(month,DATEADD(month,N+1,0)-1)
ELSE NULL
END AS NameofMonth
,CASE WHEN N BETWEEN 0 AND 6
THEN DATENAME(weekday,DATEADD(DAY,n+1,0)-2)
ELSE NULL
END AS WkDay
FROM powers
SELECT DISTINCT s.ScheduleID,Ca.Path as ReportManagerPath,Ca.Name as ReportName
, U.UserName as SubscriptionCreator
,Su.Description as SubscriptionDescription,S.StartDate,Su.LastRunTime
,CASE
WHEN s.RecurrenceType = 1 THEN 'One Off'
WHEN s.RecurrenceType = 2 THEN 'Hour'
WHEN s.RecurrenceType = 4 THEN 'Daily'
WHEN s.RecurrenceType = 5 THEN 'Monthly'
WHEN s.RecurrenceType = 6 THEN 'Week of Month'
END AS RecurrenceType
,s.EventType
,ISNULL(REPLACE(REPLACE(STUFF(
(Select ', ['+CONVERT(VARCHAR(20),MonthDate)+']' AS [text()]
FROM #morepower m1
WHERE m1.powerN < s.DaysofMonth+1
AND s.DaysofMonth & m1.powerN <>0
ORDER BY N FOR XML PATH(''), TYPE).value('.','VARCHAR(MAX)')
, 1, 2, ''),'[',''),']','')
,'N/A') AS DaysofMonth
,ISNULL(c1.NameOfMonth,'N/A') AS MonthString
,ISNULL(c2.WkDays,'N/A') AS DaysofWeek
,CASE MonthlyWeek
WHEN 1 THEN 'First'
WHEN 2 THEN 'Second'
WHEN 3 THEN 'Third'
WHEN 4 THEN 'Fourth'
WHEN 5 THEN 'Last'
ELSE 'N/A'
END AS MonthlyWeek
,ISNULL(CONVERT(VARCHAR(10),s.DaysInterval),'N/A') AS DaysInterval
,ISNULL(CONVERT(VARCHAR(10),s.MinutesInterval),'N/A') AS MinutesInterval
,ISNULL(CONVERT(VARCHAR(10),s.WeeksInterval),'N/A') AS WeeksInterval
FROM #morepower mp, dbo.Schedule s
INNER JOIN ReportSchedule RS
ON S.ScheduleID = RS.ScheduleID
INNER JOIN Catalog Ca
ON Ca.ItemID = RS.ReportID
INNER JOIN Subscriptions Su
ON Su.SubscriptionID = RS.SubscriptionID
INNER JOIN Users U
ON U.UserID = S.CreatedById
OR U.UserID = Su.OwnerID
CROSS APPLY (Select s.ScheduleID,REPLACE(REPLACE(STUFF(
(SELECT ', ['+ NameofMonth + ']' AS [text()]
FROM #morepower m1 ,dbo.Schedule s1
WHERE m1.NameofMonth IS NOT NULL
AND m1.powerN & s1.Month <>0
AND s1.ScheduleID = s.ScheduleID
ORDER BY N FOR XML PATH(''), TYPE).value('.','VARCHAR(MAX)')
, 1, 2, ''),'[',''),']','') AS NameOfMonth)c1
CROSS APPLY (SELECT s.ScheduleID,REPLACE(REPLACE(STUFF(
(SELECT ', [' + WkDay + ']' AS [text()]
FROM #morepower m1 ,dbo.Schedule s2
WHERE m1.WkDay IS NOT NULL
AND DaysOfWeek & m1.powerN <>0
AND s2.ScheduleID = s.ScheduleID
ORDER BY N FOR XML PATH(''), TYPE).value('.','VARCHAR(MAX)')
, 1, 2, ''),'[',''),']','') AS WkDays) c2
WHERE Ca.Name = ISNULL(@ReportName,Ca.Name);
DROP TABLE #morepower;
The inline code seen above likes to reformat and and will throw an error due to capitalization of the function value and text(). Download the script here: SSRS_SubscriptionsV1_5
