Converting Reports from Oracle Reports to Oracle BI Publisher

This appendix covers the following topics:

Overview
Prerequisites
Running the Conversion Utility
Uploading the PL/SQL Package to the Database
Moving Converted Reports to the Oracle BI Publisher Reports Repository
Testing and Editing Converted Reports

Overview

Oracle BI Publisher provides a utility for converting reports from Oracle Reports to Oracle BI Publisher.

In Oracle Reports, the data model (SQL query or extraction logic) and report layout specifications are contained in a single file. In Oracle BI Publisher the data model and the layout are separate objects. The conversion utility therefore generates several files from a single Oracle Report file that will make up your report in Oracle BI Publisher. In most cases this file set will include a PL/SQL specification and body that you will need to create in the database that contains the data for the report. The utility will also generate a report definition file and a layout template file that you will upload to the Oracle BI Publisher repository.

Once uploaded, test the report to ensure that the output is as expected and make any changes to the report as needed. Some reports will contain structures that the utility cannot convert. These must be manually implemented in the converted reports.

The overall flow for the conversion process is as follows:

Run the conversion utility.
Load the PL/SQL package into the database.
Upload the report to the Oracle BI Publisher repository.
Test the report and check the conversion log files to identify any manual modifications needed to complete the conversion.

Prerequisites

The following are prerequisites for running the BI Publisher Oracle Reports conversion utility:

You must be running JDK version 1.1.8 or later.
Your source Oracle Reports must be in XML format.
Your CLASSPATH must include the required JAR files.

Source Oracle Reports Must Be in XML Format

The conversion utility requires that your source Oracle Report be in XML format. Oracle Reports XML format is supported in Oracle Reports 9i and above. If your source reports are not in Oracle Reports XML format, the conversion utility can perform this conversion automatically. For the utility to perform the conversion the following is required:

Oracle Reports Designer 9i or later installed on the same machine as the conversion utility

The conversion utility must be able to call the rwconverter executable to get the reports into Oracle Reports XML format.

Oracle Reports Designer is part of the Oracle Developer Suite 10g (10.1.2.0.2) available from:

http://www.oracle.com/technology/software/products/ids/index.html

Update the Class Path with Required JAR Files

To run the conversion utilities you need the following files defined in your class path:

Collections.zip
xmlparserv2-904.jar
xdocore.jar
aolj.jar

For customers using Oracle E-Business Suite, these libraries or the corresponding classes are available under JAVA_TOP. For customers using Oracle BI Publisher Enterprise, all these libraries are available under WEB-INF\lib. A sample path to WEB-INF\lib is as follows:

C:\Oracle\bi\oc4j_ bi\j2ee\home\applications\xmlpserver\xmlpserver\WEB-INF\lib

Running the Conversion Utility

After you have updated your class path, you can run the conversion utility from the command line. The utility is called

BIPBatchConversion

Command line usage is as follows:

java ... BIPBatchConversion [-debug] -source <SourceDirectory> -target
<TargetDirectory> [-oraclehome <OracleHomePath>]

where

-source - (required) is the source directory for the Oracle Reports files. All reports must be in the same format: either RDF or XML.
-target - (required) is the target directory to create Oracle BI Publisher report objects. The converted objects will include the Oracle BI Publisher Report file (.xdo), the layout template file (.rtf), the PL/SQL package, and log file.
-oraclehome - (conditional) If your reports are in Oracle Reports XML format do not specify this parameter.

If your reports are in Oracle Reports RDF format, BIPBatchConversion requires rwconverter from Oracle Reports to convert the report from RDF format to XML format. Specify the Oracle home path where Oracle Report Designer (9i or later version) is installed. BIPBatchMigration assumes that rwconverter is contained in the bin directory beneath the Oracle Home path specified.
-debug - (optional) Specify this parameter to run the utility in debug mode and write debug statements to the log file.

Source Report Is an Oracle Reports RDF File

This example requires you to specify the -oraclehome path.

java.exe -classpath
D:\Jdev\project\xdocore.jar;d:\Jdev\project\collections.zip;d:\Jdev\project\aolj.j
ar;d:\Jdev\project\xmlparserv2-904.jar
oracle.apps.xdo.rdfparser.BIPBatchConversion -source d:\reports\pay -target
d:\reports\pay\output -oraclehome D:\oracle\BIToolsHome_1 -debug

Source Report Is in Oracle Reports XML Format

Do not specify the -oraclehome parameter in this example.

java.exe -classpath
D:\Jdev\project\xdocore.jar;d:\Jdev\project\collections.zip;d:\Jdev\project\aolj.j
ar;d:\Jdev\project\xmlparserv2-904.jar
oracle.apps.xdo.rdfparser.BIPBatchConversion -source d:\reports\pay -target
d:\reports\pay\output -debug

See the Oracle BI Publisher 10.1.3.3 Java API Reference (Javadoc) for more details on this utility and other related conversion APIs in the rdfparser package.

