Viewing: Overview of JasperReports Server Administration > Ad Hoc Options

Configuring Ad Hoc

Ad Hoc options help you fine-tune the Ad Hoc Editor and the reports it creates. You must be logged in as a user with ROLE_SUPERUSER to manage the Ad Hoc options and cache.

Ad Hoc Options

The Ad Hoc options set limits on the resources available for queries when Ad Hoc reports are designed and run. The options are:

•    Ad Hoc Filter List of Values Row Limit. The maximum number of items that should be displayed in the Condition Editor when a user defines filters for an Ad Hoc report that is based on a Domain. If this limit is exceeded when users define filters, JasperReports Server displays a message to that effect. Setting this to a lower value can improve performance.

•    Ad Hoc Dataset Row Limit. The maximum number of rows that an Ad Hoc report can return. JasperReports Server truncates the data when the limit is reached. Setting this to a lower number may improve performance, but your reports may not reflect the full data set.

•    Ad Hoc Query Timeout. The number of seconds the server should wait before timing out an Ad Hoc report while running its query. Setting this to a lower number may prevent exceptions from being displayed to users when they run Ad Hoc reports. Setting this to a higher number may prevent complex calculations from timing out, but it results in the use of more database connections.

Configuring the Ad Hoc Options

 

The Ad Hoc options must be set every time the server is restarted.

To set Ad Hoc options in JasperReports Server:

1.        Log in as system administrator (superuser by default).
2. Select Manage > Ad Hoc Settings.
3. In the Ad Hoc Filter List of Values Row Limit field, enter the maximum number of items to display in the Condition Editor when a user defines filters for an Ad Hoc report based on a Domain.
4. In the Ad Hoc Dataset Row Limit field, enter the maximum number of rows that an Ad Hoc report can return.
5. In the Ad Hoc Query Timeout (seconds) field, enter the number of seconds the server should wait before timing out an Ad Hoc report while running its query.
6. Click Save to make your changes effective. Or click Reset to return to the previously saved values.

Understanding Data Policies

Data policies determine how JasperReports Server handles data loading and processing for certain kinds of Ad Hoc reports. They determine how data is cached and where certain calculations occur. For example, you can specify that the data accessed by Domain-based reports is grouped, sorted, and aggregated in the database rather than having the server process it in memory.

By default, the server relies on the database to group, sort, and aggregate data returned by queries based on Domains when it is feasible. It also forces the server to only retrieve the fields that appear in the report (rather than the entire set of fields in the Domain). For queries based on JDBC data sources, such processing occurs in memory, and the entire result set defined in the Topic is returned.

If a query contains only aggregate values, JasperReports Server can generally transform it into an aggregate database query, but some functions (such as distinct count and average) must be calculated by reading all the detail rows from the database and performing the aggregation in memory. Note that independent check boxes control the behavior: one for Domains and another for JDBC data sources.

When these settings are disabled, the server loads the entire set of fields associated with a Domain or Topic into memory, and then applies the necessary grouping, sorting, and aggregation. This is also the case for Ad Hoc reports that do not rely on Domains or JDBC data sources; in these cases, the server processes the data in memory.

Generally, Jaspersoft recommends that these settings be enabled, especially when working with large datasets. In deciding whether JasperReports Server should process the data in memory or push that processing to the database, consider these factors:

•    The size and complexity of your reports. Reports with complex sorting, grouping, or aggregation may perform better when the server optimizes the queries.

•    The amount of data in your data sources. If your data sources include a great deal of data, reports against them may perform better when the server optimizes the queries.

•    The number of users editing and running Ad Hoc reports. If you have a large number of users creating and running Ad Hoc reports, performance may be better when the server optimizes the queries. Implementations with fewer users may perform better when the options are disabled.

•    The performance characteristics of your data source. If the database or other source of data is tuned for maximum performance, Ad Hoc reports may perform better when the server optimizes the queries. Note that, if your data source is hosted by MySQL, Jaspersoft recommends that you clear the check from the Optimize Queries for Domain-based Reports box.

•    The amount of memory allocated to JasperReports Server's Java Virtual Machine (JVM). If the JVM of the application server hosting JasperReports Server is allocated plenty of memory, Ad Hoc reports may perform better when JasperReports Server optimizes the queries. This is especially true if your data source tends to be slow.

To decide whether JasperReports Server should optimize queries for Ad Hoc reports, Jaspersoft recommends disabling the settings, opening and saving some representative reports, and testing their performance. If the performance improves, leave the settings disabled and open and save the remaining reports.

Configuring the Data Policies

 

Data policies must be set after every restart. They are not preserved between restarts.

The data polices that you can set are:

•    Optimize Queries for JDBC-based Reports. Selects grouping, sorting, and aggregation of queries for JDBC-based reports. Otherwise, the queries run unaltered in memory.

