Query error with input parameters

[GonzaLinares]

Hi,

I´m having some problems with some Standard and Custom Reports.
Can execute without any error all reports that do not have  multi-select query inputs.
Example: 
Query in report:
                           WHERE  cr.type_symbol  IN $P!{TypesList}
Input control query:
                            SELECT DISTINCT cr.type_symbol as type from cr
Then add as resource the input control.
Have 2 parameters at report:
Name: Types  Class: Collection  Nested: String
Name: TypesList Class: String Expression: "('"+org.apache.commons.lang.StringUtils.join($P{Types}.toArray(),"', '") + "')"

When i execute the report, input control works fine (shows what it´s suppossed to), then when i click "Acept" i get this error:

(Just showing "Caused By" parts)

com.jaspersoft.jasperserver.api.JSException: Error filling report Arguments:...
..... Caused by: net.sf.jasperreports.engine.JRException: Error preparing statement for executing the report query: SELECT cr.ref_num as REF_NUM, cr.open_date as OPEN_DATE, cr.close_date as CLOSE_DATE, cr.status_symbol as STATUS, cr.summary as SUMMARY, cr.impact as IMPACT, cr.priority as PRIORITY, cr.type_symbol as TYPE, request_area_description as AREA_DESC FROM cr WHERE cr.type_symbol in ('Incident', 'Problem', 'Request')
Caused by: java.sql.SQLException: [DataDirect][OpenAccess SDK JDBC Driver][OpenAccess SDK SQL Engine]syntax error, unexpected $end at ( ... N'Incident at com.ddtek.openaccess.ssp.Diagnostic.toSQLException(Unknown Source) ......

Things that i tryed:
- Instead of using $P!{TypesList}, used $X{IN,cr.type_symbol,TypesList}. Got similar error. Instead of "WHERE cr.type_symbol in ('Incident', 'Problem', 'Request')" i get an error like this: "WHERE cr.type_symbol in (?,?,?)"
- Made an input control of class string, executed report and wrote down: ('Incident','Request')  and it worked. Bad thing about this, is that i want to use multi-select query as input control, this way user don´t have to write anything.
- Tryed same report using a different datasource (this datasource point to a "test" DB while the one that fails point to "production") and it worked too. From this what i get is, maybe a JDBC controller problem?

What do you think? What am i doing wrong? Any idea will apreciate.

Thanks,
GL

[joseng62]

So in your Jasper Studio, set the given input control/parameter class as java.util.Collection
Default expression new ArrayList(Arrays.asList()).

In your jasper server you can set given IC type as mutli-select query and add your query. 

IN your SQL in where clause: 
AND $X{IN,YourDBColumn,  yourInputControlName}