April 6, 2011 at 12:34 am
HI everyone
i am looking for a solution please. i am trying to schedule a report using SSRS 2008 to run for the previous day. the report i am scheduling has start and end date parameters. is it possible to insert a function into this field so that the report will always choose the previous days values?
Thanks
Ant
April 6, 2011 at 9:25 am
If I understand you are trying to run a daily report with parameters to be filled to pull yesterday's data. Perhaps with a start and end data parameter with both equal to yesterday.
If that is correct, yes it is quite easily done with a data driven subscription. A data driven subscription can query data from a database or just run some T-SQL to populate some variables. In your case you need to come up with some T-SQL to populate a couple of variables (start and end date) and then feed those variables into the value for the report parameters.
Here is an example of the SQL I used for just this situation.
--Declare variables
DECLARE @Begin VARCHAR(11)
DECLARE @End VARCHAR(11)
DECLARE @Today DATETIME
--Get some default values
SET @Today = GetDate()
SET @Begin = DATEADD(D,-1,@Today)
SET @End = DATEADD(D,-1,@Today)
SELECT @Begin AS StartDate
,@End AS EndDate
Then as you progress through the data driven subscription steps, you will be given a window to paste this code into, then a step or two later you will have the opportunity to associate the variables with the report parameters.
April 6, 2011 at 9:38 am
If the report will always run for the previous day, you could set this in the report itself rather than the subscription by setting the date parameters (either within the report or within the individual dataset(s)) using the dateserial function. Setting it at report level means you can refer to the same parameter value throughout your report, whereas setting it at individual dataset level gives you more flexibility but can get confusing if you use the same parameter name within different datasets!
=dateserial(today.year, today.month, today.day-1) = yesterday
Effectively this just takes today's date and then deducts a day
dateserial is a nice way to do stuff when you are working on calendar months as you don't need to worry about how many days are in the month e.g.
=dateserial(today.year, today.month, 1) = first day of current month
=dateserial(today.year, today.month,0) = last day of previous month
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply