Link to home
Start Free TrialLog in
Avatar of WJM
WJMFlag for United States of America

asked on

Reporting Services Date Range Parameter with a twist

I'd like to setup two parameters for a report, the first parameter I'd like to select a "Starting Date" which could be any date, the second parameter will be "Number of days to display", so basically, if someone selects 10/10/2009 for the starting date, and then they choose "8" for the number of days, the report will then only show records that fall between 10/10/2009 - 10/17/2009.  Can someone help me with how I can build my parameters in such a way if possible, thank you.
ASKER CERTIFIED SOLUTION
Avatar of Chris Luttrell
Chris Luttrell
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
First if the above works just fine, you can change the "convert(int,@days)" to just "@days", that was a quirk in my using the wizard to set it up and I forgot to change it back.
Then, in looking over your question again, you say a value of 8 would show records that fall between 10/10/2009 - 10/17/2009 where what I first proposed would result in between 10/10/2009 - 10/18/2009, with the 10/18/2009 date being exactly midnight starting the 18th, so depending on how your dates are stored and if you mean you wanted all records from the 17th but not any that fell exactly on that midnight timestamp, you may want to do either:
WHERE I.InvoiceDate BETWEEN @StartDate AND DATEADD(d,@days - 1, @StartDate)  -- drop it back a day if you store just whole days, no time
or
WHERE I.InvoiceDate >= @StartDate AND I.InvoiceDate  < DATEADD(d,@days, @StartDate)   -- to get all timestamps on the 17th if you store time and need to exclude the midnight changeover