Parameter Based on value in field

  • I am trying to add a parameter to a report that has two date fields comfirmeddate and requesteddate. I want to check if there is a value in confirmeddate and if there is add a date range parameter on that field, if there is not a value in the confirmeddate field I want to add the date range parameter to the requesteddate field. I am nor sure of the best way to approach this. I have tried using IIF and case but I cant get it to work. Any suggestions of the best way to approach this would be appreciated.

  • I think I can help, but I have a few questions...

    Regarding "range", are you thinking about two parameters (start date & end date), and/or are you envisioning drop down menu(s) of permissible dates, or something else?  Or maybe I'm misinterpreting - let me rephrase as two questions...

    1.  Are you wanting to pass a single date value to the query or a startdate and enddate?

    2.  Do you need to restrict the end user to only entering certain date values, or can they enter whatever they like?

    This may not at all be addressing your issue, but I have had instances where I need a report to run for a range of dates where the date are confirmed dates OR run for a range of dates for requested dates.  In these situations I will usually have three parameters:  Date Type (drop down values are 'Confirmed', 'Requested'), StartDate, and EndDate.  Then I will pass all three parameters to the query and have the query set up like this:

    select t2.*
    select t.*
    ,(CASE WHEN @DateType = 'Confirmed' THEN t.ConfirmedDate ELSE t.RequestedDate END) as CustomDate
    from tables t
    ) t2
    where t2.CustomDate between @StartDate and @EndDate



    "The Bible tells us to love our neighbors, and also to love our enemies; probably because they are generally the same people." G. K. Chesterton

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply