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:
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.