E Converting Oracle Reports

Oracle BI Publisher provides Oracle Reports to BI Publisher Conversion Assistant as a tool to convert reports from the Oracle Reports format to the Oracle BI Publisher format. Oracle Reports to BI Publisher Conversion Assistant can be used for BI Publisher stand alone implementations or when it is integrated with Oracle Business Intelligence Enterprise Edition.

This appendix includes the following sections:

E.1 Overview

In Oracle Reports, the data model (SQL query or extraction logic) and report layout specifications are contained in a single file. In BI Publisher, the data model and the layout are separate objects. Oracle Reports to BI Publisher Conversion Assistant therefore generates several files from a single Oracle Report file that will make up your report in BI Publisher.

Oracle Reports to BI Publisher Conversion Assistant generates a report definition file, a data model file, and a layout template file. These are the BI Publisher objects that are uploaded to the BI Publisher repository. When the Oracle Report includes calls to package functions, Oracle Reports to BI Publisher Conversion Assistant also creates the PL/SQL specification and body.

After Oracle Reports to BI Publisher Conversion Assistant completes the report conversion, compile the .pls files on the database. After compilation, test the report to ensure that the output is as expected. If the report fails to display the expected results, review the log files (conversion log, upload log, BI Publisher Server log).

In some cases, the data model and layout template may require manual adjustments to improve report output.

Some reports contain structures that Oracle Reports to BI Publisher Conversion Assistant cannot convert. These structures must be manually implemented in the converted reports.

Figure E-1 describes the conversion process.

Figure E-1 Oracle Report to BI Publisher Report Conversion Process

Reports conversion process as described in following text

The overall flow for the conversion process is as follows:

  1. In BI Publisher, create a JDBC or JNDI data source connection that connects to the same database as the Oracle Report.

  2. Run Oracle Reports to BI Publisher Conversion Assistant.

  3. (Conditional) Upload the report to BI Publisher Catalog if you did not choose Oracle Reports to BI Publisher Conversion Assistant's automatic upload option.

  4. (Conditional) Compile the PL/SQL package on the database.

  5. Test the report and check the conversion log files to identify any manual modifications needed to complete the conversion.

E.2 Obtaining Oracle Reports to BI Publisher Conversion Assistant

To obtain Oracle Reports to BI Publisher Conversion Assistant:

  1. Go to the following website: http://www.oracle.com/technetwork/middleware/bi-publisher/downloads/index.html

  2. Download OR2BIPConvAssist.zip for your Windows or Linux environment and unzip the installation zip files to any directory on your machine. The following folders are created in the directory: bin, config, and lib.

  3. Under the bin folder, use OR2BIPConvAssist.bat or OR2BIPConvAssist.sh to launch Oracle Reports to BI Publisher Conversion Assistant.

E.3 Prerequisites and Limitations of Oracle Reports to BI Publisher Conversion Assistant

The following are prerequisites for running Oracle Reports to BI Publisher Conversion Assistant:

  • You must be running JDK version 1.1.8 or later.

  • If your source Oracle Reports are not in XML format, then you must have Oracle Reports rwconverter 9i in your Oracle Home.

  • You must have a BI Publisher role with Write access to Shared Folders in BI Publisher Catalog.

E.3.1 Converting Oracle Reports to XML Format

Oracle Reports to BI Publisher Conversion Assistant uses Oracle Report XML (or RDF XML) to convert Oracle Reports into BI Publisher reports. The conversion from RDF binary to RDF XML report formatting is supported for Oracle Reports 9i and above.

Oracle Reports to BI Publisher Conversion Assistant allows source reports to be in both formats - RDF binary and RDF XML.

The following requirements are necessary in order for Oracle Reports to BI Publisher Conversion Assistant to convert reports:

  • Oracle Reports Designer 9i or later installed on the same machine as Oracle Reports to BI Publisher Conversion Assistant.

  • You must enter the Oracle Reports Home Path in Oracle Reports to BI Publisher Conversion Assistant in order to call the rwconverter executable and convert the reports into Oracle Reports XML format.

Oracle Reports Designer is part of the Oracle Developer Suite 10g (10.1.2.0.2) and is available from: http://www.oracle.com/technology/software/products/ids/index.html

E.3.2 Limitations

Oracle Reports to BI Publisher Conversion Assistant has the following limitations:

  • Format triggers cannot be converted, but the code is written to a log file for manual implementation. For more information on PL/SQL format triggers in RTF layout templates, see Section E.7.2, "PL/SQL Format Trigger Logic Not Supported in RTF Layout Templates."

  • Charts cannot be converted. If you choose to create a RTF layout, use the Template Builder for Word to create charts. If you choose to create a BI Publisher layout template, use the Layout Editor to create charts. For more information about the layout design tools, see Oracle Fusion Middleware Report Designer's Guide for Oracle Business Intelligence Publisher.

  • Matrix reports cannot be converted. If you choose to create a RTF layout, use the Template Builder for Word's Pivot Table Wizard. If you choose to create a BI Publisher layout template, use the Layout Editor to create pivot tables. For more information about the layout design tools, see Oracle Fusion Middleware Report Designer's Guide for Oracle Business Intelligence Publisher.

E.4 Running Oracle Reports to BI Publisher Conversion Assistant

This section describes how to run the conversion assistant and includes:

E.4.1 Running the Assistant When the Source Report Is an Oracle Reports RDF File

If the source report is an Oracle Reports RDF File, perform the following steps:

  1. Create the JDBC/JNDI Connection data source on the BI Publisher Administration page. This data source connects to the same database as the Oracle Report. You must have the Administrator role to perform this step.

  2. Launch Oracle Reports to BI Publisher Conversion Assistant.

  3. Enter the following fields:

    • Source Path - source directory for Oracle Reports files.

    • Oracle Reports Home Path - Oracle Reports directory. For example, C:\Oracle_1\Middleware\OracleFRHome1.

      Oracle Reports to BI Publisher Conversion Assistant requires rwconverter from Oracle Reports in order to convert the report from RDF format to XML format. Specify the Oracle Reports home path where Oracle Reports Designer (9i or later version) is installed. Oracle Reports to BI Publisher Conversion Assistant assumes that rwconverter is contained in the bin directory beneath the Oracle Reports home path specified.

    • Target Path - location to place the output files. For each converted report, Oracle Reports to BI Publisher Conversion Assistant creates the following separate output objects in the Target Path: Oracle BI Publisher Report folder (.xdo) containing the report definition (.xdo) and layout template (.rtf) files, the data model file (.xdm), zipped report definition file (.xdoz) and zipped data model file (.xdmz), the plsql_pkgs folder containing the PL/SQL packages, and bipconvert.log and bipupload.log files.

      Note:

      You can use the zipped report definition file (.xdoz) and the zipped data model file (.xdmz) to manually upload reports to BI Publisher Catalog.

    • Data Source Name - specifies the data source that you created in step 1.

    • Debug - specifies if Oracle Reports to BI Publisher Conversion Assistant will run in debug mode, and write debug statements to the log files.

      Select No to write conversion process details and any statement level error messages to a log file at the report level in the target directory.

      Select Yes to write the details of the conversion process, including the complete error stack in case there are exceptions, to a log file at the component level in the target directory.

    • Upload to Catalog - specifies if converted reports are automatically loaded to BI Publisher Catalog.

      Select No to create converted reports only in the Target Path. This is the default option.

      Select Yes to automatically upload the reports to the BI Publisher Server. If you select this option, you must provide the BI Publisher Server URL (http://<server>:<port>/xmlpserver,) username and password, and the BI Publisher Catalog folder (under the Shared folder) where the converted reports will be stored. If you specify a folder that does not yet exist in BI Publisher Catalog, it will automatically be created during the conversion process.

      Note:

      If you choose to automatically upload the converted reports to the BI Publisher Server, the bipupload.log file is populated with the details in the Target Path. If you do not automatically upload the converted reports, this log file is empty.

  4. Click Run. Oracle Reports to BI Publisher Conversion Assistant converts the Oracle Reports and creates a folder for each of the Oracle Reports in the Target Path. If you selected Upload to Catalog, a folder is created for each report in the specified folder under the Shared folder. Each report folder contains the data model and report. The PL/SQL packages are created only in the Target Path.

  5. Compile the PL/SQL packages (.pls files) to the database. The PL/SQL packages are created in the Target Path in Oracle Reports to BI Publisher Conversion Assistant. For more information, see Section E.5, "Compiling the PL/SQL Package to the Database."

    Note:

    Oracle Reports to BI Publisher Conversion Assistant populates the Oracle DB Default Package field in the data model with the package name defined in the report.

  6. (Conditional) If you did not select Upload to Catalog, use the Upload Resource feature in BI Publisher Catalog to upload each zipped data model and report file (.xdmz and .xdoz). Ensure that you use the BI Publisher UI (/xmlpserver) to upload both objects to the same folder in BI Publisher Catalog. For more information, see "Downloading and Uploading Catalog Objects" in Oracle Fusion Middleware User's Guide for Oracle Business Intelligence Enterprise Edition

  7. View the converted reports in the Report Viewer.

  8. If the reports were not converted as expected, review the log files. For more information, see Section E.8, "Troubleshooting Oracle Reports to BI Publisher Conversion Assistant."

E.4.2 Running the Assistant When the Source Report Is in Oracle Reports XML Format

If the source report is in Oracle Reports XML format, follow these steps:

  1. Create the JDBC/JNDI Connection data source on the BI Publisher Administration page. This data source connects to the same database as the Oracle Report. You must have the Administrator role to perform this step.

  2. Launch Oracle Reports to BI Publisher Conversion Assistant.

  3. Enter the following fields:

    • Source Path - source directory for the Oracle Reports files in XML format.

    • Oracle Reports Home Path - this is not applicable for Oracle Reports XML format files.

    • Target Path - location to place the output files. For each converted report, Oracle Reports to BI Publisher Conversion Assistant creates the following separate output objects in the Target Path: Oracle BI Publisher Report folder (.xdo) containing the report definition (.xdo) and layout template (.rtf) files, the data model file (.xdm), zipped report definition file (.xdoz) and zipped data model file (.xdmz), the plsql_pkgs folder containing the PL/SQL packages, and bipconvert.log and bipupload.log files.

      Note:

      You can use the zipped report definition file (.xdoz) and the zipped data model file (.xdmz) to manually upload reports to BI Publisher Catalog.

    • Data Source Name - the data source that you created in step 1.

    • Debug - specifies if Oracle Reports to BI Publisher Conversion Assistant will run in debug mode, and write debug statements to the log files.

      Select No to write conversion process details and any statement level error messages to a log file at the report level in the target directory.

      Select Yes to write the details of the conversion process, including the complete error stack in case there are exceptions, to a log file at the component level in the target directory.

    • Upload to Catalog - specifies if converted reports are automatically loaded to BI Publisher Catalog.

      Select No to create converted reports only in the Target Path. This is the default option.

      Select Yes to automatically upload the reports to the BI Publisher Server. If you select this option, you must provide the BI Publisher Server URL (http://<server>:<port>/xmlpserver,) username and password, and the BI Publisher Catalog folder (under the Shared folder) where the converted reports will be stored. If you specify a folder that does not yet exist in BI Publisher Catalog, it will automatically be created during the conversion process.

      Note:

      If you choose to automatically upload the converted reports to the BI Publisher Server, the bipupload.log file is populated with the details in the Target Path. If you do not automatically upload the converted reports, this log file is empty.

  4. Click Run. Oracle Reports to BI Publisher Conversion Assistant converts the Oracle Reports and creates a folder for each of the Oracle Reports in the Target Path. If you selected Upload to Catalog, a folder is created for each report in the specified folder under the Shared folder. Each report folder contains the data model and report. The PL/SQL packages are created only in the Target Path.

  5. Compile the PL/SQL packages (.pls files) to the database. The PL/SQL packages are created in the Target Path in Oracle Reports to BI Publisher Conversion Assistant. For more information, see Section E.5, "Compiling the PL/SQL Package to the Database."

    Note:

    Oracle Reports to BI Publisher Conversion Assistant populates the Oracle DB Default Package field in the data model with the package name defined in the report.

  6. (Conditional) If you did not select Upload to Catalog, use the Upload Resource feature in BI Publisher Catalog to upload each zipped data model and report file (.xdmz and .xdoz). Ensure that you use the BI Publisher UI (/xmlpserver) to upload both objects to the same folder in BI Publisher Catalog. For more information, see "Downloading and Uploading Catalog Objects" in Oracle Fusion Middleware User's Guide for Oracle Business Intelligence Enterprise Edition.

  7. View the converted reports in the Report Viewer.

  8. If the reports were not converted as expected, review the log files. For more information, see Section E.8, "Troubleshooting Oracle Reports to BI Publisher Conversion Assistant."

E.4.3 Output Files

Oracle Reports to BI Publisher Conversion Assistant generates the following output files in your target directory for each converted report:

  • Report definition file (format: REPORT.xdo).

  • Zipped report definition file (for example: REPORT.xdoz).

  • Data model file that defines the data model (format: REPORT.xdm).

  • Zipped data model file (for example: REPORT.xdmz).

  • RTF Layout Template (for example: REPORT.rtf).

  • Default PL/SQL package specification (format: REPORTS.pls).

  • Default PL/SQL package body (format: REPORTB.pls).

  • Log files (format: REPORT.log).

For example, assume that you want to convert a report called invoice.rdf located in the source directory D:\reports\pay and you use Oracle Reports to BI Publisher Conversion Assistant to enter the target directory D:\BIPublisher_reports\invoice.

The following output files are generated:

  • Report definition: C:\BIPublisher_reports\invoice\invoice.xdo\_report.xdo

  • Zipped report definition: C:\BIPublisher_reports\invoice\invoice.xdo\_report.xdoz

  • RTF layout template: C:\BIPublisher_reports\invoice\invoice.xdo\invoice.rtf

  • Data model: C:\BIPublisher_reports\invoice\invoice_template.xdm\_datamodel.xdm

  • Zipped data model C:\BIPublisher_reports\invoice\invoice_template.xdm\_datamodel.xdmz

  • PL/SQL package specification: C:\BIPublisher_reports\plsql_packages\invoice\invoiceS.pls

  • PL/SQL package body: C:\BIPublisher_reports\plsql_packages\invoice\invoiceB.pls

  • Report conversion log file C:\BIPublisher_reports\bipconvert.log

  • Report upload log file C:\BIPublisher_reports\bipupload.log

E.5 Compiling 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 using the following commands to create the PL/SQL package specification and body.

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

E.6 Moving Converted Reports to the Oracle BI Publisher Repository

This step is only required if you did not specify for Oracle Reports to BI Publisher Conversion Assistant to automatically upload the converted reports to Oracle BI Publisher Catalog. You can manually upload the converted report components using one of the following methods:

E.6.1 Method 1: Move the .xdo and .xdm folder files to the BI Publisher Repository Path in the File System

To use this method, you must have access to the server file system. Oracle recommends that you use this method only when you have a large number of converted reports.

After you move the .xdo and .xdm folder files to the BI Publisher repository path in the file system, the next step depends on your implementation:

If BI Publisher is deployed standalone (not part of Oracle BI Enterprise Edition), move the .xdo and .xdm folder files to the BI Publisher repository path in the file system. Ensure that you place each report definition (.xdo) and data model (.xdm) pair in the same folder. You do not need to restart the BI Publisher Server.

If BI Publisher is integrated with BI Presentation Services Catalog, perform the following steps:

  1. Move the .xdo and .xdm folder files to the BI Publisher repository path in the file system. Ensure that you place each report definition (.xdo) and data model (.xdm) pair in the same folder.

  2. Navigate to the BI Publisher Server Administration page and select Server Configuration. On the Server Configuration page, in the Catalog region, click Upload to BI Presentation Catalog. You do not need to restart the BI Publisher Server.

E.6.2 Method 2: Upload the .xdoz and .xdmz zip files using the BI Publisher UI

To use this method, you must have Write permissions on the folder in the catalog to which you are uploading and you must have one of the following roles: BI Publisher Administrator, BI Publisher Template Designer, BI Publisher Developer, or BI Author.

  1. Log in to the BI Publisher server (for example, http://www.<host>:<port>/xmlpserver)

  2. Select the folder in the catalog to which you want to upload the report.

  3. In the Tasks region, click Upload. Click Browse to locate the report component (.xdoz or .xdmz) to upload. Repeat this step for each object, ensuring that you upload each report definition (.xdoz) and data model (.xdmz) pair to the same folder.

E.7 Testing and Editing Converted Reports

After you have successfully converted your reports, created the required PL/SQL packages, and moved the report definition and data model files into Oracle BI Publisher Catalog, test your reports. Most converted reports will run as expected without further modification of the data model; however, it is likely that the layout will require additional modifications to achieve the desired appearance. The following sections are some common issues that you may encounter with converted reports.

E.7.1 Summary Columns Moved to the Select Clause

Occasionally, when you convert a more complex Oracle Reports report, the data model or PL/SQL may contain minor errors and require manual corrections. Oracle Reports to BI Publisher Conversion Assistant 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 cause errors because the summary column will not be calculated at the same time as the query is executed.

To correct this problem, remove this formula from the select clause and implement the formula in the data model. For more information on formula columns, see Oracle Fusion Middleware Data Modeling Guide for Oracle Business Intelligence Publisher.

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

Many Oracle Reports use simple "if" formatting logic. Oracle Reports to BI Publisher Conversion Assistant automatically converts this logic into 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. Oracle Reports to BI Publisher Conversion Assistant does not convert any PL/SQL format trigger logic present in the report. Instead, Oracle Reports to BI Publisher Conversion Assistant 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 must rewrite the PL/SQL triggers using BI Publisher code syntax or XSL in the RTF template form fields.

E.8 Troubleshooting Oracle Reports to BI Publisher Conversion Assistant

Oracle Reports to BI Publisher Conversion Assistant produces two log files (bipconvert.log and bipupload.log) to assist you with troubleshooting the report conversion process. These log files capture exceptions at the statement level or in debug mode depending on which options you selected during report conversion.

After the you upload the reports to BI Publisher Catalog, you can view the reports in the Report Viewer. If the report is not displayed as expected in the Report Viewer, review the errors captured on the screen or review the following BI Publisher log file: (MWHOME\user_projects\domains\bifoundation_domain\servers\AdminServer\logs\bipublisher\bipublisher.log).

Also, if you encounter problems with a converted report, test the data model as follows:

  • If the data model is successful, verify the data in the XML output. If the data contains special characters, the report may fail while parsing the data.

  • If the data model fails, check the data source connection. Next, verify if the .pls files were compiled successfully on the database to create the package. Finally, verify that the result data is accurate.