According to the Oracle Lifetime Support Policy , Oracle is not planning any functional enhancements for Oracle Reports in its terminal release, or any subsequent releases. Oracle Fusion Middleware 12cR2 (188.8.131.52.0) will be Oracle Reports terminal release.
Oracle Report customers can migrate to Oracle BI Publisher (Oracle official tool for oracle reporting) at their own place while continuing to receive extended support as specified by the Oracle Reports Statement of Direction.
Oracle BI Publisher provides Oracle Reports to BI Publisher Conversion Assistant (official documentation) as a tool to convert reports from the Oracle Reports format to the Oracle BI Publisher format.
From a license point of view:
- For the moment, Oracle BI Publisher is free for all customers having a valid license for Oracle Forms and Oracle Reports
Let’s test this conversion tool:
Report Conversion Tools Installation
Before to install OR2BIPConvAssist tool, Oracle BI Publisher (BIP) must be installed. Check the Oracle BI Publisher documentation to install it. If a Middleware Home already exists for a fusion products like Oracle Forms/Reports, BIP must be installed into a new Middleware Home.
Go to oracle download website and download the Report Conversion Tools OR2BIPConvAssist.zip:
Unzip the file:
D:\oracle\software>unzip OR2BIPConvAssist.zip Archive: OR2BIPConvAssist.zip creating: bin/ creating: lib/ inflating: bin/OR2BIPConvAssist.bat inflating: bin/OR2BIPConvAssist.sh inflating: lib/aolj.jar inflating: lib/axis.jar inflating: lib/biputil.jar inflating: lib/collections.jar inflating: lib/commons-discovery.jar inflating: lib/commons-lang.jar inflating: lib/commons-logging.jar inflating: lib/help-share.jar inflating: lib/i18nAPI_v3.jar inflating: lib/jaxrpc.jar inflating: lib/jewt4.jar inflating: lib/mail.jar inflating: lib/ohj.jar inflating: lib/ojdbc6.jar inflating: lib/ojdl.jar inflating: lib/or2bipconvassist.jar inflating: lib/oracle_ice.jar inflating: lib/orai18n-collation.jar inflating: lib/orai18n-mapping.jar inflating: lib/orai18n.jar inflating: lib/orawsdl.jar inflating: lib/share.jar inflating: lib/xdo-core.jar inflating: lib/xdo-server.jar inflating: lib/xmlparserv2.jar
Include the ORACLE_HOME path as PATH in the report conversion batch file and add ORACLE_HOME in environment variable:
Open Oracle Reports Builder and save the report in format xml:
Execute the report with Oracle Report before the conversion:
I expect to have the same report layout after conversion to BIP.
Start DOS or PowerShell in Administrator Mode and launch the Report Conversion Tool:
Choose “RDF XML (.xml)” and click Next
Choose Source, Target directory and Debug Mode and click Next
Choose the option to upload the converted reports to Oracle BI Publisher previously installed
Check we have no errors into Report Conversion Log File and Report Upload to Catalog file.
Oracle Bi Publisher and database configuration
Next step is to configure BI Publisher and create the report objects (Program Units / Formulas Columns) into the database not converted by the conversion tool because not existing into BI Publisher.
The conversion tool creates:
- Data Model (.xdm file)
- Report File (.xdo) + a template file (.rtf)
- One PL/SQL Package with procedures and functions which contains the code of Oracle Reports Program Units and Formula Columns
This PL/SQL package must be compiled in the target database :
All function and procedures start with the character “CF*” like “Column Formulas”, the term used into Oracle Reports which contains PL/SQL code related to columns data model.
The package specification does not compile due to bad syntax in the declaration of some functions “cf_****”
Modify with the correct syntax and retry the compilation:
The package body does not compile because:
- One of t he function contains some variables not declared
Let’s declared this variable into the package specification and compile it :
And recompile the package body:
Now let’s do the BI Publisher configuration:
Connect to BI Publisher:
Go to Converted Reports location:
The Oracle Report Parameter has been converted successfully:
The Oracle Report Parameter looks like:
The BI Publisher Report parameter looks like after the conversion:
The DataModels must be modified because the one converted is not correct:
Edit the DataModel:
“Edit Data Set” under menu option:
Modify the SQL statement, the formulas columns coming from oracle reports must be rewritten with CASE sql command:
SELECT ALL ABRECHNUNG_VORGANG.ID, ABRECHNUNG_VORGANG.TYP, . . . CASE WHEN UPPER(VERTRAG.VERTRAG_TYP) = 'AGENTUR' AND rechnung.ist_rechnung_lsv = 'J' AND ABRECHNUNG_VORGANG.TOT_NETTO_PRAEMIE >= 0 THEN replace(report.get_text_report('*******', 'AGENTUR_LSV_BETRAG_POSITIV', 'BRIEF_TEXT', ADRESSE.SPRACHE), '%%RG_BETRAG%%', report.money_to_string(ABRECHNUNG_VORGANG.TOT_NETTO_PRAEMIE)) WHEN UPPER(VERTRAG.VERTRAG_TYP) = 'AGENTUR' AND rechnung.ist_rechnung_lsv = 'J' AND ABRECHNUNG_VORGANG.TOT_NETTO_PRAEMIE < 0 THEN replace(report.get_text_report('*******E', 'AGENTUR_LSV_BETRAG_NEGATIV', 'BRIEF_TEXT', ADRESSE.SPRACHE), '%%RG_BETRAG%%', report.money_to_string(abs(ABRECHNUNG_VORGANG.TOT_NETTO_PRAEMIE))) WHEN UPPER(VERTRAG.VERTRAG_TYP) = 'AGENTUR' AND rechnung.ist_rechnung_lsv != 'J' AND ABRECHNUNG_VORGANG.TOT_NETTO_PRAEMIE >= 0 THEN replace(report.get_text_report('*******', 'AGENTUR_ESR_BETRAG_POSITIV', 'BRIEF_TEXT', ADRESSE.SPRACHE), '%%RG_BETRAG%%', report.money_to_string(ABRECHNUNG_VORGANG.TOT_NETTO_PRAEMIE)) WHEN UPPER(VERTRAG.VERTRAG_TYP) = 'AGENTUR' AND rechnung.ist_rechnung_lsv != 'J' AND ABRECHNUNG_VORGANG.TOT_NETTO_PRAEMIE < 0 THEN replace(report.get_text_report('******', 'AGENTUR_ESR_BETRAG_POSITIV', 'BRIEF_TEXT', ADRESSE.SPRACHE), '%%RG_BETRAG%%', report.money_to_string(abs(ABRECHNUNG_VORGANG.TOT_NETTO_PRAEMIE))) END) CF_BRIEFE_TEXT, . . . FROM . . WHERE . . .
Do the same for all Data Models
Now let’s focus on the report layout:
First “bad” suprise, The Report Conversion tool does not convert completely the layout, we have to recreate the layout from the beginning.
Oracle BI Publisher Desktop Installation
In BI Publisher, the report layout must be created with Microsoft Office tools.
The first step is to download and install BI Publisher Desktop which adds a AddIns for Microsoft Office products (Word, Excel,etc.) which will be used to create the report layout.
If we use Microsoft Office 32 bits on a Microsoft 64 bits, we have to install last version of Java 32 bits and mix it with Java 64 bits (needed for BI Publisher Server).
Run As Administrator:
Check you have a new menu “BI Publisher” into Microsft Word and logon to BI Publisher from Microsoft Word:
Now you can either Create a report either open a report template (the .rtf file generated by the Conversion Report Tool).
Start to edit the report.
Oracle BI Publisher configuration – report configuration
Go to BI Publisher console administration and upload the .xdo file (Click “Upload Resource” into Task menu):
Execute the report (click on View Report link):
If we compare with the current Oracle Report Layout, there is a big difference in the layout.
The Report Conversion Tool does not convert successfully Oracle Reports rdf file:
- The Parameter screen is the only part converted with success
- The Data Model must be rewritten, mainly to integrate oracle reports formula columns
- The layout must me completely rebuild, all fields are in a bad position, some fileds are missing.
- The PL/SQL package which contains oracle reports program units must be modified since some errors syntax exists. Program Units in Oracle Reports are used in general to calculate oracle reports formulas columns (column where the results comes from a PL/SQL procedures or packages).
- The oracle report triggers must be refactored since the report conversion tool does not migrate them. This oracle report triggers are responsible to display or hide some fields. Recreate this trigger into BI Publisher is possible with the BI Publisher option “Event Triggers”.