SSRS 2008 R2 Get Human Readable Information from the ReportServer Database

Question

I want to extract the “human readable person” information from the ReportServer.dbo.Schedule table using t-sql.

The following is a human readable example.

At 6:02 AM every Sun, Mon, Tue, Wed, Thu, Fri, Sat of every week, starting 2/28/2011

The table has a bunch of numeric fields that are used to store the schedule, but I would like to convert them to words, as in my example.

Has anyone ever done this using Reporting Services?

Answer 1

SQL is not suitable for string processing or bitwise operations, and a moderate bit is required to parse this table. I'm sure SSRS does not do this in SQL: I could probably write this in half time and half lines in C #.

 USE ReportServer; WITH EnhancedSched AS ( SELECT dbo.Schedule.ScheduleID , dbo.Schedule.Name , dbo.Schedule.StartDate , dbo.Schedule.Flags , dbo.Schedule.NextRunTime , dbo.Schedule.LastRunTime , dbo.Schedule.EndDate , dbo.Schedule.RecurrenceType , dbo.Schedule.MinutesInterval , dbo.Schedule.DaysInterval , dbo.Schedule.WeeksInterval , dbo.Schedule.DaysOfWeek , dbo.Schedule.DaysOfMonth , dbo.Schedule.Month , dbo.Schedule.MonthlyWeek , dbo.Schedule.State , dbo.Schedule.LastRunStatus , dbo.Schedule.ScheduledRunTimeout , dbo.Schedule.CreatedById , dbo.Schedule.EventType , dbo.Schedule.EventData , dbo.Schedule.Type , dbo.Schedule.ConsistancyCheck , dbo.Schedule.Path , CASE WHEN DaysOfWeek & 1 <> 0 THEN 'Sun, ' ELSE '' END + CASE WHEN DaysOfWeek & 2 <> 0 THEN 'Mon, ' ELSE '' END + CASE WHEN DaysOfWeek & 4 <> 0 THEN 'Tue, ' ELSE '' END + CASE WHEN DaysOfWeek & 8 <> 0 THEN 'Wed, ' ELSE '' END + CASE WHEN DaysOfWeek & 16 <> 0 THEN 'Thu, ' ELSE '' END + CASE WHEN DaysOfWeek & 32 <> 0 THEN 'Fri, ' ELSE '' END + CASE WHEN DaysOfWeek & 64 <> 0 THEN 'Sat, ' ELSE '' END AS DaysOfWeekString , CASE WHEN DaysOfMonth & 1 <> 0 THEN '1,' ELSE '' END + CASE WHEN DaysOfMonth & 2 <> 0 THEN '2,' ELSE '' END + CASE WHEN DaysOfMonth & 4 <> 0 THEN '3,' ELSE '' END + CASE WHEN DaysOfMonth & 8 <> 0 THEN '4,' ELSE '' END + CASE WHEN DaysOfMonth & 16 <> 0 THEN '5,' ELSE '' END + CASE WHEN DaysOfMonth & 32 <> 0 THEN '6,' ELSE '' END + CASE WHEN DaysOfMonth & 64 <> 0 THEN '7,' ELSE '' END + CASE WHEN DaysOfMonth & 128 <> 0 THEN '8,' ELSE '' END + CASE WHEN DaysOfMonth & 256 <> 0 THEN '9,' ELSE '' END + CASE WHEN DaysOfMonth & 512 <> 0 THEN '10,' ELSE '' END + CASE WHEN DaysOfMonth & 1024 <> 0 THEN '11,' ELSE '' END + CASE WHEN DaysOfMonth & 2048 <> 0 THEN '12,' ELSE '' END + CASE WHEN DaysOfMonth & 4096 <> 0 THEN '13,' ELSE '' END + CASE WHEN DaysOfMonth & 8192 <> 0 THEN '14,' ELSE '' END + CASE WHEN DaysOfMonth & 16384 <> 0 THEN '15,' ELSE '' END + CASE WHEN DaysOfMonth & 32768 <> 0 THEN '16,' ELSE '' END + CASE WHEN DaysOfMonth & 65536 <> 0 THEN '17,' ELSE '' END + CASE WHEN DaysOfMonth & 131072 <> 0 THEN '18,' ELSE '' END + CASE WHEN DaysOfMonth & 262144 <> 0 THEN '19,' ELSE '' END + CASE WHEN DaysOfMonth & 524288 <> 0 THEN '20,' ELSE '' END + CASE WHEN DaysOfMonth & 1048576 <> 0 THEN '21,' ELSE '' END + CASE WHEN DaysOfMonth & 2097152 <> 0 THEN '22,' ELSE '' END + CASE WHEN DaysOfMonth & 4194304 <> 0 THEN '23,' ELSE '' END + CASE WHEN DaysOfMonth & 8388608 <> 0 THEN '24,' ELSE '' END + CASE WHEN DaysOfMonth & 16777216 <> 0 THEN '25,' ELSE '' END + CASE WHEN DaysOfMonth & 33554432 <> 0 THEN '26,' ELSE '' END + CASE WHEN DaysOfMonth & 67108864 <> 0 THEN '27,' ELSE '' END + CASE WHEN DaysOfMonth & 134217728 <> 0 THEN '28,' ELSE '' END + CASE WHEN DaysOfMonth & 268435456 <> 0 THEN '29,' ELSE '' END + CASE WHEN DaysOfMonth & 536870912 <> 0 THEN '30,' ELSE '' END + CASE WHEN DaysOfMonth & 1073741824 <> 0 THEN '31,' ELSE '' END AS DaysOfMonthString , CASE WHEN Month = 4095 THEN 'every month, ' ELSE CASE WHEN Month & 1 <> 0 THEN 'Jan, ' ELSE '' END + CASE WHEN Month & 2 <> 0 THEN 'Feb, ' ELSE '' END + CASE WHEN Month & 4 <> 0 THEN 'Mar, ' ELSE '' END + CASE WHEN Month & 8 <> 0 THEN 'Apr, ' ELSE '' END + CASE WHEN Month & 16 <> 0 THEN 'May, ' ELSE '' END + CASE WHEN Month & 32 <> 0 THEN 'Jun, ' ELSE '' END + CASE WHEN Month & 64 <> 0 THEN 'Jul, ' ELSE '' END + CASE WHEN Month & 128 <> 0 THEN 'Aug, ' ELSE '' END + CASE WHEN Month & 256 <> 0 THEN 'Sep, ' ELSE '' END + CASE WHEN Month & 512 <> 0 THEN 'Oct, ' ELSE '' END + CASE WHEN Month & 1024 <> 0 THEN 'Nov, ' ELSE '' END + CASE WHEN Month & 2048 <> 0 THEN 'Dec, ' ELSE '' END END AS MonthString , CASE MonthlyWeek WHEN 1 THEN 'first' WHEN 2 THEN 'second' WHEN 3 THEN 'third' WHEN 4 THEN 'fourth' WHEN 5 THEN 'last' END AS MonthlyWeekString , ' starting ' + CONVERT (VARCHAR, StartDate, 101) + CASE WHEN EndDate IS NOT NULL THEN ' and ending ' + CONVERT (VARCHAR, EndDate, 101) ELSE '' END AS StartEndString , CASE CONVERT(VARCHAR, DATEPART(HOUR, StartDate) % 12) WHEN 0 THEN '12' ELSE CONVERT(VARCHAR, DATEPART(HOUR, StartDate) % 12) END + ':' + CASE WHEN DATEPART(MINUTE, StartDate) < 10 THEN '0' + CONVERT(VARCHAR(2), DATEPART(MINUTE, StartDate)) ELSE CONVERT(VARCHAR(2), DATEPART(MINUTE, StartDate)) END + CASE WHEN DATEPART(HOUR, StartDate) >= 12 THEN ' PM' ELSE ' AM' END AS StartTime FROM Schedule ), SuperEnhancedSchedule AS ( SELECT EnhancedSched.ScheduleID , EnhancedSched.Name , EnhancedSched.StartDate , EnhancedSched.Flags , EnhancedSched.NextRunTime , EnhancedSched.LastRunTime , EnhancedSched.EndDate , EnhancedSched.RecurrenceType , EnhancedSched.MinutesInterval , EnhancedSched.DaysInterval , EnhancedSched.WeeksInterval , EnhancedSched.DaysOfWeek , EnhancedSched.DaysOfMonth , EnhancedSched.Month , EnhancedSched.MonthlyWeek , EnhancedSched.State , EnhancedSched.LastRunStatus , EnhancedSched.ScheduledRunTimeout , EnhancedSched.CreatedById , EnhancedSched.EventType , EnhancedSched.EventData , EnhancedSched.Type , EnhancedSched.ConsistancyCheck , EnhancedSched.Path , -- spec what you need. CASE WHEN RecurrenceType = 1 THEN 'At ' + StartTime + ' on ' + CONVERT(VARCHAR, StartDate, 101) WHEN RecurrenceType = 2 THEN 'Every ' + CONVERT(VARCHAR, ( MinutesInterval / 60 )) + ' hour(s) and ' + CONVERT(VARCHAR, ( MinutesInterval % 60 )) + ' minute(s), ' + 'starting ' + CONVERT (VARCHAR, StartDate, 101) + ' at ' + SUBSTRING(CONVERT(VARCHAR, StartDate, 8), 0, 6) + ' ' + SUBSTRING(CONVERT(VARCHAR, StartDate, 109), 25, 2) + CASE WHEN EndDate IS NOT NULL THEN ' and ending ' + CONVERT (VARCHAR, EndDate, 101) ELSE '' END WHEN RecurrenceType = 3 THEN 'At ' + StartTime + ' every ' + CASE DaysInterval WHEN 1 THEN 'day, ' ELSE CONVERT(VARCHAR, DaysInterval) + ' days, ' END + StartEndString WHEN RecurrenceType = 4 THEN 'At ' + StartTime + ' every ' + CASE WHEN LEN(DaysOfWeekString) > 1 THEN LEFT(DaysOfWeekString, LEN(DaysOfWeekString) - 1) ELSE '' END + ' of every ' + CASE WHEN WeeksInterval = 1 THEN ' week,' ELSE CONVERT(VARCHAR, WeeksInterval) + ' weeks,' END + StartEndString WHEN RecurrenceType = 5 THEN 'At ' + StartTime + ' on day(s) ' + CASE WHEN LEN(DaysOfMonthString) > 1 THEN LEFT(DaysOfMonthString, LEN(DaysOfMonthString) - 1) ELSE '' END + ' of ' + MonthString + StartEndString WHEN RecurrenceType = 6 THEN 'At ' + StartTime + ' on the ' + MonthlyWeekString + ' ' + CASE WHEN LEN(DaysOfWeekString) > 1 THEN LEFT(DaysOfWeekString, LEN(DaysOfWeekString) - 1) ELSE '' END + ' of ' + MonthString + StartEndString ELSE 'At ' + SUBSTRING(CONVERT(VARCHAR, StartDate, 8), 0, 6) + ' ' + SUBSTRING(CONVERT(VARCHAR, StartDate, 109), 25, 2) + StartEndString END ScheduleTextDefinition FROM EnhancedSched ) SELECT * -- This has the same columns as the native [dbo].Schedule table plus a field called "SheduleTextDefinition" -- You can use "SuperEnhancedSchedule" in place of the usual SSRS.Schedule table, joining to subscriptions and such. FROM SuperEnhancedSchedule 

