unable to run the report in Jasper Server

[shoppingtime]

Hi Team,

   I have a strange issue. I am running a report in jasperserver which is running for some time and giving below error some time. The sql used for the report is running fine in SQL Workbench. Can someone please help in resolving this issue.

 

-Thanks

 

The server has encountered an error. Please excuse the inconveience

Error Message

 

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:1233) 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:880) 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:836) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport(EngineServiceImpl.java:1702) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runWithDataSource(EngineServiceImpl.java:1057) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runReport(EngineServiceImpl.java:991) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportRunnable.run(EngineServiceImpl.java:902) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1146) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) at java.lang.Thread.run(Thread.java:701) Caused by: net.sf.jasperreports.engine.JRException: Error executing SQL statement for : Comparison Graph at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:240) at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1112) at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:689) at net.sf.jasperreports.engine.fill.JRFillDatasetRun.evaluate(JRFillDatasetRun.java:204) at net.sf.jasperreports.engine.fill.JRFillElementDataset.evaluateDatasetRun(JRFillElementDataset.java:231) at net.sf.jasperreports.engine.fill.JRFillChart.evaluateDatasetRun(JRFillChart.java:1406) at net.sf.jasperreports.engine.fill.JRFillChart.evaluateChart(JRFillChart.java:832) at net.sf.jasperreports.engine.fill.JRFillChart.createMultiAxisChart(JRFillChart.java:1149) at net.sf.jasperreports.engine.fill.JRFillChart.evaluateChart(JRFillChart.java:839) at net.sf.jasperreports.engine.fill.JRFillChart.evaluateRenderer(JRFillChart.java:808) at net.sf.jasperreports.engine.fill.JRFillChart.resolveElement(JRFillChart.java:1384) at net.sf.jasperreports.engine.fill.JRFillElement.performDelayedEvaluation(JRFillElement.java:983) at net.sf.jasperreports.engine.fill.JRFillElement.resolveElement(JRFillElement.java:1085) at net.sf.jasperreports.engine.fill.ElementEvaluationAction.execute(JRBaseFiller.java:2423) at net.sf.jasperreports.engine.fill.JRBaseFiller.resolveBoundElements(JRBaseFiller.java:1609) at net.sf.jasperreports.engine.fill.JRBaseFiller.resolveReportBoundElements(JRBaseFiller.java:1636) at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillSummary(JRVerticalFiller.java:1077) at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReportEnd(JRVerticalFiller.java:319) at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReport(JRVerticalFiller.java:158) at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:932) at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFiller.run(BaseFillHandle.java:120) ... 10 more Caused by: org.postgresql.util.PSQLException: ERROR: ORDER/GROUP BY expression not found in targetlist at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:302) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96) at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:233) ... 30 more

 

Error Message

 

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

 

Error Trace

 

net.sf.jasperreports.engine.JRException: Error executing SQL statement for : Comparison Graph at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:240) at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1112) at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:689) at net.sf.jasperreports.engine.fill.JRFillDatasetRun.evaluate(JRFillDatasetRun.java:204) at net.sf.jasperreports.engine.fill.JRFillElementDataset.evaluateDatasetRun(JRFillElementDataset.java:231) at net.sf.jasperreports.engine.fill.JRFillChart.evaluateDatasetRun(JRFillChart.java:1406) at net.sf.jasperreports.engine.fill.JRFillChart.evaluateChart(JRFillChart.java:832) at net.sf.jasperreports.engine.fill.JRFillChart.createMultiAxisChart(JRFillChart.java:1149) at net.sf.jasperreports.engine.fill.JRFillChart.evaluateChart(JRFillChart.java:839) at net.sf.jasperreports.engine.fill.JRFillChart.evaluateRenderer(JRFillChart.java:808) at net.sf.jasperreports.engine.fill.JRFillChart.resolveElement(JRFillChart.java:1384) at net.sf.jasperreports.engine.fill.JRFillElement.performDelayedEvaluation(JRFillElement.java:983) at net.sf.jasperreports.engine.fill.JRFillElement.resolveElement(JRFillElement.java:1085) at net.sf.jasperreports.engine.fill.ElementEvaluationAction.execute(JRBaseFiller.java:2423) at net.sf.jasperreports.engine.fill.JRBaseFiller.resolveBoundElements(JRBaseFiller.java:1609) at net.sf.jasperreports.engine.fill.JRBaseFiller.resolveReportBoundElements(JRBaseFiller.java:1636) at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillSummary(JRVerticalFiller.java:1077) at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReportEnd(JRVerticalFiller.java:319) at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReport(JRVerticalFiller.java:158) at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:932) 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:880) 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:836) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport(EngineServiceImpl.java:1702) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runWithDataSource(EngineServiceImpl.java:1057) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runReport(EngineServiceImpl.java:991) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportRunnable.run(EngineServiceImpl.java:902) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1146) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) at java.lang.Thread.run(Thread.java:701) Caused by: org.postgresql.util.PSQLException: ERROR: ORDER/GROUP BY expression not found in targetlist at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:302) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96) at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:233) ... 30 more

 

