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;
GOSE开发者_StackOverflow社区LECT 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.ScheduleIdThanks,
ChrisHere 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.
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
精彩评论