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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 |
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