If
we extract rows from “[ReportServer].[dbo].[Schedule]”,we
will notice that most of the infromations are stored in form of PowerN.
Months will be stored as following:
January – will be
represented by 1
February – will be
represented by 2
March – will be
represented by 4
April – will be
represented by 8
May – will be
represented by 16
June – will be
represented by 32 and so on…
Days will be stored as following:
Sunday – will be
represented by 1
Monday – will be
represented by 2
Tuesday – will be
represented by 4
Wednesday – will be
represented by 8
Thursday – will be
represented by 16
Friday – will be
represented by 32
Saturday – will be
represented by 64 and so on…
RecurrenceTypes will be stored as following:
One off (schedule for once) – will be represented by 1
Hour – will be represented by 2
Daily – will be represented by 4
Monthly – will be represented by 5
Week of Month – will be represented by 6
Following Sql pulls
the report schedule information into human readable form
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 1 As powerN,0 As n union All
Select 2 As powerN,1 As n union All
Select 4 As powerN,2 As n union All
Select 8 As powerN,3 As n union All
Select 16 As powerN,4 As n union All
Select 32 As powerN,5 As n union All
Select 64 As powerN,6 As n union All
Select 128 As powerN,7 As n union All
Select 256 As powerN,8 As n union All
Select 512 As powerN,9 As n union All
Select 1024 As powerN,10 As n union All
Select 2048 As powerN,11 As n union All
Select 4096 As powerN,12 As n union All
Select 8192 As powerN,13 As n union All
Select 16384 As powerN,14 As n union All
Select 32768 As powerN,15 As n union All
Select 65536 As powerN,16 As n union All
Select 131072 As powerN,17 As n union All
Select 262144 As powerN,18 As n union All
Select 524288 As powerN,19 As n union All
Select 1048576 As powerN,20 As n union All
Select 2097152 As powerN,21 As n union All
Select 4194304 As powerN,22 As n union All
Select 8388608 As powerN,23 As n union All
Select 16777216 As powerN,24 As n union All
Select 33554432 As powerN,25 As n union All
Select 67108864 As powerN,26 As n union All
Select 134217728 As powerN,27 As n union All
Select 268435456 As powerN,28 As n union All
Select 536870912 As powerN,29 As n union All
Select 1073741824
As powerN,30 As n
)
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
,Su.Description as SubscriptionDescription,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
,Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="TO"])[1]','nvarchar(50)') as [To]
,Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="CC"])[1]','nvarchar(50)') as [CC]
,Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="RenderFormat"])[1]','nvarchar(50)') as [Render Format]
,convert(varchar(max),su.[Parameters])[Parameters],convert(varchar(max),su.ExtensionSettings) As ExtensionSettings
,convert(varchar(max),su.DataSettings) As DataSettings
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
CROSS APPLY (Select s.ScheduleID,REPLACE(REPLACE(STUFF(
(SELECT ', ['+ NameofMonth + ']' AS [text()]
FROM #morepower m1 ,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 ,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;
