SQL Server Performance

Auto Population of Parameter Fields

Discussion in 'SQL Server 2005 Reporting Services' started by Toni, Apr 20, 2006.

  1. Toni New Member

    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?


    Toni Chaffin
    aka Toni
  2. Toni New Member

    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

Share This Page