Understanding the ReportServer.dbo.Schedule table

Question

Hey. I am trying to make a report that lists all the subscriptions on our report server, the report in which they are stored, the time and days when they are executed, and the repetition. So far I have managed to get a list of reports and report schedules. I can't figure out what the values ​​and columns in the Schedule table mean.

If anyone could shed light on how to understand these columns and their values, I would greatly appreciate it. This is the request that I have so far.

USE ReportServer,
GO

SELECT Users.UserName
  , c.Name AS Report
  , Subscriptions. , Schedule. *
/ *, Schedule.RecurrenceType
  , Schedule.MinutesInterval
  , Schedule.DaysInterval
  , Schedule.WeeksInterval
  , Schedule.DaysOfWeek
  , Schedule.DaysOfMonth
  , Schedule. [Month]
  , Schedule.MonthlyWeek * /
the FROM [Product] the AS c
  an INNER a JOIN Subscriptions
      the ON c.ItemId = Subscriptions.Report_OId
  an INNER a JOIN Members
      ON Subscriptions.OwnerId = Members .UserId
  an INNER a JOIN ReportSchedule
      the ON Subscriptions.SubScriptionId = ReportSchedule.SubScriptionId
  INTERNAL CONNECTION schedule
      ON ReportSchedule.ScheduleId = Schedule.ScheduleId

Thanks
Chris

Answer 1

Here is a partial answer ...

DaysOfWeek refers to binary tuning, where:

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

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

I am currently working on this, so I will add to this as I learn more.

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