Error Message

 

org.postgresql.util.PSQLException: ERROR: ORDER/GROUP BY expression not found in targetlist

 

Error Trace

 

org.postgresql.util.PSQLException: ERROR: ORDER/GROUP BY expression not found in targetlist at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:302) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96) at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:233) at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1112) at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:689) at net.sf.jasperreports.engine.fill.JRFillDatasetRun.evaluate(JRFillDatasetRun.java:204) at net.sf.jasperreports.engine.fill.JRFillElementDataset.evaluateDatasetRun(JRFillElementDataset.java:231) at net.sf.jasperreports.engine.fill.JRFillChart.evaluateDatasetRun(JRFillChart.java:1406) at net.sf.jasperreports.engine.fill.JRFillChart.evaluateChart(JRFillChart.java:832) at net.sf.jasperreports.engine.fill.JRFillChart.createMultiAxisChart(JRFillChart.java:1149) at net.sf.jasperreports.engine.fill.JRFillChart.evaluateChart(JRFillChart.java:839) at net.sf.jasperreports.engine.fill.JRFillChart.evaluateRenderer(JRFillChart.java:808) at net.sf.jasperreports.engine.fill.JRFillChart.resolveElement(JRFillChart.java:1384) at net.sf.jasperreports.engine.fill.JRFillElement.performDelayedEvaluation(JRFillElement.java:983) at net.sf.jasperreports.engine.fill.JRFillElement.resolveElement(JRFillElement.java:1085) at net.sf.jasperreports.engine.fill.ElementEvaluationAction.execute(JRBaseFiller.java:2423) at net.sf.jasperreports.engine.fill.JRBaseFiller.resolveBoundElements(JRBaseFiller.java:1609) at net.sf.jasperreports.engine.fill.JRBaseFiller.resolveReportBoundElements(JRBaseFiller.java:1636) at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillSummary(JRVerticalFiller.java:1077) at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReportEnd(JRVerticalFiller.java:319) at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReport(JRVerticalFiller.java:158) at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:932) 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:880) 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:836) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport(EngineServiceImpl.java:1702) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runWithDataSource(EngineServiceImpl.java:1057) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runReport(EngineServiceImpl.java:991) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportRunnable.run(EngineServiceImpl.java:902) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1146) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) at java.lang.Thread.run(Thread.java:701) 

[mgeise]

Is it possible that you are looking at the wrong query?  It seems to be complaining about the query for the "Comparison Graph."  Potentially this is a query that is generated from a subreport or a chart that you added separate from the main report query.  You might try turning the logging up on the server or even try catching the query in the DB to see what the actual query is.  It is definitely a problem with the query that is getting sent to the DB.

Poking around the internet a bit, I found that this has been seen and the response was:

I got this error when I was using PostgreSQL to query an Amazon Redshift database. To fix it, I had to change the RANK function to this Redshift-specific function:

SUM (1) OVER ( PARTITION BY field1 ORDER BY field2 ROWS UNBOUNDED PRECEDING)

Another item that I found that shows why this might happen is this:

In the result, the database returns four records. If we want to get only one entry for every name, we can use DISTINCT:

 SELECT DISTINCT name FROM customer INTERSECT SELECT DISTINCT name FROM sales;

 

In the example, we have to use DISTINCT in both SELECT statements; otherwise, an error is displayed:

SELECT DISTINCT name FROM customer INTERSECT SELECT name FROM sales

ERROR: get_sortgroupclause_tle: ORDER/GROUP BY expression not found in targetlist