Clarity

  • Private Community

  • 1.  Jaspersoft Cascade Input control displays only one row

    Posted Jun 12, 2018 07:22 AM

    Jaspersoft studio 6.4.2

     

    I have two inputs controls.  Portfolio and Programs

     

    I'm trying to create cascade input control so the display of second value is based on the first value selected.

     

    I want to display the programs based on the portfolio I select.  I get only one program name when I select the portfolio whereas the portfolio has more than one programs

     

    Any help/pointer is greatly appreciated

     

    SELECT DISTINCT PFM.ID,pfm.code, pfm.name, prg.code Program_ID
    , prg.name Program_Name
    from inv_investments prg
    LEFT OUTER JOIN inv_hierarchies ih
    ON ih.parent_id = prg.id
    LEFT OUTER JOIN inv_investments prj
    ON prj.id = ih.child_id
    LEFT OUTER JOIN srm_resources res
    ON res.user_id = prj.manager_id
    LEFT OUTER JOIN pfm_investments pinv
    ON pinv.investment_id = NVL(prg.id, prj.id)
    LEFT OUTER JOIN pfm_portfolios pfm
    ON pfm.id = pinv.portfolio_id
    WHERE  prj.id IS NOT NULL
    AND prj.is_active = 1
    AND $X{IN,PFM.ID ,portfolioKey_1}
    ORDER BY pfm.code

     



  • 2.  Re: Jaspersoft Cascade Input control displays only one row

    Posted Jun 12, 2018 07:50 AM

    I will suggest running this SQL by passing pfm.id manually to check if data is there against the portfolio or not, syntax for cascading jasper input control is correct. Also investment to hierarchy join doesn't make sense.

     

    SQL for Portfolio with program under them

     

    SELECT DISTINCT PFM.ID,pfm.code, pfm.name, prg.code Program_ID
    , prg.name Program_Name
    from pfm_portfolios pfm 

    Inner JOIN pfm_investments pinv ON pfm.id = pinv.portfolio_id 

    Inner JOIN inv_investments prg ON pinv.investment_id = prg.id

    Inner join srm_projects srm_prg on srm_prg.id =  prg.id
    LEFT OUTER JOIN srm_resources res ON res.user_id = prg.manager_id

    WHERE 1=1
    AND prg.is_active = 1

    AND srm_prg.is_program =1
    AND $X{IN,PFM.ID ,portfolioKey_1}
    ORDER BY pfm.code



  • 3.  Re: Jaspersoft Cascade Input control displays only one row

    Posted Jun 12, 2018 07:59 AM

    Thanks Prashank for the prompt response.  When I run the SQL query on the oracle client, I get multiple records for a single portfolio whereas the input control displays only one row.

     

    I even, tried your query and I get only ONE row on the program input control.

     

    I have selected 'Multi select Query' on the program input control.  Am I missing anything else?

     

     



  • 4.  Re: Jaspersoft Cascade Input control displays only one row

    Posted Jun 12, 2018 10:18 AM

    What happens if you hard code the portfolio key in the input control query?  Does it return multiple programs?

     

    Change

     

    AND $X{IN,PFM.ID ,portfolioKey_1}   

     

    to

     

    AND $X{IN,PFM.ID ,12345678}  where 12345678 ID of some program

     

    Could also try 

     

    AND    PFM.ID = $P{portfolioKey_1}

     

     



  • 5.  Re: Jaspersoft Cascade Input control displays only one row

    Posted Jun 12, 2018 10:42 AM

    Hi Steve

     

    I have hard coded the Portfolio internal id but still I get only one result.  

     

    SELECT DISTINCT PFM.ID,pfm.code, pfm.name, prg.code Program_ID
    , prg.name Program_Name
    from pfm_portfolios pfm
    Inner JOIN pfm_investments pinv ON pfm.id = pinv.portfolio_id
    Inner JOIN inv_investments prg ON pinv.investment_id = prg.id
    Inner join srm_projects srm_prg on srm_prg.id = prg.id
    LEFT OUTER JOIN srm_resources res ON res.user_id = prg.manager_id
    WHERE 1=1
    AND prg.is_active = 1
    AND srm_prg.is_program =1
    AND PFM.ID IN (5002023,5002024,5005020)
    ORDER BY pfm.code

     

    I get the below error message when I tried with parameter (PFM.ID = $P{portfolioKey_1}) .

     

    Error preparing statement for executing the report query: SELECT DISTINCT PFM.ID,pfm.code, pfm.name, prg.code Program_ID , prg.name Program_Name from pfm_portfolios pfm Inner JOIN pfm_investments pinv ON pfm.id = pinv.portfolio_id Inner JOIN inv_investments prg ON pinv.investment_id = prg.id Inner join srm_projects srm_prg on srm_prg.id = prg.id LEFT OUTER JOIN srm_resources res ON res.user_id = prg.manager_id WHERE 1=1 AND prg.is_active = 1 AND srm_prg.is_program =1 AND PFM.ID = ? ORDER BY pfm.code

     

    net.sf.jasperreports.engine.JRException: Error preparing statement for executing the report query: SELECT DISTINCT PFM.ID,pfm.code, pfm.name, prg.code Program_ID , prg.name Program_Name from pfm_portfolios pfm Inner JOIN pfm_investments pinv ON pfm.id = pinv.portfolio_id Inner JOIN inv_investments prg ON pinv.investment_id = prg.id Inner join srm_projects srm_prg on srm_prg.id = prg.id LEFT OUTER JOIN srm_resources res ON res.user_id = prg.manager_id WHERE 1=1 AND prg.is_active = 1 AND srm_prg.is_program =1 AND PFM.ID = ? ORDER BY pfm.code at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createStatement(JRJdbcQueryExecuter.java:524) at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:297) at com.jaspersoft.jasperserver.api.engine.jasperreports.util.JRTimezoneJdbcQueryExecuter.createDatasource(JRTimezoneJdbcQueryExecuter.java:169) at com.jaspersoft.commons.util.JSControlledJdbcQueryExecuter.createDatasource(JSControlledJdbcQueryExecuter.java:113) at com.jaspersoft.jasperserver.api.engine.jasperreports.util.JRQueryExecuterAdapter.executeQuery(JRQueryExecuterAdapter.java:136) at com.jaspersoft.jasperserver.api.engine.jasperreports.util.JRQueryExecuterAdapter.executeQuery(JRQueryExecuterAdapter.java:111) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.executeQuery(EngineServiceImpl.java:2302) at sun.reflect.GeneratedMethodAccessor1950.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.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.$Proxy70.executeQuery(Unknown Source) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EhcacheEngineService.executeQuery(EhcacheEngineService.java:184) at com.jaspersoft.jasperserver.war.cascade.CachedEngineService.executeQuery(CachedEngineService.java:136) at com.jaspersoft.jasperserver.war.cascade.handlers.QueryValuesLoader.loadValues(QueryValuesLoader.java:102) at com.jaspersoft.jasperserver.war.cascade.handlers.SingleSelectListInputControlHandler.fillStateValue(SingleSelectListInputControlHandler.java:123) at com.jaspersoft.jasperserver.war.cascade.handlers.BasicInputControlHandler.getState(BasicInputControlHandler.java:160) at com.jaspersoft.jasperserver.war.cascade.GenericInputControlLogic.getValuesForInputControls(GenericInputControlLogic.java:211) at com.jaspersoft.jasperserver.war.cascade.GenericInputControlLogic.getValuesForInputControlsFromRawData(GenericInputControlLogic.java:258) at com.jaspersoft.jasperserver.war.cascade.InputControlsLogicServiceImpl$4.callByContainer(InputControlsLogicServiceImpl.java:200) at com.jaspersoft.jasperserver.war.cascade.InputControlsLogicServiceImpl$4.callByContainer(InputControlsLogicServiceImpl.java:192) at com.jaspersoft.jasperserver.war.cascade.InputControlsLogicServiceImpl.callControlLogic(InputControlsLogicServiceImpl.java:248) at com.jaspersoft.jasperserver.war.cascade.InputControlsLogicServiceImpl.getValuesForInputControls(InputControlsLogicServiceImpl.java:192) at com.jaspersoft.jasperserver.war.cascade.InputControlsLogicServiceImpl.getInputControlsWithValues(InputControlsLogicServiceImpl.java:130) at com.jaspersoft.jasperserver.jaxrs.report.InputControlsJaxrsService$1.call(InputControlsJaxrsService.java:99) at com.jaspersoft.jasperserver.jaxrs.report.InputControlsJaxrsService$1.call(InputControlsJaxrsService.java:91) at com.jaspersoft.jasperserver.jaxrs.report.ReportsServiceCallTemplate.callRemoteService(ReportsServiceCallTemplate.java:49) at com.jaspersoft.jasperserver.remote.common.RemoteServiceWrapper.callRemoteService(RemoteServiceWrapper.java:44) at com.jaspersoft.jasperserver.jaxrs.report.InputControlsJaxrsService.internalGetReportInputParameters(InputControlsJaxrsService.java:91) at com.jaspersoft.jasperserver.jaxrs.report.InputControlsJaxrsService.getReportInputParametersViaPost(InputControlsJaxrsService.java:80) at sun.reflect.GeneratedMethodAccessor1572.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at com.sun.jersey.spi.container.JavaMethodInvokerFactory$1.invoke(JavaMethodInvokerFactory.java:60) at com.sun.jersey.server.impl.model.method.dispatch.AbstractResourceMethodDispatchProvider$ResponseOutInvoker._dispatch(AbstractResourceMethodDispatchProvider.java:205) at com.sun.jersey.server.impl.model.method.dispatch.ResourceJavaMethodDispatcher.dispatch(ResourceJavaMethodDispatcher.java:75) at com.sun.jersey.server.impl.uri.rules.HttpMethodRule.accept(HttpMethodRule.java:302) at com.sun.jersey.server.impl.uri.rules.ResourceClassRule.accept(ResourceClassRule.java:108) at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147) at com.sun.jersey.server.impl.uri.rules.RootResourceClassesRule.accept(RootResourceClassesRule.java:84) at com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1542) at com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1473) at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1419) at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1409) at com.sun.jersey.spi.container.servlet.WebComponent.service(WebComponent.java:409) at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:558) at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:733) at javax.servlet.http.HttpServlet.service(HttpServlet.java:742) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) at org.owasp.csrfguard.CsrfGuardFilter.doFilter(CsrfGuardFilter.java:88) at com.jaspersoft.jasperserver.api.security.csrf.JSCsrfGuardFilter.doFilter(JSCsrfGuardFilter.java:72) at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:343) at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:260) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) at com.jaspersoft.jasperserver.api.security.csrf.CrossDomainCommunicationFilter.doFilter(CrossDomainCommunicationFilter.java:116) at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:343) at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:260) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) at com.jaspersoft.ji.jaxrs.diagnostic.DiagnosticLoggingFilterPRO.doFilter(DiagnosticLoggingFilterPRO.java:43) at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:343) at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:260) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:330) at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:118) at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:84) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:113) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:113) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) at com.jaspersoft.ji.jaxrs.diagnostic.DiagnosticLoggingFilterPRO.doFilter(DiagnosticLoggingFilterPRO.java:43) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) at com.jaspersoft.jasperserver.api.metadata.user.service.impl.MetadataAuthenticationProcessingFilter.doFilter(MetadataAuthenticationProcessingFilter.java:143) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) at com.jaspersoft.jasperserver.multipleTenancy.MTBasicProcessingFilter.doFilter(MTBasicProcessingFilter.java:194) at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:343) at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:260) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) at com.jaspersoft.jasperserver.war.UserPreferencesFilter.doFilter(UserPreferencesFilter.java:235) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:199) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) at com.ca.jasper.ext.filter.TenantIdLoggingFilter.doFilter(TenantIdLoggingFilter.java:94) at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:343) at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:260) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) at com.ca.jasper.authentication.JaaSProxyAuthenticationFilter.doFilter(JaaSProxyAuthenticationFilter.java:202) at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:343) at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:260) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) at com.jaspersoft.jasperserver.war.NullFilter.doFilter(NullFilter.java:43) at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:343) at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:260) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) at com.ca.jasper.ext.filter.ExternalAuthenticationProxyFilter.doFilter(ExternalAuthenticationProxyFilter.java:109) at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:343) at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:260) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) at com.jaspersoft.jasperserver.war.UserPreferencesFilter.doFilter(UserPreferencesFilter.java:235) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) at com.jaspersoft.jasperserver.api.logging.filter.BasicLoggingFilter.doFilter(BasicLoggingFilter.java:57) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) at com.jaspersoft.jasperserver.api.security.WebAppSecurityFilter.doFilter(WebAppSecurityFilter.java:80) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) at com.jaspersoft.jasperserver.war.NullFilter.doFilter(NullFilter.java:43) at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:343) at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:260) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) at com.jaspersoft.jasperserver.api.security.encryption.EncryptionFilter.doFilter(EncryptionFilter.java:150) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:87) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) at com.jaspersoft.ji.license.JILicenseFilter.doFilter(JILicenseFilter.java:86) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:192) at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:160) at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:343) at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:260) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) at net.bull.javamelody.MonitoringFilter.doFilter(MonitoringFilter.java:201) at net.bull.javamelody.MonitoringFilter.doFilter(MonitoringFilter.java:178) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) at com.jaspersoft.jasperserver.war.util.SessionDecoratorFilter.doFilter(SessionDecoratorFilter.java:63) at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:343) at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:260) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) at com.jaspersoft.jasperserver.war.util.CharacterEncodingFilter.doFilter(CharacterEncodingFilter.java:67) at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:343) at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:260) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) at com.jaspersoft.jasperserver.war.httpheaders.ResourceHTTPHeadersFilter.doFilter(ResourceHTTPHeadersFilter.java:77) at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:343) at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:260) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) at com.jaspersoft.jasperserver.war.P3PFilter.doFilter(P3PFilter.java:43) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:199) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:595) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81) at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:650) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342) at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:803) at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66) at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868) at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1459) at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) at java.lang.Thread.run(Thread.java:748) Caused by: java.sql.SQLException: Invalid column type at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:8488) at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:7995) at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8735) at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:8714) at oracle.jdbc.driver.OraclePreparedStatementWrapper.setObject(OraclePreparedStatementWrapper.java:219) at sun.reflect.GeneratedMethodAccessor1343.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114) at com.sun.proxy.$Proxy188.setObject(Unknown Source) at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.setStatementParameter(JRJdbcQueryExecuter.java:775) at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.setStatementParameter(JRJdbcQueryExecuter.java:572) at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter$1.visit(JRJdbcQueryExecuter.java:480) at net.sf.jasperreports.engine.query.JRAbstractQueryExecuter$QueryParameter.accept(JRAbstractQueryExecuter.java:163) at net.sf.jasperreports.engine.query.JRAbstractQueryExecuter.visitQueryParameters(JRAbstractQueryExecuter.java:666) at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createStatement(JRJdbcQueryExecuter.java:465) ... 170 more


  • 6.  Re: Jaspersoft Cascade Input control displays only one row

    Posted Jun 12, 2018 03:22 PM

    If $P{portfolioKey_1} is a collection (more then 1 value) then  PFM.ID = $P{portfolioKey_1}  would error out. 

     

     

    If your hardcoded query is executed from SQL client what gets returned?

     

    SELECT DISTINCT PFM.ID,pfm.code, pfm.name, prg.code Program_ID 
    , prg.name Program_Name
    from pfm_portfolios pfm 
    Inner JOIN pfm_investments pinv ON pfm.id = pinv.portfolio_id 
    Inner JOIN inv_investments prg ON pinv.investment_id = prg.id
    Inner join srm_projects srm_prg on srm_prg.id = prg.id
    LEFT OUTER JOIN srm_resources res ON res.user_id = prg.manager_id
    WHERE 1=1
    AND prg.is_active = 1
    AND srm_prg.is_program =1
    AND PFM.ID IN (5002023,5002024,5005020)
    ORDER BY pfm.code



  • 7.  Re: Jaspersoft Cascade Input control displays only one row
    Best Answer

    Posted Jun 13, 2018 02:25 AM

    Hi Steve

     

     

    I resolved the problem.

     

    Lessons Learnt.  On the program input control query, i have removed the portfolio fields and selected only the program fields.  Also, Program key input control should have value column matching with query field name.

     

    Thank you ALL for contributing this

     

    Appreciated