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)
RETURNS SMALLDATETIME
AS
BEGIN
 DECLARE @datestring AS VARCHAR(20)
 SET @datestring = CONVERT(VARCHAR(20),@datewithtime,101)
 RETURN CONVERT(SMALLDATETIME, @datestring)
END

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.