I have requirement wherein there is field in the report filter which are multiselect.
And this is run against Requests which also have multiple values in a field. Currently the report give records for multiple select values, but for those request which contain mutiple values in a fields are excluded in the records list.
Eg if Text is a field haviing A, B for request 111. and text2 = A request 222, Text3 = B request 333
when A, B are entered in the report fiter, the report only list out 222, 333, leaving out 111.
The problem with Multi-Selecti fields as filters for Multi-Select fields on Requests is one of how the data is stored for each and how you implement the WHERE clause. A typical use of a MS-ACL file against a single value Request field would be
AND rd1.PARAMETERx in ([P.MS_FILTER.TO_STRING])
so that you can compare the value against all of the values in the filter. With a MS field on the Request form, the data is stored in the DB as 'Val1#@#Val2#@#Val3', so that when the WHERE clause looks at the column in the database against the values in the field, it will not match. From a pure SQL standpoint, I don't know that there is a simple answer. One possible solution would be to create a function that you could pass the DB column and the filter values into and have it return a 1 for a match and a 0 for no match (or any other return values that want). I don't have such a function, myself, as I have tried to heed the warning that PPM displays when you create a MS Filter on a Report that you need to take special precautions for a MS Filter on a MS Field.
BUS_AREA is the token received from the Report Type filter. Then we use expresion language to put the string received in the correct SQL format. The red text is part of the where clause in the SQL statement.