I have a multi-select prompt based on Product with values Prod1, Prod2, and Prod3.
However, if Prod2 is selected, I want the filter to add values Prod2a and Prod2b to the filter (along with whatever other Products are selected).
If no Products are selected this is an all condition and I would want Prod1, Prod2, Prod2a, Prod2b. and Prod3.
The filter is currently: [Product Code] in ?parmProduct?
Any thoughts on how best to accomplish this would be greatly appreciated.
Thanks in advance,
Adam.
hi adam_mc,
could you use a substring filter expression?
selection options: Prod1, Prod2, Prod3 - parameter name pProd
filter expression: substring([fieldname1], 1, 5) in ?pProd?
that way, anything that starts with the values in your select list will be selected..
Prod1 as filter value would work for "Prod1a" or "Prod12" or "Prod1asdfrecwefxsa" and so on 8)
Sadly, it's not that simple, and that's because I didn't explain my request better - My Bad!
It's not that I want everything like 'Prod2%" - I really want two additional products related to 'Prod2' that could be named 'abcd' and 'wxyz'.
In effect, I need to scan the created IN Clause for 'Prod2' and then add the products 'abcd' and 'wxyz' to the selections if Prod2 exists.
I hope this makes things clearer and I appreciate the help.
Thanks again,
Adam.
Not sure but try this:
Case
When ( 'Prod2' in ?parmProduct? Then [Product Code] in ( 'Prod2a', 'Prod2b' ) and [Product Code] in ?parmProduct? )
Else ( [Product Code] in ?parmProduct? )
End