In this blog I will explain brief how to convert the Oracle report into BI Publisher report and then deploy in Enterprise BI Server or Oracle e-business Suite. (In this blog I will cover the Oracle BI Enterprise server, Steps are almost same for e-Business Suite).
In Oracle Report we are following the traditional approach, by saying that I mean
- Data Model
has binding and store in single file. Where as in BI Publisher we have separate file for Data Model, Layout and translation. For a Data Model system will pick the layout and translation at run time.
Conversion Utility that converts Oracle Reports to BI Publisher reports therefore generate several files when it converts oracle standard reports to BI Publisher reports.
Advantage Of BI Publisher reportsBI Publisher has ability to design/display highly formatted report from multiple data source.
BIPBatchConversion is the utility to migrate reports from Oracle Reports to BI Publisher.
This utility converts Oracle XML reports in BI Publisher reports, but if the reports are not in XML format then this utility will not converts them in one step.
Oracle Reports 9i onwards has option to store the reports in XML format, in this case there is no issue in conversion, but Oracle reports 6i are binary reports and stored in rdf format. To convert these reports customer need to install the Reports 9i on the same machine where they execute the conversion utility. During the conversion process BI conversion utility will call the executable rwconverter to convert binary reports in XML Reports.
As shown above conversion utility will generate the following output files for each report.
- Data Template – Define data in XML format in BI Publisher (e.g. order.xml)
- LOV - Parameter details of reports
Above two are required if you want to use converted reports in Oracle E-business
- .xdo file - Report definition file, can be used in Enterprise server and has meta data as well as data template. (e.g. order.xdo) (this is required only for Enterprise server).
- . pls file – it has pl/sql package specification and body required to run the report. (e.g. order.pls)
- . rtf file – It has Layout Template of report (e.g. order.rtf).
- .log file – It will capture any thing that utility not able to convert, like format trigger. This utility will not convert the format trigger. And any trigger that this utility not able to convert will print in log file. (e.g. order. log).
What is Data Template?
Understanding of Data Template is very important. It is used by reports deployed in Oracle E-business as well as Enterprise Server.
It is XML format of defining data in BI Publisher. As shown above it has following section.
- Sql Queries
- Data Trigger
- Data Structure.
Queries & Parameters -In Sql Query we can define our queries to pull the data. We can define multiple queries as well as queries with Master-Detail relationship .We can pass parameter to queries by defining parameters in Parameter section.
Triggers -We can include Format triggers here.
Structure –It is used to format the data. Data Engine interact with the database and retrieve the data in XML format based on the structure defined in this section.
To run the conversion utilities you need the following jar files in your classpath:
- Collections.zip -
- xmlparserv2-904.jar -
1.Load the PL/SQL package into Database
2.Upload report and template to BI Publisher Repository.
4.Check Log file for any manual step
Parameters for Conversion utility
-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 Oracle BI Publisher report objects. This includes the Oracle BI Publisher Report file (.xdo), the layout template file (.rtf), the PL/SQL package, and log file.
-oraclehome — (optional) If your reports are in Oracle Reports XML format do not specify this parameter. If your 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
This utility easily converts
- Tabular and Form based reports.
- Report with repeating frames , headers and footers.
- Single or Multi query reports with Grouping/Sorting, Parameters,Summy/formula colums, PL/sql functions etc.
Customer may need to work on the layout to do some formatting as well as adding the Format Triggers.
For Demo /Further information please feel free to contact.