April 26, 2022 at 12:17 am
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.
April 26, 2022 at 1:54 pm
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.*
from
(
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
"When it comes to report design and development, I have a list of pet peeves a mile wide. You might think the list would be a mile long, but I like to turn it on its side, sharpen the end, and poke people with it." - Me
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply