To Create a Multi Select List from iReport

-1

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 Approach

Database 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 and

client_list is a varchar type variable, which takes list of comma separated names like '''abc'', ''def''' ..etc

My procedure has the code to parse each of comma separated string and collect the results for that date range for that client.

iReport Part

In 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 respectively

Please check the below screen grab for the details.

 

My first issue is:

I am not sure how to fix the above error msg and

how 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 Server

Next - I tried creating the same report in Jasper Server to see if I will have any luck

I 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 image

Now 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 Message

com.jaspersoft.jasperserver.api.JSException: Error filling report

Error Trace

com.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 more

Error Message

net.sf.jasperreports.engine.JRException: Error executing SQL statement for : TimeInterval_ClientList

Error Trace

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) 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 more

Error Message

java.sql.SQLException: The EXECUTE permission was denied on the object 'usp_timeIntervals_clientList', database 'SupportTracking', schema 'dbo'.

Error Trace

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) 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)

 

 

k_tripura's picture
Joined: Apr 5 2013 - 11:16am
Last seen: 5 years 2 months ago

5 Answers:

0

I fixed it myself, It is all good now

 

I resolved this issue finally ... The reason why it was throwing error is

My iReport Langauge property is set to "Groovy" instead of "Java"

NOT in tools-> Options-> Language- Java

BUT for Language in Activity Report Properties ->Language ->Java (THIS IS THE CHANGE that resolved my issue)

k_tripura's picture
Joined: Apr 5 2013 - 11:16am
Last seen: 5 years 2 months ago
0

Try client parameter with datatype collection.

ajinkya_c's picture
976
Joined: Aug 29 2012 - 6:13am
Last seen: 2 years 6 months ago

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

k_tripura - 6 years 4 months ago
0

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?

 

k_tripura's picture
Joined: Apr 5 2013 - 11:16am
Last seen: 5 years 2 months ago
0

Yes, sure you can put your all changes, also put your where condition in the query.

ajinkya_c's picture
976
Joined: Aug 29 2012 - 6:13am
Last seen: 2 years 6 months ago

Added the error details

k_tripura - 6 years 4 months ago
0

I changed my report completely and now this is where I stand

My 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)

 

 

 

 

 

 

 

 

k_tripura's picture
Joined: Apr 5 2013 - 11:16am
Last seen: 5 years 2 months ago

Hi,

Please provide your where condition from the query which contains $X{} and any information which you think important for troubleshoot.

ajinkya_c - 6 years 4 months ago
Feedback