SQL Server Performance Forum – Threads Archive
Requery Paramter Fields
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_curFROM 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 Toni Chaffin
aka Toni
What you mean is that src_date is to be input by users instead of ‘hard coded’ by query? To get user’s input through parameter,
e.g.
SELECT CONVERT(char(11), @sdate) AS src_date_cur
, CONVERT(char(11), dateadd(mm,-5, @sdate)) AS src_date_pre5
FROM tbl_src_date where the sdate is the name of the report parameter.
The user will select a value in parameter box one which will be based on a query, it will default to the MAX value but the user can select a different value from the box should they choose to, and then once that selection has been made, the other 5 parameter date boxes will requery based upon the users selection in parameter box one. Kind of like an OnChange/click in Access VB. Any clearer?
Toni Chaffin
aka Toni
ok..from what i understand, Create a report parameter e.g. sdate (this one will be a dropdown listbox with values based on a query..
e.g
SELECT CONVERT(char(11), src_date) AS src_date_cur
FROM tbl_src_date
which you create on the Data tab as one of the dataset. (e.g. Dataset 1) Then back on the report parameters properties, after created sdate, there is an option below:
Non-queried/From query
Select From query and choose the dataset (e.g. Dataset 1) that you created
Select the value that you need to use..in this case, it will be src_date_cur Let the users choose a date from the databinded dropdown listbox..
Once they selected on a date, Supposingly, these are the queries (datasets) that you want to fix in the selected date, change the src_date to @sdate (the parameter name of the dropdown listbox) SELECT CONVERT(char(11), @sdate) AS src_date_cur
, CONVERT(char(11), dateadd(mm,-1, @sdate)) AS src_date_pre
FROM tbl_src_date SELECT CONVERT(char(11), @sdate) AS src_date_cur
, CONVERT(char(11), dateadd(mm,-2, @sdate)) AS src_date_pre2
FROM tbl_src_date SELECT CONVERT(char(11), @sdate) AS src_date_cur
, CONVERT(char(11), dateadd(mm,-3, @sdate)) AS src_date_pre3
FROM tbl_src_date SELECT CONVERT(char(11), @sdate) AS src_date_cur
, CONVERT(char(11), dateadd(mm,-4, @sdate)) AS src_date_pre4
FROM tbl_src_date SELECT CONVERT(char(11), @sdate) AS src_date_cur
, CONVERT(char(11), dateadd(mm,-5, @sdate)) AS src_date_pre5
FROM tbl_src_date Did I answer to your question?
smy, I am not sure if you have answered it or not, either I have misunderstood what you have said or I am just so darn tired that I can’t think straight. I will re-read what you have put and work out what you are telling me. Thanks smy
Toni Chaffin
aka Toni
]]>