SQL Server Performance

Requery Paramter Fields

Discussion in 'SQL Server Reporting Services' started by Toni, May 31, 2006.

  1. Toni New Member

    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



    Toni Chaffin
    aka Toni
  2. smy New Member

    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.
  3. Toni New Member

    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
  4. smy New Member

    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?
  5. Toni New Member

    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

Share This Page