Answer 2

I have a solution for this, since he came up with the report that I am writing.

 create function [dbo].[calendarlist](@Value_in as int,@Type as int) returns varchar(200) as begin /* This code is to work out either the day of the week or the name of a month when given a value Wrriten by S Manson. 31/01/2012 */ declare @strings as varchar(200) declare @Count int if @Type = 2 --Months Begin set @Count =12 end else if @Type = 1 --Days of Week Begin Set @Count = 7 End else --Days of Month Begin Set @Count = 31 End set @strings = '' while @Count<>0 begin if @Value_in>=(select power(2,@count-1)) begin set @Value_in = @Value_in - (select power(2,@count-1)) If @Type=2 Begin set @strings = (SELECT DATENAME(mm, DATEADD(month, @count-1, CAST('2008-01-01' AS datetime)))) + ',' + @strings end else if @Type = 1 begin set @strings = (SELECT DATENAME(dw, DATEADD(day, @count-1, CAST('2012-01-01' AS datetime)))) + ',' + @strings end else begin set @strings = convert(varchar(2),@Count) + ', ' + @strings end end set @count = @count-1 end if right(@strings,1)=',' set @strings = left(@strings,len(@strings)-1) return @strings end 

Answer 3

This post may be very old, but it helped me today! I found 2 subjects that I would like to add to the excellent post above Jamie F for his CTE.

