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
1 Answer:
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}