Output Files

The conversion utility will generate the following output files in your target directory for each converted report:

Report definition file (format: REPORT.xdo) that includes the Data Model.

Note: This file is not needed for E-Business Suite users; see the following note under Data Template.
A data template file (format: REPORT_template.xml) that defines the data model.

Note: This is not required for Oracle BI Publisher Enterprise users because the data template is embedded in REPORT.xdo.
The default PL/SQL package specification (format: REPORTS.pls).
Default PL/SQL package body (format: REPORTB.pls).
The RTF Layout Template (for example: REPORT.rtf).
A log file (format: REPORT.log).

For example, assume you have a report called invoice.rdf located in D:\reports\pay. Running this command:

java.exe -classpath
D:\Jdev\project\xdocore.jar;d:\Jdev\project\collections.zip;d:\Jdev\project\aolj.j
ar;d:\Jdev\project\xmlparserv2-904.jar
oracle.apps.xdo.rdfparser.BIPBatchConversion -source d:\reports\pay -target
d:\reports\pay\output -oraclehome D:\oracle\BIToolsHome_1 -debug

Will generate the following output files:

PL/SQL Package Specification: C:\BIPublisher_reports\ invoice\invoiceS.pls
PL/SQL Package Body: C:\BIPublisher_reports\ invoice\invoiceB.pls
Report definition file: C:\BIPublisher_reports\ invoice\invoice.xdo
DataTemplate: C:\BIPublisher_reports\ invoice\invoice_template.xml
RTF Layout Template: C:\BIPublisher_reports\invoice\invoice.rtf
Log file: C:\BIPublisher_reports\invoice\invoice.log

Uploading the PL/SQL Package to the Database

Many converted Oracle Reports will generate a PL/SQL package specification file and a PL/SQL package body file as follows:

<report_name>S.pls
<report_name>B.pls

Run the PL/SQL package files against your Oracle Database as follows. This will create the PL/SQL package specification and body.

SQL> @C:\BIPublisher_reports\invoice\invoiceS.pls 
SQL> @C:\BIPublisher_reports\invoice\invoiceB.pls

Moving Converted Reports to the Oracle BI Publisher Reports Repository

Making your reports visible in the Oracle BI Publisher repository is a two-step process:

Copy the report folders into the repository.
Refresh the repository metadata.

If you have a file-based repository, copy the report folder structure to the file system. For example, suppose your repository path was set to:

C:\oracle\bi\xmlp\XMLP

For the report in the previous example, simply copy the report directory and all its files to the desired folder structure in the existing report repository.

For example:

> copy C:\BIPublisher_reports\invoice C:\oracle\bi\xmlp\XMLP\Shared Folders\Converted reports

If you are using the XML DB functionality of the Oracle Database as your report repository, there are several ways you can upload content. One method is through WebDAV. For options and details on how to load reports into your XML DB based repository, please refer to the following topics in the Oracle XML DB Developer's Guide:

Loading XML Content into Oracle XML DB
FTP, HTTP(S), and WebDAV Access to Repository Data

Once you have loaded report directories into your repository, log on to Oracle BI Publisher with administrator privileges and select "Refresh Metadata" from the System Maintenance Section of the Oracle BI Publisher Admin page. Now all reports should be available for testing and execution.

Testing and Editing Converted Reports

After you have successfully converted your reports, created the needed PL/SQL package, and moved the report definition and RTF template files into the Oracle BI Publisher repository, you should test your reports to make sure they are providing the data and formatting desired. Most converted reports will run as expected without further modification. More complex reports may require some additional modification to work as desired. Following are some common issues with converted reports.

Summary Columns Moved to the select Clause

Occasionally when converting a more complex Oracle Reports report, the Data Template or PL/SQL may contain minor errors and require manual correction. The conversion utility will move all formula columns to the select clause of the SQL query in the data model. In most cases this will not cause a problem. However, if any argument to the formula is a summary column, this will not work because the summary column will not be calculated at the time the query is executed.

To correct this problem you will need to remove this formula from the select clause and implement the formula as XSL in your layout template. Most of these formulas are used either for simple addition or summation or currency conversion, formatting, and rounding.

PL/SQL Format Trigger Logic Not Supported in RTF Layout Templates

The majority of Oracle Reports reports use simple "if" formatting logic. The conversion utility automatically converts this logic to equivalent XSL-FO and inserts the code into form fields in the RTF layout template. However, there is no support for PL/SQL in RTF layout templates. The conversion utility does not convert any PL/SQL format trigger logic present in the report. Instead the conversion utility writes all the format trigger code to a log file. You will need to implement any corresponding PL/SQL logic as XSL code.

To aid in this process, the resulting RTF template will contain form fields 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. You will need to rewrite these PL/SQL triggers as XSL-FO.

For more information, see Extended Function Support.