cross tab reports

[nirum]

Hi,

  I created a cross-tab report using the ad-hoc option in the JasperServer.  Even as I designed the report ( adding the column groups and row groups),  the tool gave a "Too many rows" error but it showed an overall design with a single row.

When I tried to run the report, it gave me "The query for the report has been canceled becuase it returned more than 200000 rows. Modify the report to reduce the number of rows".

Is this a limitation of JasperReports - is 200000 the maximum number of rows that it can return? How can we restrict our clients databases. This doesn't seeem to be reasonable expectation.

Thanks

Niru

[nirum]

I did nto get an answer to this however I found out  from other sources that there is a limitation set in applicationContext-adhoc.xml for the default number of rows/columns an ad hoc report can handle. so I increased that to 1000000 and restarted the server. However, it still crashed the server when I tried to run the report  - it tried to run the report throws the following exception and dies.

Thanks for any help in this matter, There are exactly 277000 rows in this particular table that I am accessing.

14:10:17,695 ERROR ViewAdhocReportAction,http-9090-Processor25:171 - error setti
ng up report
com.jaspersoft.commons.util.QueryExecutionTooManyRowsException: jsexception.Quer
yExecutionTooManyRowsException
        at com.jaspersoft.commons.util.JSControlledResultSetDataSource.next(JSCo
ntrolledResultSetDataSource.java:34)
        at com.jaspersoft.commons.semantic.dsimpl.JRQueryDataSet$JRDataSetIterat
or.next(JRQueryDataSet.java:244)
        at com.jaspersoft.commons.datarator.jr.DataratorJRDataSource.next(Datara
torJRDataSource.java:25)
        at com.jaspersoft.commons.datarator.jr.JRDataSourceAdapter$JRDataSetIter
ator.next(JRDataSourceAdapter.java:134)
        at com.jaspersoft.commons.datarator.CachedData.fetchData(CachedData.java
:51)
        at com.jaspersoft.commons.datarator.DataSetCacheImpl.getCachedDataSet(Da
taSetCacheImpl.java:132)
        at com.jaspersoft.commons.datarator.DataSetCacheImpl.getCachedDataSet(Da
taSetCacheImpl.java:78)
        at com.jaspersoft.ji.adhoc.service.AdhocEngineServiceImpl.getDatarator(A
dhocEngineServiceImpl.java:252)
        at com.jaspersoft.ji.adhoc.service.AdhocEngineServiceImpl.getDatarator(A
dhocEngineServiceImpl.java:188)
        at com.jaspersoft.ji.adhoc.action.ViewAdhocReportAction.verifyData(ViewA
dhocReportAction.java:147)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.
java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces
sorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:585)
        at org.springframework.webflow.util.DispatchMethodInvoker.invoke(Dispatc
hMethodInvoker.java:103)
        at org.springframework.webflow.action.MultiAction.doExecute(MultiAction.
java:136)
        at org.springframework.webflow.action.AbstractAction.execute(AbstractAct
ion.java:203)
        at org.springframework.webflow.engine.AnnotatedAction.execute(AnnotatedA
ction.java:142)
        at org.springframework.webflow.engine.ActionExecutor.execute(ActionExecu
tor.java:61)
        at org.springframework.webflow.engine.ActionState.doEnter(ActionState.ja
va:180)
        at org.springframework.webflow.engine.State.enter(State.java:200)
        at org.springframework.webflow.engine.Transition.execute(Transition.java
:229)
        at org.springframework.webflow.engine.TransitionableState.onEvent(Transi
tionableState.java:112)
        at org.springframework.webflow.engine.Flow.onEvent(Flow.java:572)
        at org.springframework.webflow.engine.impl.RequestControlContextImpl.sig
nalEvent(RequestControlContextImpl.java:207)
        at org.springframework.webflow.engine.ActionState.doEnter(ActionState.ja
va:185)
        at org.springframework.webflow.engine.State.enter(State.java:200)
        at org.springframework.webflow.engine.Flow.start(Flow.java:557)
        at org.springframework.webflow.engine.impl.RequestControlContextImpl.sta
rt(RequestControlContextImpl.java:195)
        at org.springframework.webflow.engine.impl.FlowExecutionImpl.start(FlowE
xecutionImpl.java:189)
        at org.springframework.webflow.executor.FlowExecutorImpl.launch(FlowExec
utorImpl.java:206)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.
java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces
sorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:585)
        at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflecti
on(AopUtils.java:281)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJo
inpoint(ReflectiveMethodInvocation.java:187)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(
ReflectiveMethodInvocation.java:154)
        at org.acegisecurity.intercept.method.aopalliance.MethodSecurityIntercep
tor.invoke(MethodSecurityInterceptor.java:66)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(
ReflectiveMethodInvocation.java:176)
        at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynami
cAopProxy.java:210)
        at $Proxy17.launch(Unknown Source)
        at org.springframework.webflow.executor.support.FlowRequestHandler.handl
eFlowRequest(FlowRequestHandler.java:131)
        at org.springframework.webflow.executor.mvc.FlowController.handleRequest
