Business Intelligence – Oracle

Archive for August 22nd, 2007

Oracle Reports to BI Publisher – Conversion Utility

Posted by Venkatakrishnan J on August 22, 2007

Oracle BI Publisher 10.1.3.3 has a very interesting feature. It comes along with a small utility that can convert Oracle Reports to BI Publisher reports. I just thought i would blog about the same, since a lot of Oracle Report users are now migrating towards BI Publisher. This utility would come in handy for a lot of people. In fact getting this utility to work is very simple. Lets see how this utility works in this article. First i started with creating a sample Oracle Report.

         osnap2.jpg

The report is based on 3 columns of Geography table. Those 3 are Region Name, Country Name and City Name. This report would primarily consist of grouping by region and then displaying a tabular report of the coutries and cities for that region. The report looks like this

         osnap3.jpg

The next step is to export/save the report in the form of XML from within Oracle Reports. Remember that Oracle Reports stores both the layout and data queries in the same file. Once the report is saved update your CLASSPATH to point to the $BIPUB_HOME\oc4j_bi\j2ee\home\applications\xmlpserver\xmlpserver\WEB-INF\lib directory. This directory would contain 4 import jar files. They are

1.   Collections.jar
2.   xmlparserv2-904.jar
3.   xdocore.jar
4.   aolj.jar

The above 4 files are required for the utility to work. The utility accepts 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 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.

Command Line Usage:

                     java … BIPBatchConversion [-debug] -source SourceDirectory -target TargetDirectory [-oraclehome OracleHomePath]

                     osnap4.jpg

Once the command is executed, it will create the layout, sample xml, xdo file and others in a same directory.

                     osnap5.jpg

The next step is to compile the package that is created once the utility is executed.

                     osnap6.jpg

Lets open the template to see whether the grouping and others are intact.

                     osnap7.jpg

Copy the entire folder to the xmlp reports directory. Once the copy is done, refresh the report repository. We would get the report within BIP Enterprise as shown below.

                     osnap8.jpg

View the report. But ensure that you have data source of the name default that would connect to the source from which you have created the report. Else change the name of the datasource in the date template definition.

                     osnap9.jpg

 All of the above and more, is available in the documentation here.

Advertisements

Posted in All Posts, Bi Publisher | 15 Comments »