Thursday, 8 November 2012

Create a data driven subscription for the last working day

The standard scheduling reporting services 2008 r2 does not include the last day of the month.

A workaround is to use sql code in a data driven subscription to create this schedule.

The code is shown below:

I have used a function to strip out the time ...

CREATE  FUNCTION wfDateWithoutTime
 (@datewithtime DATETIME)
 DECLARE @datestring AS VARCHAR(20)
 SET @datestring = CONVERT(VARCHAR(20),@datewithtime,101)

The t sql code is then used in the sql query of the data driven subscription as follows:

with cte as
(select DATEADD(m, DATEDIFF(m, 0, GETDATE())+1, 0) -1 as tday)

select * from cte where tday = (select dbo.wfdatewithouttime(getdate()))

You then need to set the data driven subscription to run on a daily basis. The subscription will then only run on the last day of the month.

No comments:

Post a Comment