Internal(FlowController.java:170)
        at org.springframework.web.servlet.mvc.AbstractController.handleRequest(
AbstractController.java:153)
        at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.ha
ndle(SimpleControllerHandlerAdapter.java:45)
        at org.springframework.web.servlet.DispatcherServlet.doDispatch(Dispatch
erServlet.java:820)
        at org.springframework.web.servlet.DispatcherServlet.doService(Dispatche
rServlet.java:755)
        at org.springframework.web.servlet.FrameworkServlet.processRequest(Frame
workServlet.java:396)
        at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServl
et.java:350)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:689)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appl
icationFilterChain.java:252)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationF
ilterChain.java:173)
        at com.jaspersoft.jasperserver.war.common.UploadMultipartFilter.doFilter
(UploadMultipartFilter.java:86)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appl
icationFilterChain.java:202)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationF
ilterChain.java:173)
        at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(F
ilterChainProxy.java:264)
        at org.acegisecurity.ui.switchuser.SwitchUserProcessingFilter.doFilter(S
witchUserProcessingFilter.java:335)
        at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(F
ilterChainProxy.java:274)
        at org.acegisecurity.intercept.web.FilterSecurityInterceptor.invoke(Filt
erSecurityInterceptor.java:107)
        at org.acegisecurity.intercept.web.FilterSecurityInterceptor.doFilter(Fi
lterSecurityInterceptor.java:72)
        at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(F
ilterChainProxy.java:274)
        at org.acegisecurity.ui.ExceptionTranslationFilter.doFilter(ExceptionTra
nslationFilter.java:110)
        at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(F
ilterChainProxy.java:274)
        at com.jaspersoft.ji.license.JILicenseFilter.doFilter(JILicenseFilter.ja
va:107)
        at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(F
ilterChainProxy.java:274)
        at org.acegisecurity.providers.anonymous.AnonymousProcessingFilter.doFil
ter(AnonymousProcessingFilter.java:125)
        at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(F
ilterChainProxy.java:274)
        at com.jaspersoft.jasperserver.api.metadata.user.service.impl.MetadataAu
thenticationProcessingFilter.doFilter(MetadataAuthenticationProcessingFilter.jav
a:141)
        at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(F
ilterChainProxy.java:274)
        at com.jaspersoft.jasperserver.war.util.RequestParameterAuthenticationFi
lter.doFilter(RequestParameterAuthenticationFilter.java:97)
        at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(F
ilterChainProxy.java:274)
        at org.acegisecurity.ui.basicauth.BasicProcessingFilter.doFilter(BasicPr
ocessingFilter.java:181)
        at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(F
ilterChainProxy.java:274)
        at com.jaspersoft.jasperserver.war.UserPreferencesFilter.doFilter(UserPr
eferencesFilter.java:165)
        at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(F
ilterChainProxy.java:274)
        at org.acegisecurity.ui.AbstractProcessingFilter.doFilter(AbstractProces
singFilter.java:217)
        at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(F
ilterChainProxy.java:274)
        at com.jaspersoft.jasperserver.war.UserPreferencesFilter.doFilter(UserPr
eferencesFilter.java:165)
        at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(F
ilterChainProxy.java:274)
        at org.acegisecurity.context.HttpSessionContextIntegrationFilter.doFilte
r(HttpSessionContextIntegrationFilter.java:191)
        at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(F
ilterChainProxy.java:274)
        at org.acegisecurity.util.FilterChainProxy.doFilter(FilterChainProxy.jav
a:148)
        at org.acegisecurity.util.FilterToBeanProxy.doFilter(FilterToBeanProxy.j
ava:90)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appl
icationFilterChain.java:202)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationF
ilterChain.java:173)

        at com.jaspersoft.jasperserver.war.util.CharacterEncodingFilter.doFilter

(CharacterEncodingFilter.java:70)

        at org.springframework.web.filter.DelegatingFilterProxy.doFilter(Delegat

ingFilterProxy.java:138)

        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appl

icationFilterChain.java:202)

        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationF

ilterChain.java:173)

        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperV

alve.java:213)

        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextV

alve.java:178)

        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.j

ava:126)

        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.j

ava:105)

        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineVal

ve.java:107)

        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.jav

a:148)

        at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java

:869)

        at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.p

rocessConnection(Http11BaseProtocol.java:664)

        at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpo

int.java:527)

        at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFol

lowerWorkerThread.java:80)

        at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadP

ool.java:684)

        at java.lang.Thread.run(Thread.java:595)

 

 

 

Hi,   I created a cross-tab report using the ad-hoc option in the JasperServer.  Even as I designed the report ( adding the column groups and row groups),  the tool gave a "Too many rows" error but it showed an overall design with a single row. When I tried to run the report, it gave me "The query for the report has been canceled becuase it returned more than 200000 rows. Modify the report to reduce the number of rows". Is this a limitation of JasperReports - is 200000 the maximum number of rows that it can return? How can we restrict our clients databases. This doesn't seeem to be reasonable expectation. Thanks Niru

[frankee787]

Try changing  the value of  "maxResultSetRows" (default 200000)    in the "applicationContext-semanticLayer.xml" file. Should work.

 

Regards,

Franklin