February 10, 2009 at 5:34 am
I have 2 solutions for you.
1. Jason Selburg has a nice way of creating data driven subscription for sql standard edition
http://www.sqlservercentral.com/articles/Development/2824/
2. I have created 2 parameters and 2 hidden parameters in the report.
FromDate
ToDate
XFromDate (hidden) default null
XToDate (hidden) default null
then I have a sp (sp_getDates) that Receives a parameter
if the value is null, it returns yesterdays date.
else it uses the parameter to determine the date. This could be done in 2 ways:
1. pass m-1 and translate that to dateadd(m,-1,getdate())
2. pass "select dateadd(m,-1,getdate())" as the parameter and use dynamic sql to resolve the date.
option 1 is safer but more work.
Then I set FromDate default value to query and the query that i use is sp_getdate @XFromDate
so when the user opens the report it will use xfromdate = NULL as default because it is hidden and the user cant change it and will return yesterdays date as default for FromDate,
but if you schedule the report you can put values in the hidden parameters.
here is examples of sp_getdate
CREATE PROCEDURE sp_getdate @par varchar(5) = null
as
DECLARE @num int
IF @par IS NULL
Begin
SELECT dateadd(d,-1,getdate())
end
ELSE
BEGIN
SELECT @num = (select cast(substring(@par,2,len(@par)-1)as int))
IF substring(@par,1,1) = 'd' SELECT dateadd(d,@num,getdate())
ELSE
IF substring(@par,1,1) = 'w' SELECT dateadd(week,@num,getdate())
ELSE
IF substring(@par,1,1) = 'm' SELECT dateadd(month,@num,getdate())
ELSE
IF substring(@par,1,1) = 'y' SELECT dateadd(year,@num,getdate())
ELSE
SELECT dateadd(d,-1,getdate())
end
go
Create PROCEDURE sp_getdate2 @par varchar(2000) = null
as
IF @par IS NULL
Begin
SELECT dateadd(d,-1,getdate())
end
ELSE
BEGIN
IF @par LIKE 'select dateadd(%'
EXEC (@par)
ELSE
SELECT dateadd(d,-1,getdate())
END
go
EXEC sp_getdate @PAR='y-1'
EXEC sp_getdate2 @par = 'select dateadd(month,-1,getdate())'
Hope this helps
Viewing post 16 (of 15 total)
You must be logged in to reply to this topic. Login to reply