I have run report for few more filter, then i got Exception with Query failed error.
com.jaspersoft.jasperserver.api.JSException: Error filling report
Arguments:
at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$FillResultListener.reportFillError(EngineServiceImpl.java:1295)
at net.sf.jasperreports.engine.fill.BaseFillHandle.notifyError(BaseFillHandle.java:210)
at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFill.run(BaseFillHandle.java:134)
at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$SynchronousExecutor.execute(EngineServiceImpl.java:923)
at net.sf.jasperreports.engine.fill.BaseFillHandle.startFill(BaseFillHandle.java:164)
at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport(EngineServiceImpl.java:1848)
at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$SynchronousReportFiller.fillReport(EngineServiceImpl.java:827)
at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport(EngineServiceImpl.java:1775)
at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runWithDataSource(EngineServiceImpl.java:1119)
at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runReport(EngineServiceImpl.java:1048)
at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportRunnable.run(EngineServiceImpl.java:945)
at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$SynchronousExecutor.execute(EngineServiceImpl.java:923)
at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport(EngineServiceImpl.java:640)
at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.executeReportUnitRequest(EngineServiceImpl.java:1981)
at com.jaspersoft.jasperserver.api.engine.jasperreports.domain.impl.ReportUnitRequest.execute(ReportUnitRequest.java:67)
at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.execute(EngineServiceImpl.java:526)
at sun.reflect.GeneratedMethodAccessor670.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:80)
at com.jaspersoft.ji.license.LicenseCheckAspect.runReport(LicenseCheckAspect.java:139)
at sun.reflect.GeneratedMethodAccessor669.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:621)
at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:610)
at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:65)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:161)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:91)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
at com.sun.proxy.$Proxy65.execute(Unknown Source)
at com.jaspersoft.jasperserver.api.engine.scheduling.quartz.ReportExecutionJob.runReport(ReportExecutionJob.java:784)
at com.jaspersoft.jasperserver.api.engine.scheduling.quartz.ReportExecutionJob.executeReport(ReportExecutionJob.java:751)
at com.jaspersoft.ji.report.options.engine.ReportOptionsExecutionJob.executeReport(ReportOptionsExecutionJob.java:138)
at com.jaspersoft.jasperserver.api.engine.scheduling.quartz.ReportExecutionJob.executeReport(ReportExecutionJob.java:660)
at com.jaspersoft.jasperserver.api.engine.scheduling.quartz.ReportExecutionJob.executeAndSendReport(ReportExecutionJob.java:487)
at com.jaspersoft.jasperserver.api.engine.scheduling.quartz.ReportExecutionJob.execute(ReportExecutionJob.java:246)
at com.jaspersoft.ji.report.options.engine.ReportOptionsExecutionJob.execute(ReportOptionsExecutionJob.java:47)
at org.quartz.core.JobRunShell.run(JobRunShell.java:213)
at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:557)
Caused by: net.sf.jasperreports.engine.JRException: Error preparing statement for executing the report query:
SELECT r.employment_type_key,
rln.employment_type,
SUM(CASE WHEN ? = 'FTE' AND c.period_fte <> 0
THEN pf.alloc_hours/c.period_fte ELSE pf.alloc_hours END) demand
FROM dwh_inv_team t
INNER JOIN dwh_inv_investment i ON t.investment_key = i.investment_key
INNER JOIN dwh_res_resource r ON t.resource_key = r.resource_key
INNER JOIN dwh_res_resource_ln rln ON r.resource_key = rln.resource_key
AND rln.language_code = ?
LEFT OUTER JOIN dwh_res_obs_mapping obsm ON obsm.resource_key = r.resource_key
AND t.staff_obs_unit_key = 0
INNER JOIN dwh_cmn_obs_hierarchy obsh ON obsh.child_obs_unit_key = (CASE WHEN t.staff_obs_unit_key = 0 THEN obsm.obs_unit_key ELSE t.staff_obs_unit_key END)
LEFT OUTER JOIN dwh_inv_team_period_facts pf ON t.team_key = pf.team_key
INNER JOIN dwh_cmn_period c ON pf.period_key = c.period_key
AND c.period_type_key = ?
WHERE 1=1
AND r.employment_type_key <> 0
AND r.resource_type_key <= 1
AND i.is_template = 0
AND obsh.parent_obs_unit_key = ?
AND c.period_start_date BETWEEN CASE WHEN ? = 'MONTHLY'
THEN <schema_name>.dwh_cal_startdate_fct(<schema_name>.dwh_cal_trunc_date_fct(?),'MONTH')
ELSE <schema_name>.dwh_cal_startdate_fct(<schema_name>.dwh_cal_trunc_date_fct(?),'WEEK') END
AND CASE WHEN ? = 'MONTHLY'
THEN <schema_name>.dwh_cal_date_add_fct(<schema_name>.dwh_cal_startdate_fct(<schema_name>.dwh_cal_trunc_date_fct(?),'MONTH'),'MONTH',11)
ELSE <schema_name>.dwh_cal_date_add_fct(<schema_name>.dwh_cal_startdate_fct(<schema_name>.dwh_cal_trunc_date_fct(?),'WEEK'),'WEEK',12) END
AND (r.is_active = CASE WHEN ? = 1 THEN r.is_active ELSE 1 END)
AND (i.is_active = CASE WHEN ? = 1 THEN i.is_active ELSE 1 END)
AND r.employment_type_key IN (?, ?)
AND 0 = 0
AND 0 = 0
AND 0 = 0
AND 0 = 0
AND 0 = 0
AND i.investment_status_key IN (?)
AND (? IS NULL
OR
i.investment_key IN (SELECT DISTINCT obsm.investment_key
FROM dwh_inv_obs_mapping obsm
INNER JOIN dwh_cmn_obs_hierarchy obsh ON obsm.obs_unit_key = obsh.child_obs_unit_key
WHERE obsh.parent_obs_unit_key = ?))
AND EXISTS (SELECT 1
FROM dwh_res_security_v
WHERE user_uid = ?
AND resource_key = r.resource_key)
GROUP BY r.employment_type_key, rln.employment_type
at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createStatement(JRJdbcQueryExecuter.java:486)
at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:295)
at com.jaspersoft.jasperserver.api.engine.jasperreports.util.JRTimezoneJdbcQueryExecuter.createDatasource(JRTimezoneJdbcQueryExecuter.java:169)
at com.jaspersoft.commons.util.JSControlledJdbcQueryExecuter.createDatasource(JSControlledJdbcQueryExecuter.java:114)
at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1160)
at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:717)
at net.sf.jasperreports.engine.fill.JRFillDatasetRun.evaluate(JRFillDatasetRun.java:248)
at net.sf.jasperreports.engine.fill.JRFillElementDataset.evaluateDatasetRun(JRFillElementDataset.java:238)
at net.sf.jasperreports.engine.analytics.dataset.FillMultiAxisDataset.evaluateData(FillMultiAxisDataset.java:105)
at net.sf.jasperreports.engine.analytics.dataset.FillMultiAxisData.evaluate(FillMultiAxisData.java:47)
at com.jaspersoft.jasperreports.highcharts.charts.ChartFillComponent.evaluateChart(ChartFillComponent.java:193)
at com.jaspersoft.jasperreports.highcharts.charts.ChartFillComponent.evaluate(ChartFillComponent.java:172)
at net.sf.jasperreports.engine.fill.JRFillComponentElement.evaluate(JRFillComponentElement.java:109)
at net.sf.jasperreports.engine.fill.JRFillElementContainer.evaluate(JRFillElementContainer.java:281)
at net.sf.jasperreports.engine.fill.JRFillFrame.evaluate(JRFillFrame.java:163)
at net.sf.jasperreports.engine.fill.JRFillElementContainer.evaluate(JRFillElementContainer.java:281)
at net.sf.jasperreports.engine.fill.JRFillBand.evaluate(JRFillBand.java:504)
at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillColumnBand(JRVerticalFiller.java:2026)
at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillGroupHeader(JRVerticalFiller.java:613)
at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillGroupHeaders(JRVerticalFiller.java:535)
at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReportStart(JRVerticalFiller.java:257)
at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReport(JRVerticalFiller.java:119)
at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:559)
at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFill.run(BaseFillHandle.java:119)
... 41 more
Caused by: java.sql.SQLException: Connection has already been closed.
at org.apache.tomcat.jdbc.pool.ProxyConnection.invoke(ProxyConnection.java:117)
at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:109)
at org.apache.tomcat.jdbc.pool.interceptor.AbstractCreateStatementInterceptor.invoke(AbstractCreateStatementInterceptor.java:67)
at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:109)
at org.apache.tomcat.jdbc.pool.interceptor.ConnectionState.invoke(ConnectionState.java:153)
at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:109)
at org.apache.tomcat.jdbc.pool.TrapException.invoke(TrapException.java:41)
at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:109)
at org.apache.tomcat.jdbc.pool.DisposableConnectionFacade.invoke(DisposableConnectionFacade.java:80)
at com.sun.proxy.$Proxy193.prepareStatement(Unknown Source)
at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createStatement(JRJdbcQueryExecuter.java:373)
... 64 more