Oracle BI Publisher set to become every accountant’s best friend (Part 2)
October 7, 2011 Leave a comment
by Kenny Miller, Principal Consultant for Rocela
In my last post (Part I – Oracle BI Publisher set to become every Accountants best friend – 21st September 2011), I spoke about the newly added Excel functionality Oracle introduced into IIg BIP, otherwise known as ‘True Excel’ and the advantages that this brings to Oracle EBS users.
So in this post, I’ll run through some report conversion examples.
Data selection can achieved with any tool that produces XML output. It’s easy to convert any standard Oracle Reports concurrent program – simply change the output format of the program to XML (see example below). This is the simplest method for generating XML output for concurrent programs, but PL/SQL, SQL*Plus, and Java Concurrent Programs can also be used.
Report Conversion Example
There is no need to convert all existing custom reports to BIP – these will continue to run in R12 (assuming that the impact of any database changes are resolved).
However all payment format output (e.g. BACS and cheque prints) MUST be converted from reports to BIP. The payment functionality in Payables has been replaced in R12 with a new Payments application which only uses BIP to generate report output.
The following is a simple example of how to convert a standard program.
Create a custom Concurrent Program
Copy the program, changing the Output Format to XML:
Add to Request Group
The new program needs added to a request group, just as any other concurrent program does:
The request should now be run to generate XML output (this will then be used to develop the layout of the report):
BIP Data Definition
Use the “XML Publisher Administrator” responsibility to create a Data Definition. By using the same name for both the Concurrent Program Short Name and the Data Definition (in this example “FNDSCRUR_XML”) our concurrent program becomes a BIP concurrent program.
Microsoft Word Desktop Template Builder
The Template Builder installs as a plug-in to Microsoft Word, and is used to develop the report layout. Describing how to develop the layout is beyond the scope of this document. However, the Template Builder includes a comprehensive User Guide:
The following two layouts were developed, one designed to produce PDF output, and the other designed to produce Excel output (XHTML – not “True Excel”):
At the time of writing, only the 11g Template Builder can be used to build “True Excel” templates for R11 and R12. However, only the 10g Template Builder can be used to develop RTF templates for R11 and R12.
Unfortunately, the 10g and 11g Template Builders can’t both be installed on a machine at the same time, meaning anyone who will be developing both “True Excel” and RTF templates will either need to install/de-install as required, or install in multiple separate machines or virtual machines.
Chapter 6 of the “Report Designer’s Guide for Oracle Business Intelligence Publisher” describes how to create a “True Excel” template. See http://download.oracle.com/docs/cd/E21764_01/bi.1111/e13881.pdf for more details.
Normally the layout for BI Publisher reports is created using a RTF template. Another type of template for BI Publisher is eText. This gives precise control over where each character appears in the output, and is used to produce the output for interface files e.g. payment output files. An example of an eText template is shown below:
To sum up, BI Publisher makes reporting directly from EBS to Excel much easier than it’s ever been before. Simple tabular reports can be easily created using the XHTML approach, but why not take some time to fully explore the benefits of “True Excel”? For those that haven’t used BI Publisher before then hopefully the simple example shown above removes any “fear of the unknown”.
BI Publisher may well be Oracle’s reporting tool of the future, but it’s here right now, and it’s not difficult to use. Why not take advantage of it?!?