Making sense of the ReportServer.dbo.Schedule table

Question

Hi I am trying to make a report that lists all the subscriptions on our report server, the report they are on, the times and days they are run on, and the reccurence. So far I have been able to get a list of the reports and schedules of the reports. I cannot seem to understand what the values and columns in the Schedule table mean.

If anyone could shed some light on how to make sense of these columns and their values, I would really appreciate it. This is the query I have so far.

USE ReportServer;
GO

SELECT Users.UserName
, c.Name AS Report
, Subscriptions.Description
, Schedule.*
/* , Schedule.RecurrenceType
, Schedule.MinutesInterval
, Schedule.DaysInterval
, Schedule.WeeksInterval
, Schedule.DaysOfWeek
, Schedule.DaysOfMonth
, Schedule.[Month]
, Schedule.MonthlyWeek */
FROM [Catalog] AS c
INNER JOIN Subscriptions
ON c.ItemId = Subscriptions.Report_OId
INNER JOIN Users
ON Subscriptions.OwnerId = Users.UserId
INNER JOIN ReportSchedule
ON Subscriptions.SubScriptionId = ReportSchedule.SubScriptionId
INNER JOIN Schedule
ON ReportSchedule.ScheduleId = Schedule.ScheduleId

Thanks,
Chris

Answer 1

Here is a partial answer...

DaysOfWeek relate to the binary setting where:

Sunday is bit 0: Value of 1 Monday is bit 1: Value of 2 Tuesday is bit 2: Value of 4 Wednesday is bit 3: Value of 8 Thursday is bit 4: Value of 16 Friday is bit 5: Value of 32 Saturday is bit 6: Value of 64

So if the report is run every Monday and Wednesday, the DaysOfWeek will be 2 + 8, or 10.

I am currently working on this myself so I will add to this as I discover more.

Answer 2

I have a solution for this as it came up for a report 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