•    Optimize Queries for Domain-based Reports. Selects grouping, sorting, and aggregation of queries for Domain-based reports. Otherwise, the queries run unaltered in memory.

To set data policies in JasperReports Server:

1.        Log in as system administrator (superuser by default).
2. Select Manage > Ad Hoc Options.
3. Select Optimize Queries for JDBC-based Reports to process queries for JDBC-based reports.
4. Select Optimize Queries for Domain-based Reports to process queries for Domain-based reports.
5. Click Save to make your changes effective. Or click Reset to return to the previously saved values.

 

These data policy settings do not retroactively update the existing reports in your repository. To change the data policy for an existing report, select the appropriate policy setting, open the report in the Ad Hoc Editor, and save the report.

Managing the Ad Hoc Cache

JasperReports Server can temporarily cache ad hoc query result sets for re-use. Caching reduces hardware loads and delivers frequently-used datasets to the user quickly. Caching applies when reports are created as well as when they are run. You can configure the cache.

The Cache Mechanism

The cache is populated by queries. The queries are uniquely identified by a key that references the query itself, the data source URI, and parameters.

 

Typical Dataset in Ad Hoc Cache

By default, queries for each user are cached separately; a parameter in the cache key identifies the user. This per-user caching can result in duplicate result sets when different users run the same query, impairing performance. You can configure JasperReports Server to share result sets across users by adding the following lines to \WEB-INF\applicationContext-adhoc.xml. The code configures the cacheKeyInterceptor to ignore logged-in users’ credentials when creating the cache keys. Restart JasperReports Server after adding the code:

 

<property name=”ignoredParameters”>

<list>

<…values…>

<value>LoggedInUser</value>

<value>LoggedInUsername</value>

</list>

</property>

Configuring the Cache

Caching improves overall performance of data retrieval and sorting, but unused datasets can consume memory and cached data can become stale. To address these concerns, JasperReports Server automatically removes datasets periodically. By default, datasets are removed from the cache if they are not accessed for 20 minutes. They are also cleared after 60 minutes, regardless of how recently they were accessed.

You can configure the periodic clearing of the cache. You can also clear the cache manually.

To change the frequency with which the cache is automatically cleared:

Edit the following configuration file as shown:

 

Ad Hoc Cache Expiration

Configuration File

…\WEB-INF\applicationContext-adhoc.xml

Property

Bean

Description

defaultTimeoutMinutes

dataSetCache

The number of minutes to wait before removing a dataset from the cache. Ensures that stale data is periodically replaced with fresh data from the data source. The default is 60 minutes.

defaultUnusedTimeoutMinutes

dataSetCache

The number of minutes to wait after a dataset is used before removing it from the cache. The default is 20 minutes.

To clear a specific dataset from the cache:

1.        In JasperReports Server, log in as system administrator (superuser by default).
2. Click Manage> Ad Hoc Cache.

The Ad Hoc Dataset Caching page appears, displaying all the datasets that are in the cache, sorted by age.

 

Ad Hoc Dataset Caching Administration Page
3. To view the details of a specific dataset, click it in the Query & Source column.

The Dataset Caching Detail page appears, displaying the details of the selected query.

4. To remove a dataset from the cache, select it as in See "To view the details of a specific dataset, click it in the Query & Source column." and click Clear.
5. To remove all datasets, click Clear All.

Comparison with Jaspersoft OLAP

The following table contrasts the key features of the Ad Hoc cache in JasperReports Server and Jaspersoft OLAP.

 

Ad Hoc Caching in JasperReports Server and Jaspersoft OLAP

Feature

JasperReports Server

Jaspersoft OLAP
(Using Mondrian Server)

Structure of cache

 

Result caches are held at the query level: query text and language, plus datasource URI and query parameters.

Result caches are held at the analysis connection level: schema plus database connection.

Sharing

 

Not by default, but can be enabled.

There is only one cache; it is shared across all queries and users.

Security

 

Applied to cache control so that users are not allowed to see privileged data.

Same

Populating

 

Queries populate the cache. You can also run queries to pre-populate the cache during off-hours.

Same

Clearing the cache

Periodically, by the system

See section See "Configuring the Cache" above.

In low-memory situations, cached items are removed automatically by JVM garbage collection; the least-recently-used items are cleared first. There is no way to remove data based on how long it has been in memory.

Individual datasets, manually

See section See "Configuring the Cache" above.

Cache regions can be defined and cleared programmatically with APIs.

All datasets, manually

See section See "Configuring the Cache" above.

In JasperReports Server, click Manage > Analysis Options. On the Analysis Options page click OLAP Cache: Flush Now.

For additional methods, see the Jaspersoft OLAP Ultimate Guide.

Size

 

Limited by available JVM memory (heap). Not configurable.

Running out of memory is unusual. It can only happen if a single query returns too many elements for available memory. The report fails with an out-of-memory error.

Same