ExportToExcel in background incorrect
We have a business requirement to export a report (from portal) to Excel in foreground (normal) mode when the result count is small and in background mode when large. We don't use scheduled reports as the requirement is to export the report on-demand.
We have a report with columns containing Hungarian, Weish, French or other language names. These names appear as it is in the end user portal and also when exported to excel in 'normal (foreground)' mode.
The same report when exported in background, some of the characters are replaced with html tags or other non-printable characters.
Interestingly, the background export looks good in VM instance (from Pega Academy) but incorrect in a non-VM instance, for the same Pega version (tested in 7.1.9, 7.2)
Steps to Reproduce
Create a report that has result of names as in the attached file ExportToExcel_names.txt
In the report result set, choose 'Export to Excel' and save this file to desktop. This file should have names matching the original source exactly the same.
Now modify the out of box activity (in private edit mode)
Rule-Obj-Report-Definition
ID -> pzViewExportToExcel
RS -> Pega-Reporting:07-10-15
Comment the step 'Call pzViewExcelData'
Set Param.fileLocation to a file location on the server
and set Param.reportRunMode == "background" (or you can disable the WHEN condition on the java step)
We have a business requirement to export a report (from portal) to Excel in foreground (normal) mode when the result count is small and in background mode when large. We don't use scheduled reports as the requirement is to export the report on-demand.
We have a report with columns containing Hungarian, Weish, French or other language names. These names appear as it is in the end user portal and also when exported to excel in 'normal (foreground)' mode.
The same report when exported in background, some of the characters are replaced with html tags or other non-printable characters.
Interestingly, the background export looks good in VM instance (from Pega Academy) but incorrect in a non-VM instance, for the same Pega version (tested in 7.1.9, 7.2)
Steps to Reproduce
Create a report that has result of names as in the attached file ExportToExcel_names.txt
In the report result set, choose 'Export to Excel' and save this file to desktop. This file should have names matching the original source exactly the same.
Now modify the out of box activity (in private edit mode)
Rule-Obj-Report-Definition
ID -> pzViewExportToExcel
RS -> Pega-Reporting:07-10-15
Comment the step 'Call pzViewExcelData'
Set Param.fileLocation to a file location on the server
and set Param.reportRunMode == "background" (or you can disable the WHEN condition on the java step)
Now, run the 'Export to Excel' on the report result again. Fetch the exported file from the server and open in excel application and note that the names don't match with the original source.
Note that we are private editing the OOTB activity pzViewExportToExcel only to set the Param.reportRunMode internally to reproduce the bug. The activity itself has code to function for background (async) export, we are not adding any new functionality to this activity.
A solution suggested by Pega Support is to edit the java code in the activity to hardcode 'UTF-8'. I am not in favor of this suggestion as encoding should be set at JVM/OS level and hardcoding in an activity java step will break when we get data with different encoding.
Any help in understanding why the OOTB activity pzViewExportToExcel doesn't export right for Param.reportRunMode == "background" but looks correct in "nomal (foreground)" would be highly appreciated.
***Updated by moderator: Lochan to add SR Exists group tag***
Accepted Solution
Pegasystems Inc.
US
To fix Export to Excel data - Pega support provided the patches HFIX-29522, HFIX-30654. Basically this has the activity pzViewExportToExcel use UTF-8 encoding
The exported data looks good after the above patches. However, the pdf format had some question mark (?) in places of fonts. This happens when the pd4ml library doesn't have the desired font. The below 2 articles helped resolve the issue:
https://community.pega.com/support/support-articles/chinese-characters-not-being-exported-pdf
https://community.pega.com/pegatube/support-guide-troubleshooting-cyrillic-script-not-appearing-generated-pdf
Pegasystems Inc.
US
This looks like a bug with pzViewExportToExcel. In line 10 of the Java step, the Java standard method String#getBytes(String) method is used, which expects the name of a valid charset to be passed in. According to the Java 7 String documentation, however, "UTF8" is not one of them. getBytes() is instead falling back to a default character encoding, which appears to be ISO-8859-1.
It may be possible to avoid the issue by setting the default encoding to UTF-8 via a JVM argument or OS setting, but this may only cause more problems with your system.
There might be a way to fix the file programmatically after export, but it would present a major performance problem. The only way to fix this issue without changing the JVM or OS settings is to modify the Java step. Since this is a bug with the activity, you should seek a hotfix which includes this small change.
Pegasystems Inc.
US
I found the same bug when the report is run as scheduled report too, OOTB. The support team has owned the bug and is working on hotfix. Will keep posted on the fix.
Accepted Solution
Pegasystems Inc.
US
To fix Export to Excel data - Pega support provided the patches HFIX-29522, HFIX-30654. Basically this has the activity pzViewExportToExcel use UTF-8 encoding
The exported data looks good after the above patches. However, the pdf format had some question mark (?) in places of fonts. This happens when the pd4ml library doesn't have the desired font. The below 2 articles helped resolve the issue:
https://community.pega.com/support/support-articles/chinese-characters-not-being-exported-pdf
https://community.pega.com/pegatube/support-guide-troubleshooting-cyrillic-script-not-appearing-generated-pdf