JasperSoft Ad Hoc Row Limit

Products

• CA Service Desk Manager
• CA Service Management - Service Desk Manager

Issue/Introduction

When a JasperSoft Ad Hoc Dataset contains more than 200,000 rows, errors similar to the following are seen:

1.  The query for the report has been canceled because it returned more than 200000 rows. Modify the report to reduce the number of rows

2.  Exception calling JRDataSource.next() for query select top 200000.................

Environment

Service Management 17.x

JasperSoft 7.x

All Supported Windows Operating Systems

Cause

For performance reasons, the JasperSoft Ad Hoc Dataset Row Limit is set to 200000 by default

Resolution

There are two ways to change this value, temporary and permanent.

Temporary Solution (reset on server restart - the value would revert to default)

1.  Login to JasperSoft as Superuser.
2.  Go to Manage -> Ad Hoc Settings
3.  Set "Ad Hoc Dataset Row Limit" to desired value.

Permanent Solution

1.  Edit the file applicationContext-semanticLayer.xml file under the WEB-INF directory and change the settings to a larger limit

2.  Near the bottom of the file, locate the bean property

3.  Change the limit value for the setting bellow (default setting is 200000)

4.  Save the change and restart JasperSoft server for the change to take effect