I defined a parameter, multi-choice, using a "like" condition. It always works for the first value of the LOV and ignores the rest of the values chosen.
Is there any way that the "like" condition can be used for more than one parameter value?
In other words, if someone chooses values A,B,C from the LOV, I would like the condition to be interpreted as:
Field like 'A' or Field like 'B' or Field like 'C'.
The reason that I need to use LIKE is that the field contains, e.g. A-1, A-2, A-3, B-1,B-2...
Instead of having the LOV list the variations, it would be much easier for the user to choose A and then get all the A's that exist.
From what I have experienced, sql does not like multiple options for LIKE. An option would be to create a new calculation on the field so it only grabs the first letter of the field. Then your parameter can become a regular '=' or IN condition and would allow the user to get A,B,C values. Could use Index values to show the true list.
I am not sure that I am understanding your suggestion. (By the way, right now we changed the parameter so that it is not multi-choice).
Let's say the list of values would be "permit medical", "permit seniority". "maternity leave"
Examples of the field in the table: "permit medical-1", "permit medical-2","permit seniority-4", "maternity leave-2"
If the user chose "permit medical" I would like to retrieve the first two records above.
Some of the beginning letters are the same and they are different lengths. Would you please give an example to help me understand your explanation?
Take your field that has the values "permit medical-1", "permit medical-2","permit seniority-4", "maternity leave-2" in it. Going to call it Leave_type.. Create a new calculation called Type_of_leave substr(leave_type,1,14). Create a new condion using the type_of_leave field = :leave. You could use a LOV item class to show the real values, if they select Permit Medical, both of the first ones will be selected or any that start with permit medical.
Little clearer ?
What I am still understanding is defining the calculated field as a predefined length which I don't think will work since some of the values are shorter and some longer.
However, you gave me another idea (unless this is what you really meant). I can define the calculated field similar to how I defined the list of values, i.e. take the value of the field until the '-' sign (if it exists). If the actual field value is "permit medical-2", then in the LOV it only says "permit medical". If the original field is "surgery" then the LOV is "surgery. I can do the same thing in the calculated field. Then the condition would be calculated field = LOV.
Thank you! Now my only problem is that I will not be at work for 10 days so I will have to wait until then to try it.
I will let you know how it worked out.
I defined an item in the sql select folder which takes the field without the "-". I see the field in the report and it seems fine. However, when I define the item class for this calculated item, it does not appear in the parameter that I am defining in the report, i.e. there is no LOV icon next to the parameter.
(I defined the field as mandatory). What can I be doing wrong? I have used LOV on calculated items before without a problem.