k_tripura Posted April 19, 2013 Share Posted April 19, 2013 Hello All,My Goal Create a iReport which takes begin_date, end_date and selected client names as input and returns the results.(I am trying to create a multi select list in iReport for the first time and facing difficulty in creating it.)Steps of ApproachDatabase part In Stored Procedure:I created a stored procedure in my Ms Sql database, which takes 3 parameters as input. They are begin_date, end_date and client_list.begin_date, end_date are simple datatime variables andclient_list is a varchar type variable, which takes list of comma separated names like '''abc'', ''def''' ..etcMy procedure has the code to parse each of comma separated string and collect the results for that date range for that client.iReport PartIn my iReport, I opened "Report Query" window and selected the 'Query Language' as plsql and created 3 New parameters (with same names as my stored proc.. just to be on the safe side)begin_date, end_date and client_list respectivelyPlease check the below screen grab for the details. My first issue is:I am not sure how to fix the above error msg andhow to give an option to create and select multiple client names.But - If I select a java.lang.string type I have no errors and my report seems to be working fine for default values, but not for the values that I give to client_list parameter at run time.My second issue is:How do I create a multi select list in iReport. In Jasper ServerNext - I tried creating the same report in Jasper Server to see if I will have any luckI uploaded the created iReport to my Jasper Server for Creating a new report.In Jasper admin, and created 3 Input Controls ( I am using exactly same names as my procedures input parameters)Please check the below screen grab2 I defined Client Input control type as "Mult-Select List of Values" and defined my own list Please check the below imageNow When I try to run this report, in Jasper Server ( or from iReport), I am getting the popup window as I expected, please see the below image But when I run this by selecting the required parameters, I am getting big big errors as follow, may be because, my iReport did not complie properly, I am not sure how to create this kind of list in iReport or make this working. Please suggest. This is my first report with multi select list. The server has encountered an error. Please excuse the inconvenience.Error Messagecom.jaspersoft.jasperserver.api.JSException: Error filling reportError Tracecom.jaspersoft.jasperserver.api.JSException: Error filling report at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$FillResultListener.reportFillError(EngineServiceImpl.java:1178) at net.sf.jasperreports.engine.fill.BaseFillHandle.notifyError(BaseFillHandle.java:211) at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFiller.run(BaseFillHandle.java:135) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$SynchronousExecutor.execute(EngineServiceImpl.java:859) at net.sf.jasperreports.engine.fill.BaseFillHandle.startFill(BaseFillHandle.java:165) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$AsynchronousReportFiller.fillReport(EngineServiceImpl.java:821) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport(EngineServiceImpl.java:1622) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runReport(EngineServiceImpl.java:1005) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportRunnable.run(EngineServiceImpl.java:881) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) at java.lang.Thread.run(Thread.java:722) Caused by: net.sf.jasperreports.engine.JRException: Error executing SQL statement for : TimeInterval_ClientList at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:240) at com.jaspersoft.jasperserver.api.engine.jasperreports.util.JRTimezoneJdbcQueryExecuter.createDatasource(JRTimezoneJdbcQueryExecuter.java:168) at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1086) at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:667) at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:1258) at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:877) at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFiller.run(BaseFillHandle.java:120) ... 9 more Caused by: java.sql.SQLException: The EXECUTE permission was denied on the object 'usp_timeIntervals_clientList', database 'SupportTracking', schema 'dbo'. at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372) at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2893) at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2335) at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:638) at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:505) at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:1029) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92) at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:233) ... 15 moreError Messagenet.sf.jasperreports.engine.JRException: Error executing SQL statement for : TimeInterval_ClientListError Tracenet.sf.jasperreports.engine.JRException: Error executing SQL statement for : TimeInterval_ClientList at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:240) at com.jaspersoft.jasperserver.api.engine.jasperreports.util.JRTimezoneJdbcQueryExecuter.createDatasource(JRTimezoneJdbcQueryExecuter.java:168) at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1086) at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:667) at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:1258) at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:877) at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFiller.run(BaseFillHandle.java:120) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$SynchronousExecutor.execute(EngineServiceImpl.java:859) at net.sf.jasperreports.engine.fill.BaseFillHandle.startFill(BaseFillHandle.java:165) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$AsynchronousReportFiller.fillReport(EngineServiceImpl.java:821) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport(EngineServiceImpl.java:1622) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runReport(EngineServiceImpl.java:1005) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportRunnable.run(EngineServiceImpl.java:881) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) at java.lang.Thread.run(Thread.java:722) Caused by: java.sql.SQLException: The EXECUTE permission was denied on the object 'usp_timeIntervals_clientList', database 'SupportTracking', schema 'dbo'. at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372) at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2893) at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2335) at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:638) at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:505) at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:1029) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92) at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:233) ... 15 moreError Messagejava.sql.SQLException: The EXECUTE permission was denied on the object 'usp_timeIntervals_clientList', database 'SupportTracking', schema 'dbo'.Error Tracejava.sql.SQLException: The EXECUTE permission was denied on the object 'usp_timeIntervals_clientList', database 'SupportTracking', schema 'dbo'. at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372) at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2893) at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2335) at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:638) at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:505) at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:1029) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92) at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:233) at com.jaspersoft.jasperserver.api.engine.jasperreports.util.JRTimezoneJdbcQueryExecuter.createDatasource(JRTimezoneJdbcQueryExecuter.java:168) at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1086) at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:667) at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:1258) at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:877) at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFiller.run(BaseFillHandle.java:120) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$SynchronousExecutor.execute(EngineServiceImpl.java:859) at net.sf.jasperreports.engine.fill.BaseFillHandle.startFill(BaseFillHandle.java:165) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$AsynchronousReportFiller.fillReport(EngineServiceImpl.java:821) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport(EngineServiceImpl.java:1622) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runReport(EngineServiceImpl.java:1005) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportRunnable.run(EngineServiceImpl.java:881) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) at java.lang.Thread.run(Thread.java:722) Link to comment Share on other sites More sharing options...
ajinkya_c Posted April 30, 2013 Share Posted April 30, 2013 Try client parameter with datatype collection. Link to comment Share on other sites More sharing options...
k_tripura Posted May 1, 2013 Author Share Posted May 1, 2013 I tried it all ready and I did some more changes too. still I am not able to execute the query in iReport using $X{} format Link to comment Share on other sites More sharing options...
k_tripura Posted May 1, 2013 Author Share Posted May 1, 2013 I tried it all ready and I did some more changes too. still I am not able to execute the query in iReport using $X{} format I can post that changes here, can you take a look at it? Link to comment Share on other sites More sharing options...
ajinkya_c Posted May 2, 2013 Share Posted May 2, 2013 Yes, sure you can put your all changes, also put your where condition in the query. Link to comment Share on other sites More sharing options...
k_tripura Posted May 2, 2013 Author Share Posted May 2, 2013 I changed my report completely and now this is where I standMy client_list variable in iReport is a collection and is using something like this new ArrayList(Arrays.asList(new String[] {"test"}))I changed my client_list variable name in Jasper Server to a "Multi-Select Query" and added the query and it is populating all the entries from the database (which is a good sign)Now when I run the report, I am able select the begin_date, end_date and multiple clients But When I click OK, I am getting the below error The server has encountered an error. Please excuse the inconvenience. Error Message net.sf.jasperreports.engine.JRRuntimeException: No clause function for id IN found Error Trace net.sf.jasperreports.engine.JRRuntimeException: No clause function for id IN found at net.sf.jasperreports.engine.query.JRAbstractQueryExecuter.resolveFunction(JRAbstractQueryExecuter.java:288) at net.sf.jasperreports.engine.query.JRAbstractQueryExecuter.appendClauseChunk(JRAbstractQueryExecuter.java:531) at net.sf.jasperreports.engine.query.JRAbstractQueryExecuter.appendQueryChunk(JRAbstractQueryExecuter.java:383) at net.sf.jasperreports.engine.query.JRAbstractQueryExecuter.parseQuery(JRAbstractQueryExecuter.java:358) at com.jaspersoft.jrx.query.PlSqlQueryExecuter.(PlSqlQueryExecuter.java:96) at com.jaspersoft.jrx.query.PlSqlQueryExecuterFactory.createQueryExecuter(PlSqlQueryExecuterFactory.java:108) at net.sf.jasperreports.engine.util.JRQueryExecuterUtils$WrappingQueryExecuterFactory.createQueryExecuter(JRQueryExecuterUtils.java:155) at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1083) at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:667) at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:1258) at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:877) at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFiller.run(BaseFillHandle.java:120) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$SynchronousExecutor.execute(EngineServiceImpl.java:859) at net.sf.jasperreports.engine.fill.BaseFillHandle.startFill(BaseFillHandle.java:165) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$AsynchronousReportFiller.fillReport(EngineServiceImpl.java:821) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport(EngineServiceImpl.java:1622) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runReport(EngineServiceImpl.java:1005) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportRunnable.run(EngineServiceImpl.java:881) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) at java.lang.Thread.run(Thread.java:722) I am not able to understand this error and what they mean? (I can post the screen shots if the can help to give better picture) Link to comment Share on other sites More sharing options...
k_tripura Posted May 2, 2013 Author Share Posted May 2, 2013 Added the error details Link to comment Share on other sites More sharing options...
ajinkya_c Posted May 3, 2013 Share Posted May 3, 2013 Hi, Please provide your where condition from the query which contains $X{} and any information which you think important for troubleshoot. Link to comment Share on other sites More sharing options...
Solution k_tripura Posted May 9, 2013 Author Solution Share Posted May 9, 2013 I fixed it myself, It is all good now I resolved this issue finally ... The reason why it was throwing error isMy iReport Langauge property is set to "Groovy" instead of "Java"NOT in tools-> Options-> Language- JavaBUT for Language in Activity Report Properties ->Language ->Java (THIS IS THE CHANGE that resolved my issue) 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