SA-50715

Summary

Filter List on Grid populated from report grid is blank.

Report definition has been set up with filter criteria that attempts to implement the SQL "IN" operator, as shown in the following example:

WHERE column_name IN (value1, value2, ...)

This was attempted by setting up an "Is Equal" filter condition where the Value was passed in as a parameter and consisted of a comma-separated list of values with manually escaped quotes, as shown in this example:

"\"New\",\"Open\",\"ReOpen\",\"Pending-Documentation\""

The filter list that pops up when clicking the filter image on a column in a grid sourced from report definition is blank and the tracer shows an exception.
The parameters in the query are passed as "<&quot;New&quot" instead of <New>.

Error Messages

There was a problem getting a list: code: -302 SQLState: 22001 Message: DB2 SQL Error: SQLCODE=-302, SQLSTATE=22001, SQLERRMC=null, DRIVER=4.18.60

Steps to Reproduce

1. Create a section containing a repeating grid sourced from a report definition. 
   The filters to the report are passed as a parameter.
   Few parameters have multiple values constructed manually such as, "\"value1\",\"Value2\"".
   The values are treated as in query inputs. 
   The correct results are displayed in the grid.
2. Click the filter image next to column after the grid is populated.
   Items are not displayed in the pop-up window.
   The SQL query was constructed with Equals operator and values are passed as "<&quot;value1&quot;" , "<&quot;Value2&quot;" > instead of In operator and values as <value1>,<value2>.

Root Cause

An issue in the custom application code or rules
This is not the recommended method to implement the SQL IN operator. A text Value List must be used as the Filter value and each value from the comma-separated list added to the Value List.

Here is the excerpt from the Help topic:

A fully qualified property reference to a Value List or Value Group property present on the clipboarad at runtime. The Type of this property must match the Type of the property in the Column field. To compare the Column value with multiple Value List or Value Group properties, separate each property name with a comma.

Resolution

Perform the following local-change:

Create a Value List property to contain values from a list and specify that property as the Value for the filter condition in the report definition.