Excel template XML Publisher report – data exceeding more than 65k rows

There was a requirement from client to build a report using xml publisher 5.6.3 for getting Purchase order commitments. The intention is to use excel template to achieve the table formatting such as auto expansion of height and width for a cel, and adding headers and company logos are achieved in excel template using Alt-text option. During the time when report was built and tested the total row count in the outcome of the report was less than 50,000 records, we never had a thought of going with csv file because excel is feasible with presenting data in required fashion and total row count was within the limits of older version of MS excel (.xls template excel 2003). XML Publisher 5.6.3 which is a embedded version in e-business suite R12.1.3 only supports excel 2003 version templates to run reports. After an year, transaction data surged in the system only to affect the report to end in warning status when ran and output is not viewable as .xls file only allows 65k rows in a sheet.

As an interim solution to provide report to user, I had to run the script manually in toad and extract to excel (latest version). An oracle SR was raised for the issue, they only said they stopped to support for this version of bi publisher, and no patches are available for this issue. Although rebuilding the report with csv would fix the issue but user will lose the presentable way of viewing data, i then built a XSL template to overcome this product limitation. With XSL template, we will be able to show all the data with perfect formatting standards as user needs, this solution comes with an additional step to perform to get the concurrent request output file.

When the report is submitted, user needs to give the contacts of recipients who want to receive output file. After the request is completed normal it sends an notification to user with a link to open output file, they then have to right click on the link and click save as, it saves the file to their local machine which then has to be opened with Microsoft excel. This is required because when user clicks on View Output button in the SRS window, it opens an xsl gobbledegook in an internet explorer. This way it doesn’t matter what the record count is, they will get it as they need.

There are options to convert your excel template to xsl template online.

Thanks,

Nithin