September 8, 2010 at 2:48 am
Hi there,
I am currently trying to figure out how to meet a parameter related requirement. Here are the details:
My report has two datetime parameters - start_date and end_date.
Each of these has a default value. Both default values come from a query. The end_date value is 18 weeks after the start_date.
What I need to acheive is a way to keep the date range between the parameters at 18 weeks if either is altered.
e.g. If start_date is moved forward, end_date also moves to stay 18 weeks ahead of it. Likewise if end_date is moved backwards, start_date moves back to stay 18 weeks before it.
I am aware of, and have used, cascading parameters but is there a way to make parameters mutually dependant like I need?
TIA,
Chris
September 8, 2010 at 12:42 pm
If your end date is always 18 weeks after your start date, why do you need an end date parameter at all? Drop your end date parameter, go to your SQL and change it to something like
date BETWEEN @Start AND DATEADD(W,18,@Start)
September 14, 2010 at 3:29 am
Unfortunately, it's not quite that simple.
I also need to be able to show periods of less than 18 weeks so a start and end date are required to allow the user to select a period between the two dates.
The difficult bit is then moving either parameter in the right direction when a period of more than 18 weeks is selected.
September 14, 2010 at 8:08 am
So if the end date is the parameter you are trying to keep within 18 weeks, then go to the Available Values of the end date parameter and build a query to constrain the end date.
September 15, 2010 at 11:10 am
chris.king (9/14/2010)
Unfortunately, it's not quite that simple.I also need to be able to show periods of less than 18 weeks so a start and end date are required to allow the user to select a period between the two dates.
The difficult bit is then moving either parameter in the right direction when a period of more than 18 weeks is selected.
You can also do it this way.
Visible parameter @FromDate
Visible parameter @DaysOut, @WeeksOut, or whatever you want.
Then create a hidden parameter, @ToDate and set it's default value = AddDate("D",@DaysOut,@FromDate)
for example. Then your @ToDate is set depending on how many days or weeks or what have you is entered in the second parameter.
September 15, 2010 at 11:14 am
You can even have 3 visible parameters.
@FromDate
@ToDate
@DaysOut
and allow blank values. Then, in your query, you can check which parameters the user filled in
If Len(Ltrim(RTrim(@ToDate))) = 0
Begin
xxxxxx
End
Else
If... you get the picture.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply