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 ...
If your first selection is reading from a View could you change the view such that you have
id=-1 and value=(Any Performance)
in the dataset.
Then you could force that selection to be required and guarantee a value being present and also that your 'Unfiltered' value is also fixed and known.Then your where clause for parameter 2 could be
<<p1>>=-1 or filtered_field=<<p1>>
I must admit I haven't tried this myself but I assume that would work.
Mark
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