Oracle BI Publisher set to become every accountant’s best friend (Part 2)

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”):

FNDSCRUR_XML_PDF.rtf

FNDSCRUR_XML_XLS.rtf

BIP Template

Use the “XML Publisher Administrator” responsibility to create a Template for the developed layouts:

Submit Concurrent Request

When we go to submit our request, because we’ve linked our Data Definition to two different templates, we now have the option of selecting which output we want to produce:

The program output using the template “Users of a Responsibility (XML) PDF” is:

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

eText Templates

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:

In conclusion

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?!?

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: