Ad Hoc functionality is available only to JasperReports Server Professional edition users.

Ad Hoc settings help you fine-tune the Ad Hoc Editor and the views created in it. Users with ROLE_SUPERUSER can manage the Ad Hoc settings and cache. This section covers the following ways to configure Ad Hoc:

Ad Hoc Query Settings

The Ad Hoc settings include the following:

To configure the Ad Hoc query settings:

Ad Hoc Data Policies

Data policies determine how data is cached and where certain calculations occur. All Ad Hoc work is based on a query, either from a Domain or Domain Topic, or from the JRXML of a plain Topic. Data policies determine whether the Ad Hoc engine uses the query as-is and process the data in memory, or rewrites the query so that the database processes data and returns only what Ad Hoc needs to display.

By default, the data accessed by Domain-based reports is grouped, sorted, and aggregated in the database, rather than processed in memory on the server. This way the server retrieves only the columns that appear in the report rather than the entire set of fields in the Domain. As of JasperReports Server 5.5, calculated fields are also processed by the database. For Topic queries based on JDBC (and JNDI) data sources, the default behavior is to request the entire result set and process the columns for display in memory. Note that independent check boxes control the behavior -- one for Domains and another for JDBC data sources.

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

Generally, we recommend 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:

To decide whether JasperReports Server should optimize queries for Ad Hoc views, we recommend 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.

The data polices you can set are:

To configure the Ad data policies:

Ad Hoc Data Policies for Big Data

When handling large datasets (big data) from a Domain source in the Ad Hoc Editor, fields summarized by distinct count are computationally intensive. The server optimizes the distinct count by requesting distinct count calculations from the data source, as opposed to performing the calculations in memory. Database servers are usually optimized for these calculations, which improves the overall performance of the Ad Hoc Editor.

Performing distinct count aggregates in the database applies only in the following cases:

This setting has no effect when there is a row or column group involving a time, timestamp, or date. In this case, Ad Hoc performs the distinct count summary calculations in memory, regardless of the calcMethod setting.

Ad Hoc Templates and Report Generators

JasperReports Server5.5 introduced Ad Hoc templates and report generators. Ad Hoc templates are JRXML files in the repository that define the format for reports generated from Ad Hoc views. Report generators are custom beans that create custom output from Ad Hoc views.

In the user interface, users can select either the default template, browse for a different template, or select a report generator, if any are defined. For more information about the report template interface, see the JasperReports Server User Guide. This section explains how to configure these controls.

Changing the Default Ad Hoc Template

The default Ad Hoc template creates a report that resembles the Ad Hoc user interface. It's meant for online viewing and doesn't restrict the size of the contents for printing. To set a different template, modify the following property:

Modifying the Ad Hoc Template Folders

Ad Hoc templates must be uploaded to specific folders in the repository. The default folders are /templates (in every organization) and /public/templates. To set different folders, modify the following property:

The default Ad Hoc template locations contain the default template. If you move or delete those folders in the repository, be sure to update the definition of the default Ad Hoc template as described in the previous section.

Also, organization templates include the adhoc/templates folder so it appears in every new organization. You should update all organization templates if you change or remove the %/templates value. For more information, see Default Folders for Organizations.

Adding Ad Hoc Report Generators

By default no custom report generators defined on the server, and none appear in the user interface.

To add a custom report generator:

Ad Hoc Configuration File

The following properties are among those that can be configured in the WEB-INF/applicationContext-adhoc.xml file:

Configurable Properties in WEB-INF/applicationContext-adhoc.xml

Property

Description

JrxmlScriptURI

The location in the file system of the state2jrxml.js script, which generates the JRXML report based on the current Ad Hoc Editor selections. By default, this file is located in the /adhoc folder of the repository.

realmsURI and realmsURIParent
SQLPatternList

The repository locations where Topics should reside. The defaults are /adhoc/topics and /public/adhoc/topics.

defaultTheme

The name of the default style for Ad Hoc views. This name must match a style defined in both a CSS and a JRXML file. The default is default.

aruFolder

The repository location where users are allowed to save their Ad Hoc views. The default is /. This allows your users to save Ad Hoc views anywhere. If you have a folder specifically for user content, specify this folder; for example, /userviews.

tempFolderName

The repository location where JasperReports Server saves reports created from Ad Hoc views. The default is /temp relative to root and to every organization.

The server allows users with ROLE_ADMINISTRATOR or ROLE_SUPERUSER to view the temporary folders and their contents. The server manages these temporary files automatically, but files may accumulate in certain cases. As part of regular maintenance, you should periodically delete the files in these folders.

maxSafeGroupMembers

The maximum number of row groups or column groups a crosstab can display before the editor prompts the user for confirmation. This limit is a safeguard to avoid performance issues when grouping a field with too many values. The default is 100. Set it higher to allow more groups to appear without prompting users.

