Parameter field with multiple values not working
Set up a parameter field with multiple values to be used in the SQL query command and it doesn't work if multiple values are selected, but works fine with one selected value. And yes, the Allow Multiple Values flag is set to True in Options.
I am trying to go from this:
EMPBNFIT.BENEFITPLAN in ('CONSUMER CHOICE','HMO', 'HS HMO','HS NETWORK CHOICE','HS PPO BASIC NH RPN','HS PPO PLUS NH RPN','MFS CONSUMER CHOICE','NETWORK CHOICE','PPO BASIC NH RPN','PPO PLUS NH RPN','WAIVE MEDICAL')
:
WHERE EMPBNFIT.BENEFITPLAN in ('{?MyPlans}')
source to share
What a coincidence; had the same problem this morning. I was able to do a workaround in Crystal by converting an array of multiple parameters to one string and then replacing the section with a IN
comparison INSTR
.
Execute the formula titled ParamFix
with this logic:
REPLACE(JOIN({?MyPlans}, ","), "&", "; ")
In my case, the different values were separated by a character &
, but you can replace it based on what is returned from the tables. Then replace the comparison IN
with:
INSTR({@ParamFix}, EMPBNFIT.BENEFITPLAN) > 0
source to share