Oracle Reports to BI Publisher Nigration

Oracle Reports to BI Publisher Migration

Firstly we need to understand the basic difference between Oracle Report and BIPublisher Report.In Oracle Report both the data and report structure layout are embedded together in a single rdf file, where as in a BIP report these two are in different layers. Therefore the migration is a two-step process.

• Convert the Oracle Report to xml using the rwconverter. Note that this xml
  file contains both the data as well as the report structure layout.

rwconverter batch=yes source=oracle report filename with fullpath
(ie the rdf file) dest=output xml filename with fullpath dtype=xmlfile
overwrite=yes
(Note although there are many ways of converting the rdf file
to xml,like save as xml file,or generate to file -à xml .The above method is the
best way to do it as the template generated is very usefull with very few
modifications required .)

• Call the DataTemplateGenerator API to migrate the Oracle Reports Data model
  to a data template. This will generate the following output files
  Data Template(is the Report.xml)
  Default pl/sql package specification(ReportS.pls) and Default pl/sql package body(ReportB.pls)

  Call the RTFTemplateGenerator API to migrate the Oracle Reports Layout to an BI Publisher rtf template.This will generate the following output files
  RTF template
  Log File

  You can combine these two calls into a single one as follows
  java.exe -classpath D:\report_mig\xdocore.jar;D:\ report_mig \collections.jar;D:\ report_mig \aolj.jar;D:\ report_mig \xmlparserv2-904.jar oracle.apps.xdo.rdfparser.BIPBatchConversion -source D:\ report_mig\input -target D:\ report_mig \output –debug
  (Make sure that the folder D:\ report_mig contains all the jar files and the folder D:\ report_mig\input contains the xml file generated in step1 and the folder D:\ report_mig\output will contain all the output files generated from the execution of the above command.(ie you will have a template rtf file,log file,xdo file,data template xml file and two .pls files).

  Copy the entire output folder to the BI Publisher server reports directory
  $BIPUB_HOME \xmlp\XMLP\Reports.

  Now you can check the report output.

Now we have completed with the migration part.Next comes the difficult part.
If the report is a simple one with no triggers and formulaes then the migration is very simple,the migration utility will handle the entire process and no manual intervention is required.But we have the following limitations and issues.

Issues and Workarounds

• Some complex Oracle Reports may result in minor errors in the generated DataTemplate or PL/SQL that require manual correction
• Format triggers are not supported. The format trigger logic must be implemented separately though XSLT.
• The Data Template cannot support the following scenario: A formula column references the summary column as a parameter and the summary column belongs to the same Data Source/Data Query. This is not supported because the data template moves all the formula columns to the select statement; therefore the summary column value is not available while executing the formula.
• Values for placeholder columns and pl/sql logic for Formula columns may not get fully implemented in the data template.(Note: The last two limitations is of most important and definitely requires manual workarounds)

Workarounds

• As a first step you can start correcting the datatemplate. Try to implemement the logic incorporated in the formulae columns and placeholder columns in the sql query of the data template.Although you can see the columns in the query,at runtime the value for these may not get substituted because of Limitaion 3 & 4.
• If the report is a simple one, then rtf template file generated would be correct. But f or complex reports you need to modify your rtf template or start building a new one.