For 31 days there was no entry to be added as part of the EnhancedSched CTE, below, add “DaysOfMonthString” to the end

 + CASE WHEN DaysOfMonth & 1073741824 <> 0 THEN '31,' ELSE '' END 

Also, defining a “StartTime” column with module 12 makes any time starting from zero equal to zero, so a simple English result shows the start time at 0:30 pm for something that is supposed to say 12:30 pm. Replace

 CONVERT(VARCHAR, DATEPART(hour, StartDate) % 12) 

with

 CASE CONVERT(VARCHAR, DATEPART(hour, StartDate) % 12) WHEN 0 THEN '12' ELSE CONVERT(VARCHAR, DATEPART(hour, StartDate) % 12) END 

to get the usual English start time for proper reading.

Thanks to Jamie F's post above, Mega saved my bacon. +1 internet for you good sir.

Sorry for the extra “reply” post, do not submit a comment to vote for Jamie F, great post above.

Answer 4

The actual stored procedure in the MSDB database is called sp_get_schedule_description , which can generate schedule descriptions. I have the code below to enter a schedule identifier and a readable description in the ScheduleInfo table. It works very well, but the user executing this code will need access to the msdb database and execute permissions for the SP to work.

 DECLARE @schedule_description NVARCHAR(255) DECLARE @freq_type INT DECLARE @freq_interval INT DECLARE @freq_subday_type INT DECLARE @freq_subday_interval INT DECLARE @freq_relative_interval INT DECLARE @freq_recurrence_factor INT DECLARE @active_start_date INT DECLARE @active_end_date INT DECLARE @active_start_time INT DECLARE @active_end_time INT DECLARE @schedule_id_as_char VARCHAR(10) DECLARE @scheduleID UNIQUEIDENTIFIER DECLARE @resultCursor CURSOR -- Create cursor using records from job schedules in MSDB database -- SET @resultCursor = CURSOR FOR SELECT d.freq_type ,d.freq_interval ,d.freq_subday_type ,d.freq_subday_interval ,d.freq_relative_interval ,d.freq_recurrence_factor ,d.active_start_date ,d.active_end_date ,d.active_start_time ,d.active_end_time ,a.ScheduleID FROM ReportServer.dbo.Schedule a JOIN msdb.dbo.sysjobs b on CONVERT(NVARCHAR(128),a.ScheduleID) = b.name JOIN msdb.dbo.sysjobschedules c on b.job_id = c.job_id JOIN msdb.dbo.sysschedules d on c.schedule_id = d.schedule_id OPEN @resultCursor -- Fetch first record from cursor -- FETCH NEXT FROM @resultCursor INTO @freq_type ,@freq_interval ,@freq_subday_type ,@freq_subday_interval ,@freq_relative_interval ,@freq_recurrence_factor ,@active_start_date ,@active_end_date ,@active_start_time ,@active_end_time ,@scheduleID -- Loop through cursor and get the rest of the records -- WHILE @@FETCH_STATUS = 0 BEGIN -- Call stored prc in MSDB database to get schedule description -- EXECUTE msdb.dbo.sp_get_schedule_description @freq_type, @freq_interval, @freq_subday_type, @freq_subday_interval, @freq_relative_interval, @freq_recurrence_factor, @active_start_date, @active_end_date, @active_start_time, @active_end_time, @schedule_description OUTPUT -- Insert record to ScheduleInfo table -- INSERT INTO ScheduleInfo VALUES (@scheduleID, @schedule_description) -- Get the next record from the cursor -- FETCH NEXT FROM @resultCursor INTO @freq_type ,@freq_interval ,@freq_subday_type ,@freq_subday_interval ,@freq_relative_interval ,@freq_recurrence_factor ,@active_start_date ,@active_end_date ,@active_start_time ,@active_end_time ,@scheduleID END --Close cursor -- CLOSE @resultCursor DEALLOCATE @resultCursor