Business Intelligence – Oracle

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.

About these ads

15 Responses to “Oracle Reports to BI Publisher – Conversion Utility”

  1. Manish said

    Do you have any experience yourself or among your co-workers of performing a conversion of a real world RDF to BIP? It seems that most “production” RDFs will require >50% rework either for format triggers or for layout.

  2. Venkatakrishnan J said

    I am afraid i have not tried it out with complex RDFs. But again, I believe that would be true for most conversion utilities. This is more of a stop gap arrangement for Oracle Report users who do not know where to start in BIP. There will always be some amount of trade off since Oracle Reports and BIP are pretty much different technologies. But let me try to find out if there is any recommended way of using this utility for production RDFs.

    • justb said

      Dear All,
      its not work with my it give allot of error like

      I create the RDF and XML report and then but it inside one folder and I create a lib folder and but all of java class inside it and irs not work and the following is the error msg
      “Failed to load main -class manifest attribute from d:\conv\lib\xdocore.jar”
      and this my code

      **************************
      java.exe

      -classpath

      D:\conv\lib\xdocore.jar;
      D:\conv\lib\collections.zip;
      D:\conv\lib\aolj.jar;
      D:\conv\lib\xmlparserv2.jar

      oracle.apps.xdo.rdfparser.BIPBatchConversion

      -source d:\conv

      -target d:\conv\output

      -debug
      ************************************

      any one can help me please.

  3. Manish said

    Do you know of any documents that gives a feature level “how-to” between Reports and BIP?

  4. Ralph said

    hi venkat,

    i tried this out with a simple report and every time i upload the .xdo or just do as you said which is to copy the entire directory over to BIP i can never run the report. i always get a …

    Error
    The report cannot be renedered because of an error, please contact administrator.
    Unexpected element: :title.

    Ever run into this?

    • justb said

      Dear All,
      its not work with my it give allot of error like

      I create the RDF and XML report and then but it inside one folder and I create a lib folder and but all of java class inside it and irs not work and the following is the error msg
      “Failed to load main -class manifest attribute from d:\conv\lib\xdocore.jar”
      and this my code

      **************************
      java.exe

      -classpath

      D:\conv\lib\xdocore.jar;
      D:\conv\lib\collections.zip;
      D:\conv\lib\aolj.jar;
      D:\conv\lib\xmlparserv2.jar

      oracle.apps.xdo.rdfparser.BIPBatchConversion

      -source d:\conv

      -target d:\conv\output

      -debug
      ************************************

      any one can help me please.

  5. Ralph said

    Hi Venkat,

    Actually the error I got above is only if I try to upload the .xdo instead of copying the entire converted directory over.

  6. Vidhya said

    Hi Venkat,
    I too get the same error as Ralph had mentioned when i tried to do the conversion. Infact, i have copied the entire directory and not only the .xdo file. Even then i end up getting the same error

    Error:
    The report cannot be rendered because of an error, please contact the administrator.
    Error Detail

    Unexpected element: :title

    Do you have any suggestions?

  7. Dennis said

    I had the same problem with a cross table. I solved it by developing the RTF template in an earlier version of the Word add-in.
    The new version has a new way of coding the fields of a cross table within the Word add-in. And if you are running an earlier version of BI Publisher, it will result in this error.
    If you’re interested you can have a look at the code with the fieldbrowser.

  8. ellyn said

    Error while logged into BI Publisher thru BI Presentation
    ———————————————————

    Oracle BI Publisher Enterprise
    Reporting Login: java.net.UnknownHostException: unknown
    Error Details
    Error Codes:

    >> I had changed the XML file SECURITY_MODEL = ‘XDO’
    >> Checked on the user login group…Administrator given a XMLP_ADMIN

    Even thru the http://hostname:9704/xmlpserver with the Administrator/Administrator login wont work

    Any solution??

  9. Vera said

    Hi,
    Is it possible to simply extract to a txt file the SQL instruction used by a RDF report?
    I would like to get the SQL, cut-and-paste to PL/SQL and simply execute it to check the result.
    I have to analise the SQL for auditing purposes (check if the SQL does what the client asked to be done).
    There are several Oracle Reports files I have to analyse and it´s very boring to open each one to copy the SQL…
    Thanks in advance.

  10. LAKSHMANAN said

    i need more information abt this BI.

  11. justb said

    Dear All,
    its not work with my it give allot of error like

    I create the RDF and XML report and then but it inside one folder and I create a lib folder and but all of java class inside it and irs not work and the following is the error msg
    “Failed to load main -class manifest attribute from d:\conv\lib\xdocore.jar”
    and this my code

    **************************
    java.exe

    -classpath

    D:\conv\lib\xdocore.jar;
    D:\conv\lib\collections.zip;
    D:\conv\lib\aolj.jar;
    D:\conv\lib\xmlparserv2.jar

    oracle.apps.xdo.rdfparser.BIPBatchConversion

    -source d:\conv

    -target d:\conv\output

    -debug
    ************************************

    any one can help me please. plz feel free to contact me at my email justb2004@hotmail.com

    • Jerry Blair said

      The issue here is that you are running BI Publisher 10.1.4.1 and the class D:\conv\lib\xmlparserv2.jar is not the correct one to use when converting reports. The required class is xmlparserv2-904.jar which is only installed with BIP 10.1.3. If you uninstall BIP and reinstall 10.1.3.x the conversion will work.

  12. Why make life harder, why don’t you use the Reports6i to BIP Conversion Utilities I wrote which are free. There’s a demo video and executable that can be run to do this. It makes the process pretty painless.

    Click this link: http://bipublisher.blogspot.com/2009/05/bi-publisher-reports6i-to-bip.html

    Post a question on my blog if you have any questions.

    Ike

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 151 other followers

%d bloggers like this: