How to Use XML Publisher to Generate Oracle Reports in Excel and PDF

In the past, Oracle E-Business Suite Customers seldom used outputs of the seeded Oracle reports because they were unfriendly, and frankly, hard to use. Unfriendly because the generated output was always fixed width courier only font, contained no graphics, and could only be in black and white. Hard to use because the only output was text. In the business world, we all know how important the final output is and text only is not even an option. Businesses require PDF and XLS output that support tables, charts, graphics, color, etc.

Today, Oracle offers a solution that enables our Oracle E-Business Suite Customers to turn the once unfriendly Oracle seeded reports into PDF and XLS outputs of color, graphics, and tables. The solution is XML Publisher. XML Publisher is a java based reporting tool that is bundled for free within the technology stack of the Oracle E-Business Suite. The concept is similar to that of “mail-merge” where the data and template are maintained separately until the two are “married” to generate the desired document. XML Publisher separates a report’s data and layout component into two manageable pieces while it supports the desired outputs of PDF, HTML, XLS and RTF. The prerequisites to use XML Publisher include:

• XML Data
• Report Template
• Registration of Data Definition and Template at XML Publisher

How does XML Publisher leverage on Oracle seeded reports? An Oracle seeded report is a report definition file that integrates both the data query and the report layout into one file. XML Publisher will use Oracle Reports for only the data query portion and will ignore the defined report layout. This will fulfill one of XML Publisher’s prerequisites: XML Data. So the question is, how do you set up the Oracle system to only use the data query portion of the Oracle report?

• Log into Oracle with System Administrator Responsibility
• Navigate to > Concurrent > Program > Define
• Search for the desired Oracle Report
• Change the Output Format from the default ‘Text’ to ‘XML’
• Take special note of the Program Short Name (you’ll need it later to register the data at XML Publisher)

How does it benefit Oracle E-Business Suite customers? XML Publisher provides full control of the layout and look and feel of the report to the Customers. This means the Customer will fulfill the second prerequisite, the Report Layout. Customers will leverage on familiar tools like Microsoft Word and Adobe Acrobat to create the report layout with reference to the XML Data Tags. So the question is, how does the Customer create the Report Layout? PDF Forms template are intended for Customers that require a fixed look and feel such as completed IRS forms and are created in Adobe Acrobat. RTF templates are intended for all other purposes and are created in Microsoft Word. In this example, we’ll go over how to create an RTF Template.To create an RTF Template, you’ll want to download the XML Publisher Desktop Utility and its prerequisite Java Runtime Environment. This enables you to create and test your Report Template in a localized environment, your computer, before registering it at Oracle XML Publisher. So where to download the XML Publisher Desktop Utility?

• Navigate to Oracle eDelivery Site
• Select Product Pack: Oracle Application Server Products
• Select Platform: MS 32 bit
• Click on the GO Button
• Select and Download Oracle XML Publisher Release 5.6.2 Media Pack for Microsoft Windows

The XML Publisher Desktop Utility is a plug-in to Microsoft Word. To create the RTF template, you must first load the XML Data File by clicking on the Data button and selecting Load XML Data. To get a sample XML data file, you can run the Oracle seeded report and download the output. Once the data file is loaded, you can make references to the XML Data elements. For example, if you want to reference the ‘Project_Number’ XML Data Element, the format you would type would be … The common used tags include:

• for-each | end for-each
• if | end if
• choose | when | end when | otherwise | end otherwise | end choose

Alternatively, you can use the Desktop Wizard functionality by clicking the Insert button and selecting Table/Form to drag and drop the desired XML Data Elements into the Template window. This will automatically generate a table for you.
You can test your RTF template with the loaded XML Data File by click on the Preview button and selecting one of the supported output formats. Once satisfied with the created RTF Template, the final steps is to register both the Data Definition and the Template at XML Publisher. The steps are:
Register the Data Definition:

• Log into Oracle with XML Publisher Administrator responsibility
• Navigate to > Home > Data Definitions
• Click on the Create Data Definition button
• The Code must be the Concurrent Program Shortname from above

Register the Template

• From Data Definitions, Navigate to > Templates
• Click on the Create Template button
• The Data Definition value should be the Data Definition you registered above

When the Customer runs this configured report through the Concurrent Manager, the Customer will follow the same steps to run the standard Oracle Report by selecting the report in the Single Request Screen and entering the desired run parameters. Now, the Customer has one additional option to select the desired output format of the report. From the Single Request Screen, click on the Options button and select the desired output.