I have a report where Parameter 1 (an event ID from our event management system reading from a View) is optional and can filter Parameter 2 (perf_no), which is required.
I need to be able to allow the user to select P2 from a full list unfiltered by P1, as the user may not always know the P1 codes.
I'm guessing there's a way of doing a "Where" clause on Parameter 2 that is like an if statement; ie something like "if <<p1>> is null then present the unfiltered Parameter 2 dropdown, else filter by <<p1>>" ?
Thanks all ...
When a parameter is set as optional, the (none) entry is essentially a NULL value in the where clause, so you could do something like this:
Coalesce(<<p1>>,0) = 0 or perf_no = <<p1>>,
So if no selection is made for Parameter 1, every row will resolve to TRUE for 0=0, or if Parameter 1 has a perf_no value, only the filtered rows will appear in the dropdown.
This also assumes that <<p1>> is an integer/number data type. For strings, you can use Coalesce(<<p1>>,'') = ''.
Hope that helps!
Thank you both ...
I went with Ryan's solution as I didnt have to play with the View. Worked like a charm, just had to change the "perf_no" to the actual P1 value.
I heart forums