Under PeopleTools 8.53 the default Excel output format for BI Publisher reports is now native XLSX rather than “MHTML” as in previous releases. This has a number of advantages such as a much smaller output file size due to it being a real Excel format and not HTML :-).
The change to this format by default also has some interesting issues with cells and cell merging that are fixed in a BI Publisher patch. The description of the bug is:
16602238 – ER NEW OPTION FOR XLSX OUTPUT TO EXPAND COLUMN TO AVOID CELL MERGING
This patch is included in the embedded BI Publisher class files in PeopleTools 8.53.20. I haven’t as yet worked out which minor patch brought this fix in, but I struggled to easily find details of the “new option” or where to set it. To save others time, there is a new option in the xdo.cfg file to enable the fix. You need to do this in order to remove extra blank columns in BI Publisher output compared to the previous behaviour. The biggest impact this had for my use case was that users were unable to easily create pivot tables from the raw data due to the extraneous blank columns.
The property you need to add to xdo.cfg is “xlsx-keep-values-in-same-column”. Set it to true:
<properties>
<!– System level properties –>
<property name=”xslt-xdoparser”>true</property>
<property name=”xslt-scalable”>true</property>
<property name=”system-cachepage-size”>50</property>
<property name=”system-temp-dir”>D:\temp</property>
<property name=”xlsx-keep-values-in-same-column”>true</property>
</properties>
No need to bounce any servers – next BI Publisher report will pick up the new config setting.
Note: You can override the output Excel format to the old MHTML (.xls) at the report level within the report definition in PeopleSoft if you still find issues after this change.