Can I separately evaluate each value in a multiselect prompt?

Question

Hi,

I'd like to be able to type multiple parts of names into a prompt and return records where the field contains any parts of those names. For example, if I type "Jenny;Sydney" into the prompt, I would like the report to return both "Jenny Chen" and "Sydney Smith".

Currently I have the prompt and report working if only 1 name or part of a name is entered into the prompt. However, if I select more than 1 value like "Jenny;Sydney Smith", the prompt passes the string to the presentation variable as "Jenny,Sydney Smith". Is there a way to evaluate each value separately, so it will evaluate "Jenny" and then "Sydney Smith" ?

My current filter logic:

case when "Approver"."Approver Name" like '%' || upper('@{reviewerAssignedTo}['@']{%}') || '%' OR upper('@{reviewerAssignedTo}['@']{%}') LIKE '%' || "Approver"."Approver Name" || '%' then 1 else 0 end

reviewerAssignedTo is the presentation variable set by the prompt.

Andrew Fomin. · Answer

Just because it's Friday.

EVALUATE_PREDICATE('REGEXP_LIKE(%1,%2)', "Approver"."Approver Name", '.*'||replace('@{reviewerAssignedTo}{}',',','.*|')||'.*')

But it uses EVALUATE - bad practice, REGEXP - slow.

In real life, I'd use "is LIKE (pattern match)" operator.

Gianni Ceresa · Answer

Not really ...

What you ask is doable with many prompts (like 3-4) and you can enter a name into each one, but just one per prompt.

Your condition isn't one that you can apply on a list of elements, so you ask to be able to split the value you enter based on the " ; " and then build dynamically a filter condition joined by a OR for each one of the values. That's asking a bit too much