Oracle BI Publisher set to become every accountant’s best friend (Part 1)
September 21, 2011 Leave a comment
by Kenny Miller, Principal Consultant for Rocela
Accountants love spreadsheets! Retrieving data from Oracle E-Business Suite (EBS) to Excel has never been easy and so in the past, most customers have cobbled together their own solutions for doing this such as CSV, macro’s etc.
So how can Oracle “standard” reports in R12 be easily converted to output to an Excel spreadsheet? BI Publisher makes it much easier to do – and the new “True Excel” functionality is a powerful new feature.
BI Publisher (“Business Intelligence Publisher”, also known as BIP and XML Publisher) has been available in EBS R11 for a number of years. Oracle Reports is no longer available in Oracle Fusion, where BIP becomes Oracle’s de-facto reporting tool for EBS, therefore BIP is being increasingly used in EBS R12.
BIP has always been capable of forcing spreadsheet programs to open its output, but this was not in a true binary file format, rather XHTML output was used instead. This relies on spreadsheet programs being able to convert XHTML into a viewable spreadsheet format, something Excel (from version 2003 onwards) is able to do.
However, there are significant limitations to this approach – primarily an inability to fully leverage the formatting and formula functionality in spreadsheets. Also, output was limited to a single spreadsheet tab.
With very little fan-fare, Oracle recently added new Excel functionality to 11g BIP. Oracle calls this functionality “True Excel” – templates are developed using Excel, and can more fully leverage the functionality of Excel. With even less fan-fare, Oracle have back-ported this functionality for BIP 10g in both EBS R11 and R12.
The most significant difference between BIP and traditional reporting tools (such as Oracle Reports and SQL*Plus) is that BIP completely separates the selection of source data from the formatting of the data layout for presentation.
The theory is that while data selection remains a task for a technical IT resource, the layout of the data can be done by a functional resource. In practice, complex data layouts have remained a task for a technical IT resource, with functional resources only capable of minor changes such as fonts, boiler-plate text and logos.
However, one real advantage of the separation is that multiple layouts can be developed for a single selection of source data. Once these formats are defined in the BIP application, and are linked to the relevant concurrent program, EBS users have the ability to select which format they want when submitting a concurrent request.
Therefore, to partly answer the question, EBS users can easily select at run-time to output a concurrent program request in Excel format, BUT only if an Excel layout has been developed for the concurrent program.
In my next blog I’ll go on to show you how to practically apply this new functionality, by running through some report conversion examples.