Oracle® Business Intelligence New Features Guide Release 10.1.3.4.2 Part Number E10416-07 |
|
|
View PDF |
This chapter describes a feature of BI Publisher that was introduced in Release 10.1.3.3 and that applies to Releases 10.1.3.3 through 10.1.3.4. Information about this feature is also provided in the BI Publisher documentation set that has been updated for Release 10.1.3.4.
Release 10.1.3.3 introduces the ability to convert reports from Oracle Reports to BI Publisher, as described in this chapter. This chapter includes the following sections:
BI Publisher provides a utility for converting reports from Oracle Reports to BI Publisher.
In Oracle Reports, the data model (SQL query or extraction logic) and layout are contained in a single file. In BI Publisher the data model and the layout are separate objects. The conversion utility therefore generates several files that comprise the report in BI Publisher. In most cases this includes a PL/SQL specification and body that you must create in the database that contains the data for the report. The utility also generates a report definition file and a layout template file that you upload to the BI Publisher repository.
When uploaded, test the report to ensure that the output is as expected and make any changes to the report as needed. Some reports 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 BI Publisher repository.
Test the report and check the conversion log files to identify any manual modifications needed to complete the conversion.
This section describes the steps required before running the conversion utility.
During the conversion process, the source reports must eventually be in Oracle Reports XML format. Oracle Reports XML format is supported in Oracle Reports 9i and above.
If the source reports are not in Oracle Reports XML format, the conversion utility handles this conversion automatically. However, this requires Oracle Reports Designer 9i or later on the same computer where you perform the conversion so that the BI Publisher conversion utility can call the rwconverter executable to get the reports into Oracle Reports XML format.
To get Oracle Reports Designer, download Oracle Developer Suite 10g (10.1.2.0.2) from:
http://www.oracle.com/technology/software/products/ids/index.html
and install Reports Designer.
If the reports exist in Oracle Reports XML format, there is no need to install Oracle Reports.
To run the conversion utilities, you need the following jar files in your classpath:
Collections.zip -
xmlparserv2-904.jar -
xdocore.jar
aolj.jar
For customers using Oracle Enterprise Business Suite (EBS), these libraries are available under JAVA_TOP. If these libraries are not there, then the corresponding classes exist under the JAVA_TOP.
For customer using Oracle BI Publisher Enterprise (sometimes referred to as "standalone"), all these libraries are available under WEB-INF\lib. Example:
C:\Oracle\bi\oc4j_bi\j2ee\home\applications\xmlpserver\xmlpserver\WEB-INF\lib
BIPBatchConversion is the utility to migrate reports from Oracle Reports to BI Publisher.
It takes the following parameters:
-source — (required) Source directory for Oracle Reports files. All reports must be in the same format - either RDF or XML.
-target — (required) Target directory to create BI Publisher report objects. This includes the BI Publisher Report file (.xdo), the layout template file (.rtf), the PL/SQL package, and log file.
-oraclehome — (optional) If the reports are in Oracle Reports XML format do not specify this parameter. If the reports are not in Oracle Reports 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.
BIPBatchConversion requires rwconverter from Oracle Reports to convert the report from RDF format to XML format.
-debug — (optional) To run the utility in debug mode and write debug statements to the log file.
Command Line Usage:
java ... BIPBatchConversion [-debug] -source SourceDirectory -target TargetDirectory [-oraclehome OracleHomePath]
Sample Commands to run BIPBatchConversion:
Example 20-1 Source Report in 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.jar;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
Example 20-2 Source Report 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.jar;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.
The conversion utility generates the following output files for each report:
Report definition file that includes the Data Model (for example: REPORT.xdo)
(Note: This file is not needed for E-Business Suite users; see the following note under DataTemplate.)
DataTemplate (for example: REPORT_template.xml)
(Note: this is not required for Oracle BI Publisher Enterprise users because the data template is embedded in REPORT.xdo.)
Default PL/SQL package specification (for example: REPORTS.pls)
Default PL/SQL package body (for example: REPORTB.pls)
RTF Layout Template (for example: REPORT.rtf)
Log file (for example: REPORT.log)
For example, assume that you have a report called raxinv.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.jar;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
Generates the following output files:
PL/SQL Package Specification: C:\BIPublisher_reports\ raxinv\raxinvS.pls
PL/SQL Package Body: C:\BIPublisher_reports\ raxinv\raxinvB.pls
Report definition file: C:\BIPublisher_reports\ raxinv\raxinv.xdo
DataTemplate: C:\BIPublisher_reports\ raxinv\raxinv_template.xml
RTF Layout Template: C:\BIPublisher_reports\raxinv\raxinv.rtf
Log file: C:\BIPublisher_reports\raxinv\raxinv.log
Many converted Oracle Reports 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 the Oracle Database as follows. These files create the PL/SQL package specification and body.
SQL> @C:\BIPublisher_reports\ raxinv\raxinvS.pls SQL> @C:\BIPublisher_reports\ raxinv\raxinvB.pls
Making your reports visible in the 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, then copy the report folder structure to the file system. For example, suppose the 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\raxinv C:\oracle\bi\xmlp\XMLP\Shared Folders\Converted reports
If you are using the XML DB functionality of the Oracle Database as the report repository, then there are several ways that you can upload content. One method is through WebDAV.
For options and details on how to load reports into the XML DB based repository, see the following topics in Oracle XML DB Developer's Guide (http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14259/toc.htm
):
"Loading XML Content into Oracle XML DB"
"FTP, HTTP(S), and WebDAV Access to Repository Data"
When you have loaded report directories into the 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.
Now that you have successfully converted the reports, created the needed PL/SQL package, and moved the report definition and RTF template files into the BI Publisher repository, you should test the reports to ensure that they are providing the appropriate data and formatting.
Most converted reports run exactly as expected without further modification. More complex reports might require some additional modification to work as desired. Following are some common issues with converted reports.
Occasionally when converting a more complex Oracle Reports report, the Data Template or PL/SQL might contain minor errors and require manual correction.
The conversion utility moves all formula columns to the select clause of the SQL query in the data model. In most cases this does not cause a problem. However, if any of the arguments to the formula is a summary column, this does not work because the summary column is not calculated at the time the query is executed.
To correct this problem, remove this formula from the select clause and implement the formula as XSL in the layout template. Most of these formulas are used either for simple addition or summation or currency conversion, formatting, and rounding.
The majority of Oracle Reports reports use simple "if" formatting logic that is automatically converted to equivalent XSL-FO and inserted 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 must implement any corresponding PL/SQL logic as XSL code.
To aid in this process, the resulting RTF template contains form fields that hold the format trigger names that are called; these fields are 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 these PL/SQL triggers as XSL-FO.
For more information, see the "Supported XSL-FO Elements" section of Oracle Business Intelligence Publisher Report Designer's Guide (http://download.oracle.com/docs/cd/E10091_01/doc/bip.1013/b40017/toc.htm
)