GonzaLinares Posted July 16, 2018 Share Posted July 16, 2018 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 crThen add as resource the input control.Have 2 parameters at report:Name: Types Class: Collection Nested: StringName: 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 Link to comment Share on other sites More sharing options...
joseng62 Posted July 17, 2018 Share Posted July 17, 2018 So in your Jasper Studio, set the given input control/parameter class as java.util.CollectionDefault 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} Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now