SQL Server Performance Forum – Threads Archive
Auto Population of Parameter Fields
I have a report that uses a number of parameters to search for the correct data. One of these parameters is a fed from a dataset query which lists ‘Site Locations’. Another box uses a query to find the MAX date in a table and auto populates the answer into a parameter. I then have 5 other boxes to complete. What I want to do is by default have the other five boxes populated with the previous months data. For Example :- @vch_site_ref = AC @dt_src_date = 01/01/2006 I want the follwing parameters to be filled in as follows automatically @dt_src_date_pre = 01/12/2005 @dt_src_date_pre1 = 01/11/2005 @dt_src_date_pre2 = 01/10/2005 etc etc. Long term (or now if more convenient) I want the user to be prompted to use default historic data (previous months as listed above) when they select the MAX date in @dt_src_date I would like it to ask the question. Do you wish to use default previous dates (do the above) or if they say ‘No’ they get the option to just specify the dates the require manually. How do I go about this or where can I start to even begin to look at coding this? RegardsToni Chaffin
aka Toni
Have manageed to get the answer to this with SELECT CONVERT(char(11), MAX(src_date)) AS LatestMonth
, CONVERT(char(11), dateadd(mm,-1, MAX(src_date))) AS [LatestMonth-1]
FROM tbl_src_date and then a new dataset with dateadd(mm,-2, blah blah Works fine. Thanks Anyway. Toni Chaffin
aka Toni
]]>