BI Publisher report fails to generate XLSX output in financial accounting module

ERP Oracle JD Edwards
, , , , , , , ,
1

We’re experiencing consistent failures when trying to generate XLSX output from BI Publisher reports in our financial accounting module (JDE 9.2.1). PDF and HTML outputs work fine, but XLSX generation fails every time with a format error.

Error message from BI Publisher:


XML-22021: Error parsing XML template
Output format XLSX generation failed at row 2847
Template validation error: Invalid cell reference

The report definition settings appear correct, and we’ve verified the BI Publisher patch level is current. The same report template worked in our test environment last month before we promoted to production. Has anyone seen XLSX-specific output format issues in BI Publisher with financial reports?

2

Based on the error occurring at row 2847 specifically, this is definitely a data-related issue rather than a template structure problem. Here’s the systematic resolution:

BI Publisher Patch Level Verification:

First, confirm you have the minimum required patches:

  • BI Publisher 12.2.1.4 (you have this)
  • Patch 32545771 for XLSX large dataset handling
  • Patch 33564892 for special character encoding in financial reports

Verify these patches are applied by checking the BIP admin console > About section. The second patch is critical for financial data.

Report Definition Settings Analysis:

  1. Template Encoding: Open your RTF template in BI Publisher Template Builder. Go to Properties and verify:
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0">

The encoding MUST be UTF-8 for XLSX output with financial data.

  1. Cell Reference Validation: In your template, check for dynamic column generation using for-each loops. If you’re creating columns based on account segments, add this constraint:
<xsl:if test="position() &lt;= 16000">

This prevents exceeding Excel’s column limit.

Output Format Configuration Fix:

  1. Special Character Escaping: The most common cause of “Invalid cell reference” at specific rows is unescaped special characters in your data. Add this to your template header:
<xsl:template name="escape-special">
  <xsl:param name="text"/>
  <xsl:value-of select="translate($text, '&lt;&gt;&amp;', '')" disable-output-escaping="yes"/>
</xsl:template>

  1. Report Definition Update: In JDE, go to your report definition (P95620) and modify the XLSX output properties:

    • Set “Enable Data Validation” to False
    • Change “Cell Format” from “Automatic” to “Text” for account number columns
    • Increase “Memory Allocation” to at least 512MB for large financial reports

  2. Row 2847 Investigation: Since the error occurs at a specific row, export your data to XML and examine row 2847:

    • Check for null values in required fields
    • Look for account descriptions with special characters (&, <, >, ", ')
    • Verify numeric amounts don’t have text characters
    • Check date formats are consistent (YYYY-MM-DD)

Testing Procedure:

  1. After making template changes, test with a filtered dataset first:

    • Limit output to 1000 rows using report parameters
    • Verify XLSX generates successfully
    • Gradually increase row count to identify threshold

  2. If the filtered report works, the issue is definitely data-related in rows beyond your test range. Query your source data around row 2847 to find the problematic record.

Production Resolution:

  1. For immediate production use, implement a data cleansing step:

    • Create a pre-processing SQL that escapes special characters in account descriptions
    • Add validation to reject records with invalid characters before report generation
    • Consider splitting large reports into multiple XLSX files (one per account range)

  2. Long-term fix: Update your financial data entry validation to prevent special characters in account descriptions. This prevents future XLSX generation issues.

The difference between test and production is likely the volume of accounts with special characters in descriptions. Test had clean data while production has legacy accounts with unescaped ampersands or XML-invalid characters that break XLSX cell references.

3

You can enable BIP debug logging to see the exact XML being generated before XLSX conversion. Add this to your xdo.cfg file temporarily: <property name="debug-mode">true</property>. The debug output will show you how many columns are being created from your XML data. Also check if there are any special characters in your account segment values - XLSX has strict naming rules for cell references that XML/HTML don’t care about.

4

I’d also check the BI Publisher version compatibility. JDE 9.2.1 requires BI Publisher 12.2.1.3 or higher for proper XLSX support. Run this query to verify your BIP version and compare against Oracle’s compatibility matrix. Sometimes patches update JDE but don’t update the corresponding BIP components, causing format-specific failures like this.

5

We encountered this last year with our financial reports. The issue was special characters in account descriptions (specifically ampersands and less-than symbols) that weren’t properly escaped in the XML template. These characters are fine in PDF rendering but break XLSX cell references. Check your template’s XML encoding settings and make sure special characters are being escaped properly for Excel output.