Issue

Issues with exporting data to Excel.

The following issues are observed when exporting data to Excel:

• Slight variations in count of records every time user performs an export
• Presence of duplicate records in exported data
• Timeout (504 gateway) errors while exporting data to Excel

Root Cause

The above listed issues occur due to huge data being exported in the form of Excel. It is not just about the number of rows, but also depends on the number of columns, and the amount of data inserted into each cell of the Excel. It also depends on some of the infrastructure factors such as CPU utilization, system performance, and so on.

Solution

Perform the following troubleshooting steps:

1. If the number of columns or rows are not many, verify the properties and confirm if all the properties are optimized for reporting. Review the logs or report definition rule warnings to find out the unoptimized properties. Remove the properties one after another and verify if removal of a specific property makes a huge difference in the performance improvement.
2. If the report is configured to run on descendant classes, then verify if the properties are optimized for descendant classes. Understand the requirements. If the descendant class configuration is not required, then run the report only on the Implementation class.
3. Select the Remove duplicate rows check box for report definition on the General tab and provide a sorting order on the report such that the export batch run can pick up the records in a sequential manner.
4. Verify report definition’s Data Access tab and perform the following steps:

• Increase the value of Maximum elapsed time in seconds to a higher value.
• Increase the value of Maximum elapsed time in seconds for export.
• Increase the value of Maximum number of rows for export.

• Select the Ignore formatting while exporting to Excel option.

5. Verify the global settings for report export and their values by navigating to Configure > Reporting > Settings. Update the following values in the Resource limits when exporting data from reports section:

• Maximum number of rows to retrieve  
• Maximum elapsed time (in seconds) for database query                        
• Number of rows to export at a time

6. In case of 504 gateway timeout issues after a specific duration (For example, 60 seconds), verify the idle timeout at server or Load Balancer level.
7. If the issue persists, then Pega recommends using BIX (Business Intelligence Exchange) extraction. Pega Platform provides some level of reporting though it is not designed to be a reporting tool. BIX is a powerful extraction tool designed to efficiently handle large volumes of data. It enables you to extract, transform, and load (ETL) data from Pega applications into external databases, data warehouses, or files.