Collaborate 14 “Outtake:” Building MS Excel Templates for BI Publisher in Oracle E-Business Suite R12

“So how do I get this into Excel?” There’s probably hasn’t been a conference goer in the history of Collaborate who hasn’t been on one, if not both ends of that question.collaborate 14 day 2It seems like not matter how robust your reporting solution is, no matter how snazzy your graphical interface can be, at the end of the day the suits in the board room are going to ask to have the data presented in Excel.

Having been around this block a few times, we’re happy to do what we can to help conference goers bridge tried and true old fashioned MS Excel format and the latest from Oracle’s BI Publisher in our latest Collaborate 14 Outtake.

collaborate oaug oracle development bi publisher excel xml
Discussing Oracle Development @ Collaborate

Oracle BI Publisher Takes Center Stage

BI Publisher has taken enterprise reporting to the next level. It has made the life of the Oracle developer easy. Oracle has modularized the reporting allowing users to control the “look and feel” of the data in BI Publisher. Dare we say reporting using BI Publisher is now fun as we can even test the reports without logging into the Oracle E-Business Suite by using the BI Desktop utility.

BI Publisher

also provides reporting security to report data by delivering data in xml format. XML is strongly recommended by most of the software giants as the preferred format for data security. It makes things easier for developers by providing the facility to write simple queries to generate the data in the same XML format  and registering it as data definitions.

In general, designing the look and feel of a report is challenging, but BI Publisher has provided lot of features that are useful. It is as simple as installing the BI Publisher desktop add-in for the Excel sheets. With the Excel templates, BI Publisher took the reporting to next level.

Setting Up the Template in Excel 

The first step to getting something from BI Publisher to Excel is to design a template in Microsoft Excel for retrieving and formatting your enterprise reporting data in Excel. Excel templates provide a set of special features for mapping data to worksheets and for performing additional processing to control how your data is output to Excel workbooks.

In general, users love the reports to be in Excel sheets as it helps them in their daily day to day business. The advantages of providing the Excel outputs have changed the reporting as this facilitates the user to use all the available options of Excel sheets.

Key features of Excel templates:

  • Define the structure for your data in Excel output
  • Split hierarchical data across multiple sheets and dynamically name the sheets
  • Create sheets of data that have master-detail relationships
  • Use native XSL functions in your data to manipulate it prior to rendering
  • Use native Excel functionality
  • Supported Output
  • Excel templates generate Excel binary (.xls) output only.

Key Benefits of Publishing from BI Publisher to MS Excel

There are several benefits of this method, the first of which is that your reports are built using the MS Excel, which means you can get decision makers key business data into a format they can use without much knowledge. By the same token, once it is in Excel, you’ve got the freedom to format it in whatever way you need to. And you can do it quickly, as it is in .XML format.

Limitations of Publishing from BI Publisher to MS Excel

As with anything else, there are also limitations to this method and it is important that you understand them going forward. Here are two limitations to keep in mind.

  • First, for reports that split the data into multiple sheets, images are not supported. If the template sheet includes images, when the data is split into multiple sheets, the images will show only on the first sheet.
  • Second, there is no tool to facilitate the markup of the template with BI Publisher tags; all tags must be manually coded. Some features require the use of XSL and XSL Transformation (XSLT) specifications.

Make the Most of Collaborate!

In conclusion, Oracle’s BI Publisher has greatly simplified E-Business reporting.

Of course the devil is always in the details, if you need more input on beefing up the reporting solution that supports your Oracle E-Business Suite, PeopleSoft, or JD Edwards ERP solution then please stop by and visit us at Booth 1049 during Collaborate 14 and ask to speak with one of our experts.

If you’re unable to be in Las Vegas (or your finding this long after Collaborate 14 has ended) then you’re still in luck. First, you can visit our Collaborate 14 Resources Page to download copies of our presentations.

Likewise, we encourage you to check out our Development Services page to see how we can help you bring your reporting to the next level.

Follow the action at Collaborate 14 as it happens! Click here to access all blog entries with our Collaborate tag.