Oracle RDF Report to XML Publisher Report

Nowadays I am working in a project that requires few existing RDF reports to be converted to XML Reports. So I thought of sharing the little knowledge that I have gained while working on this project.

First question that would come to anyone's mind is :
Why do we need to convert RDF eports to XML?

Successful migration of RDF report to BI Publisher will allow the customer to take avantage of the many benefits found in BI Publisher including:

• · Access to heterogeneous databases
• · Zero footprint, fully web based architecture
• · Low cost of setup and Administration
• · Simple reports development and low cost of reports maintenance
• · Wide range of delivery support (Printer, Fax, Email, WebDAV, FTP/HTTP)
• · Multi-language support
• · Performance improvement
• · Improved Scheduling functionality
• · Data Caching
• · Security enhancements

Oracle XML Publisher is a template-based publishing solution delivered with the Oracle E-Business Suite. It provides a new approach to report design and publishing by integrating familiar desktop word processing tools with existing E-Business Suite data reporting. XML Publisher leverages standard, well-known technologies and tools, so you can rapidly develop and maintain custom report formats.

The flexibility of XML Publisher is a result of the separation of the presentation of the report from its data structure. The collection of the data is still handled by the E-Business Suite, but now you can design and control how the report outputs will be presented in separate template files. At runtime, XML Publisher merges your designed template files with the report data to create a variety of outputs to meet a variety of business needs, including:

• • Customer-ready PDF documents, such as financial statements, marketing materials, contracts, invoices, and purchase orders utilizing colors, images, font styles, headers and footers, and many other formatting and design options.
• • HTML output for optimum online viewing.
• • Excel output to create a spreadsheet of your report data.
• • "Filled-out" third-party provided PDF documents. You can download a PDF document, such as a government form, to use as a template for your report. At runtime, the data and template produce a "filled-out" form.
• • Flat text files to exchange with business partners for EDI and EFT transmission.

So now we know the advantages of converting the RDF to XML Publisher report. But how do we go about it?
What are the steps involved in the RDF to XMLP report Conversion Process?

The overall flow can be diagrammatically represented by the following figure:

Pre-Requisites

The migration APIs accept an Oracle Report in XML Format, this format is only supported in Oracle Reports 9i and above. Therefore you need to have a 9i+ version of Oracle Reports available. For R12 the Oracle Reports version in the techstack is 10.1.3 so the conversion to RDF XML is straightforward. For Release 11i the version is 6i, so you will need a higher version of Oracle Reports available somewhere on your system.

To get the Oracle Report in XML format from rdf format, either use Oracle Report Designer or Oracle Report rwconverter.exe utility under $ORACLE_HOME/bin

Example
D:\Oracle_home\BIN>rwconverter batch=yes source= h:\reports\brvac.rdf dest= h:\reports\brvac.xml dtype=xmlfile overwrite=yes

This will convert the binary RDF file into an RDF-XML format that can then be consumed by the conversion APIs.

This looks confusing isn't it.
What are the exact steps involved that we need to perform?

1.Data Model Migration

Use DataTemplateGenerator API to migrate the Oracle Reports Data Model to a DataTemplate and associated PL/SQL logic to PL/SQL Package (specification and body).

The API can be called through the command line or through a shell script. This will generate following output files:

• DataTemplate (REPORT.xml)
• Default PL/SQL package specification (REPORTS.pls)
• Default PL/SQL package body (REPORTB.pls)

Example :
javaw.exe oracle.apps.xdo.rdfparser.DataTemplateGenerator H:\report\brvac.xml

Output files:

1. PL/SQL Package:H:\ report\brvacS.pls
2. PL/SQL Body:H:\report\brvacB.pls
3. DataTemplate:H:\report\brvac_template.xml

2. Layout Migration

Use RTFTemplateGenerator API to migrate the Oracle Reports layout to an XML Publisher RTF template.

Since there is no support of PL/SQL in RTF Template, the process does not migrate any format trigger logic present in the report. Instead the generator writes all the format trigger code to log file. You will need to implement any corresponding PL/SQL logic as XSL code. The majority of Oracle Reports use simple ‘if’ formatting logic that can be converted relatively easily. To aid in this process, the resulting RTF template will contain formfields that hold the format trigger names that are called, these fields will be highlighted in red. You can then refer to the log to find the actual PL/SQL code used in the original Oracle Report.

The API can be called through the command line or through a shell script. This will generate following output files:

• RTF Template
• Log file

Example :
javaw.exe oracle.apps.xdo.rdfparser.RTFTemplateGenerator H:\report\brvac.xml

Output files:

1. RTF Template: H:\ report\ brvac.rtf
2. Log File : H:\report\brvac.log

Does that mean, these two steps exactly converts the RDF to the required XMLP report without hassles?

No, its not really that simple though it is not tough either. There are some open issues that we need to take care of. Here are they:

Known Issues:

1. Some times, because of complexity of Oracle Report the, Data Template or PL/SQL has some minor errors and requires manual correction.
2. Format triggers are not supported. The format trigger logic should be implemented separately though XSLT
3. If formula column reference the summary column as parameter and the summary column belongs to same Data Source/Data Query, this implementation is not supported in Data Template. This is because of all the formula columns moved to select statement and the summary column value is not available while executing the formula.

So can we have a complete example with sample data definition and template files?
Surely, we can have a complete example with required data definition and template file.
But not today or else I would be breaching the confidentiality agreement of my company. Because as of now I only have the reports on which I have worked as a part of the project. These are company exclusive. Very shortly, I shall create a rather simple report easy to understand for beginners and not related to the company I am working for. Then I shall upload the sample data definition and template files with all required screenshots and outputs.

Till then, bye bye and have a nice day.