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