Where JasperReports Server AdHoc Cache Query “Limit 1000” is coming from?

When running an AdHoc View related query in JasperReports Server, user often see “limit 1000” in the AdHoc Cache query. This limit is the fetch size setting for SQL query statement retrieval in foreground AdHoc View execution. The purpose of this limit is to prevent large amount of data from flooding the web UI that 1) takes forever to load, and 2) can potentially crash the browser.

There’s no property configuration in JasperReports Server Ad Hoc Settings web UI to alter this limit for it is hard coded in JRS CommonDomainDataStrategy class:

public abstract class CommonDomainDataStrategy extends BaseDataStrategy {
......

public static int PREFETCH_SIZE = 1000;

......

To set the prefetchSize to a different value, user can edit applicationContext-adhoc-dataStrategy.xml file (under jasperserver-proWEB-INF directory), locate "commonDomainDataStrategy" bean, and add "prefetchSize" properties to set the limit. For example:

<bean id="commonDomainDataStrategy"
class="com.jaspersoft.ji.adhoc.strategy.CommonDomainDataStrategy"
parent="baseDataStrategy" scope="prototype">
<property name="aggregateConfigs" ref="aggregateConfigs"/>
<!--
These
properties apply to domainDataStrategy and (for dynamic filters)
sqlQueryDataStrategy. They determine whether different types of filters
should be applied by modifying the generated SQL or by filtering results
in memory.
-->
<property name="applyQueryFilterInMemory" value="false"/>
<property name="applySecurityFilterInMemory" value="false"/>
<property name="applyDynamicFilterInMemory" value="false"/>
<property name="configuration" ref="slConfig"/>
<property name="PREFETCH_SIZE" value="2000"/>
</bean>

After making the change, save it and make sure to restart the server to have the new limit take effect.

TTC-20150317-58091