How to use sql substring function in a $X{IN} expression?

Question

I have a query at report that not working after adding substring at $X{} expression.

SELECT t1.grn
FROM qc_master t1
WHERE $X{IN, t1.i_rep_no.substring(0,1), type}

I am getting the error Parameter 1 does not exist during preview at iReport/JSS. How to fix this?

Answer 1

Duplicating the answer from Jaspersoft Community:

You cannot use an SQL function inside a JasperReports $X clause function as per: http://jasperreports.sourceforge.net/sample.reference/query/index.html#clause_functions

Instead, you could have something like this in your SQL:

and substr(t1.i_rep_no,1,1) IN ($P!{type})

with parameter type as a java.lang.String with comma separated values, or as anything that resolves to that.