Categories
BI Publisher Configuration Peoplesoft Tools

BI Publisher fix for XLSX Cell Merging Problem in PeopleSoft

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:

Categories
BI Publisher Peoplesoft

Displaying related content on Peoplesoft BI Publisher Crosstab Report

If you need to print related content against each row of a crosstab report, you can do this by assigning an “incontext” variable to the value of the controlling field on the row (typically assigned to “./H”), and then using that as a filter in an XPath query back to the query results e.g.

Assign a variable with the value we are searching for:

<?variable@incontext:case_id;’. /H’?>

And then use the variable in an XPath filter to find (say) a sibling value from the result set. In this case RC_SUMMARY for the given CASE_ID on the row:

<?(/query/row/CASE_ID[text()=$case_id]/../RC_SUMMARY)[1]?>

This query accesses the first RC_SUMMARY for the given CASE_ID in the query results.

Note the importance of the brackets around the query here … ( ) – this ensures we find just one node. Without them we potentially find repeated RC_SUMMARY values for a given CASE_ID.

NOTE: You could use the XPath sibling functions to find the related value(s), but ‘../’ seems somehow more readable to me.