createColumn
CrosstabHeaders

This property is located in the actualSizeReportGenerator bean. With the default setting of false, column group headers appear in the Ad Hoc view but not in the report generated from the view (this is the historical behavior). When set to true, column group headers appear in both the Ad Hoc view and in the generated report. When column group headers are included in a crosstab, an extra row is added below the column headers for spacing.

Ad Hoc Cache Management

JasperReports Server can temporarily cache Ad Hoc query result sets for re-use. The cache is populated by the data that results from queries when creating or running Ad Hoc views. The datasets are uniquely identified by a key that references the query itself, the data source URI, and parameters used when the query was issued.

Caching reduces database loads and delivers frequently-used datasets to the user quickly. Caching applies when reports are created and when they're run. JasperReports Server version 5.0 introduces a new cache implementation called Ehcache that allows the administrator to view cache entry memory size and set limits on memory use. You can configure the Ad Hoc cache to optimize memory use and response time for your use patterns.

Setting the Cache Granularity

By default, datasets for each user are cached separately. A parameter in the cache key identifies the user. This per-user caching can result in duplicate datasets and impaired performance when different users run the same query. You can configure JasperReports Server to share cached datasets across users by editing the /WEB-INF/applicationContext-datarator.xml file.

The following code configures the cacheKeyInterceptor bean to ignore logged-in users’ credentials when creating the cache keys:

Restart JasperReports Server after saving the modified file.

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, the cache automatically removes datasets periodically. By default, datasets are removed from the cache if they are not accessed for 30 minutes. They are also cleared after 90 minutes, regardless of how recently they were accessed.

To configure the frequency with which the cache is automatically cleared, edit the following configuration file:

Restart JasperReports Server after modifying these values.

Manually Clearing the Cache

Administrators can also use the server interface to view the queries whose datasets are in the cache. Administrators can see the full query but never the contents of the dataset. The Ad Hoc cache page also displays performance data about each query. This information can be helpful when trying to resolve performance issues. The interface displays several values for every query:

The Ad Hoc cache page also allows administrators to manually remove datasets if necessary. Removing a dataset from the cache forces the server to get fresh data the next time a user creates or runs an Ad Hoc view with that query.

To view queries and manually clear the Ad Hoc cache:

The Ad Hoc Cache page appears, displaying all the datasets in the cache, sorted by age.

Each dataset is listed by its corresponding query and data source. Recall that Ad Hoc Topics have user-defined queries, so they tend to be short, whereas the query for Domains are generated from the design of the Domain and user selections in the Data Chooser dialog. The Ad Hoc Cache page displays only the first few lines of a query, as well as the data source.

Comparison with Jaspersoft OLAP Cache

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

Cache Feature

JasperReports Server

Jaspersoft OLAP

Structure of cache

Result caches are held at the query level: query text and language, plus data source 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 as described in Setting the Cache Granularity, above.

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.

Populating

Queries populate the cache. You can also schedule reports to pre-populate the cache during off-hours.

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.

Automatic time-based cache policy

Configurable, as described in 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.

Clearing selected datasets manually

Configurable, as described in section Manually Clearing the Cache above.

Cache regions can be defined and cleared programmatically with APIs.

Clearing all datasets manually

Configurable, as described in Manually Clearing the Cache above.

In JasperReports Server, select Manage > Server Settings, then select OLAP Settings and click Flush OLAP Cache. For additional methods, see the Jaspersoft OLAP Ultimate Guide.

Disabling the Ad Hoc Cache

There are two reasons to consider disabling the Ad Hoc cache:

To disable the Ad Hoc cache for Topics and Domains, set the value of maxBytesLocalHeap to 1 (1 byte). For instructions, see Configuring the Cache. This effectively turns off the cache so that every query is retrieved directly from the data source.

To disable the OLAP cache for OLAP connections used in the Ad Hoc Editor, check the mondrian.rolap.star.disable-Caching setting on the Manage > Server Settings > OLAP Settings page. For more information, see the Jaspersoft OLAP User Guide.

In addition, if you have modified any of these three properties in applicationContext-adhoc.xml, set them back to false:

<property name="applyQueryFilterInMemory" value="false"/>

<property name="applySecurityFilterInMemory" value="false"/>

<property name="applyDynamicFilterInMemory" value="false"/>

Ad Hoc OLAP Filter Configuration

When using filters in Ad Hoc OLAP, the server queries the database to display a list of values to select from. To avoid performance issues, the number of items in a filter is limited. By default, the limit is 250 possible values.

If your filters reach this limit and your list of values is truncated, you should first consider using a different filter operation. For example, instead of “city is one of <list>,” use “City starts with <letter>.” If you still need to change this limit, modify the following property: