Limiting the number of Excel records in a report

By default, a report exported to Microsoft Excel exports a maximum of 20,000 records. If the number of records is too large, you can set a limit of the number of records for the query to retrieve.

About this task

Attention: Export intended for individual users, automated exports might cause performance delays:
  • Export to Microsoft Excel is designed for individual users to export data occasionally. It is not designed to be part of an automated Extract-Transform-Load (ETL) process to transfer large amounts of data out of Lifecycle Query Engine for other uses.
  • Jazz Reporting Service Report Builder's default query.results.excel.limit=20000 should not be increased for the Report Builder querying your primary Lifecycle Query Engine server. If this recommendation is not followed, users might experience performance delays and server instability.
  • If you need to export large amounts of data on regularly, use a second Lifecycle Query Engine server and Report Builder for this purpose, leaving the primary Lifecycle Query Engine server and Report Builder to provide uninterrupted service to everyday users.
  • If you use a second Lifecycle Query Engine for this purpose, monitor Lifecycle Query Engine performance and memory use, and if necessary adjust the second Report Builder's query.results.excel.limit, the complexity of the reports, and/or the frequency at which they are run.
Note: Considerations when upgrading from 6.0.6.1:
  • In 6.0.6.1, if you set the query.results.excel.limit property to a certain value, after you upgrade to 7.0, this setting is preserved and the value will continue to be valid in 7.0.
  • In 6.0.6.1, if you did not set this property, the number of records exported is 5,000. After the upgrade to 7.0, since no value is set, the number of records exported is the default value of 20,000 records.

Procedure

  1. On your Jazz Reporting Service system, open the JRS_install_dir\server\conf\rs\app.properties file in a text editor.
  2. Find the query.results.excel.limit property and set a value with the limit.
    For example, to limit the query to 1000 records, set
    query.results.excel.limit=1000
  3. Save your changes and restart the server.