May 31, 2006 at 11:37 pm
I have a report that runs on a number of parameter date fields (coverted to string). At present, the report automatically defaults to MAX value of a date. The syntax for that particular element/dataset is
SELECT CONVERT(char(11), MAX(src_date)) AS src_date_cur
FROM tbl_src_date
What I am looking to acheive is that if I was to modify the parameter to allow the user to specifiy a date from that particular field (rather than default to MAX), how could I get the subsequent parameter boxes to requery automatically. The code I use for the other 5 parameter fields is;
SELECT CONVERT(char(11), MAX(src_date)) AS src_date_cur
, CONVERT(char(11), dateadd(mm,-1, MAX(src_date))) AS src_date_pre
FROM tbl_src_date
SELECT CONVERT(char(11), MAX(src_date)) AS src_date_cur
, CONVERT(char(11), dateadd(mm,-2, MAX(src_date))) AS src_date_pre2
FROM tbl_src_date
SELECT CONVERT(char(11), MAX(src_date)) AS src_date_cur
, CONVERT(char(11), dateadd(mm,-3, MAX(src_date))) AS src_date_pre3
FROM tbl_src_date
SELECT CONVERT(char(11), MAX(src_date)) AS src_date_cur
, CONVERT(char(11), dateadd(mm,-4, MAX(src_date))) AS src_date_pre4
FROM tbl_src_date
SELECT CONVERT(char(11), MAX(src_date)) AS src_date_cur
, CONVERT(char(11), dateadd(mm,-5, MAX(src_date))) AS src_date_pre5
FROM tbl_src_date
If it makes a difference I am using SS-RS 2005.
Regards
June 5, 2006 at 8:00 am
This was removed by the editor as SPAM
June 6, 2006 at 5:42 pm
Toni
You need to make the calculated dates 'dependant' on the date entered by the user.
Instead of:
SELECT CONVERT(char(11), MAX(src_date)) AS src_date_cur
, CONVERT(char(11), dateadd(mm,-1, MAX(src_date))) AS src_date_pre
FROM tbl_src_date
Try
SELECT dateadd(mm,-1,@UserEnteredDate) as ....
Make this the default value, and if you wish the only available value. If the user goes back to the first date field and changes it all of the subsequent dates should refresh automatically.
Good luck
Mike
June 6, 2006 at 11:37 pm
Hi Mike
Thanks for this suggestion, I shall give it a try and let you know the result
Regards
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply