Sunday, May 10, 2009

BI Publisher: Reports6i to BIP Conversion Utilities

Currently, many organizations and developers would really like to make the leap to BI Publisher. One potential option to ease into the BIP tool set is to convert some relatively simple reports6i reports.

There are two ways to do the conversion. Manually do it (see links below) or use the BIP Conversion Utilities.
Oracle OBIEE Documentation
RDFtoBIP.zipped
Doing it manually without a GUI is a good option but it's more time consuming because there is less automation and it doesn't really tell you where your at in the conversion process. Using the BIP conversion utilities is really the way to go though because of it's simplicity. It reminds me of quote by Walter Chrysler
"Whenever there is a hard job to be done, I assign it to a lazy man; he is sure to find an easy way of doing it."
It's refreshingly easy to do the conversion using the BIP Conversion Utilities and extremely fast. Here are the main advantages of using them:
  1. BIP technical expertise is not required for the conversion
  2. Complete automation from start to finish
Download the tutorial video (Quicktime)


Download the code

*By downloading the software you agree that you will not sell or license the BIP conversion Utilities. It's free software for everyone to use capiche.

InstructionsStep 1 : Extract the zip
Step 2: Navigate to directory to the bin directory (ReportConversion->bin)
Step 3: Copy the batch the dos batch file to the directory where you will be converting your reports6i binary to xml. Double click to run.
Step 4: Run the ReportConversion.exe (ReportsConversion->bin, do not move the executable)
Step 5. Click the folder icon
Step 6. Navigate to your conversion directory. Select the files named .rdf.xml
Step 7. Click process button.

Report Conversion FAQ


Q. I need to have reports9i installed on the apps server in order to do the conversion.
A. This is the number one misconception about the conversion process. I have heard a lot of developers say they don't have 10g setup in there apps server environment, so they will have to wait until release 12. In reality all a developer needs to do is install forms and reports 10g locally on there PC. Also, it's possible to have multiple versions oracle forms and reports installed on a PC, just be sure to create a new home for each installation.

Q. The conversion has to take place in unix; it must be done from the command line.
A. Because BIP is written in java it allows the conversion APIs to run anywhere there is a JVM. To resolve this issue you need to grab the BIP jar files. The BIP jar files are included with the conversion utility so there is no need to worry.

Q. The reports6i Binary to XML conversion is a manual process and I have to manually key everything into rwconverter.exe.
A. A dos batch or shell script can be written to accomplish this task. A dos batch script is included with the code.

Q. The conversion is time consuming.
A. It's remarkable fast. The conversion utility is running so fast (30 seconds) that it was very difficult to add an voice over for each clip step in the process. As a matter of fact, the video was extended by nearly a minute to allow for a voice over.

Q. The conversion is flawless.
A. Definitely not. The conversion has some known issues: format triggers, bind variables, etc that don't port (there is a log that can be reviewed after the conversion)! You can find more about those issues on this blog, Tim Dexter blog at oracle or if your feeling lucky you can convert your reports and see for yourself. The utility does a really good job but there is no magic bullet.

52 comments:

Gaby said...

Hi Ike,

What if you are looking at integrating your new BI Pub reports (data templates and layout templates)into the OBIEE standalone BI Pub tool? Do you know the steps to accomplish this? Thanks,
Gaby

Ike Wiggins said...

Hi Gaby,

The steps are identical and the conversion process will work OBIEE.

Note: You will need to remove this line from the data template.

< xml version="1.0" encoding="UTF-8>

You may also need to add a dataSource attribute in your sqlStatement nodes or dataTemplate node.

sreekanth said...

i am trying to download RDFtoBIP.zip, but it fails with the following

We're sorry, the weblog you requested cannot be accessed.
- You may not have the necessary permission for access the weblog.
- This weblog does not exist.

You may wish to try again using one of the tools below.
- Check the URL and contact your System Administrator for access

Ike Wiggins said...

Hi Sreekanth,

Just so you know the RDFtoBIP.zipped is a link to oracle's documentation and it's not the link for the BIP Conversion Utilities.....

I've updated the link. Thanks for the heads up.

Ike Wiggins

Kerry said...

I downloaded and extracted the reportconversion but when I go to run the reportconversion I get a problem and needs to close error. Any idea why I can't run this program?

Anonymous said...

I downloaded the conversion utility and was able to run the report batch converter but I get an error when I run the reportconversion.exe. When I send the error to microsoft it states that the problem was caused by java. Any ideas? Thanks Kerry@controls.com

Kerry said...

I’ve been reading articles and articles about this conversion. I tried several different cmd line codes but I kept getting an error. It was nice to have all the files in one location. I really needed this. I’m now able to create a report in report builder and then extract the data template
Thanks for the tool
Kerry

Unknown said...

Ike,
Thanks for the great utility. I have a problem though: I am trying to use dynamic images in my rdf, converted it to xml pub using thje utility you gave me, and configured the DD and the template definitions. When i run the report i get this:
java.sql.SQLException: No corresponding LOB data found :

in the RTF, i am doing this for the image field:
[fo:instream-foreign-object content-type="image/jpg"][xsl:value-of select="IMAGE"][fo:instream-foreign-object]
Nothing seems to be work..
Can you help please. need this for an urgent project!!
Sheena

Ike Wiggins said...

The problem is in your data template. See this post:

http://bipublisher.blogspot.com/2008/07/bi-publisher-let-go-my-logo.html

Unknown said...

Ike,
I am not getting that error anymore, cant remember what made it go away, but my concurrent process is now erroring with this:

Calling XDO Data Engine…
[062109_015329488][][EXCEPTION] java.sql.SQLException: Stream has already been closed
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:269)
at oracle.jdbc.dbaccess.DBDataSetImpl.getStreamItem(DBDataSetImpl.java:1598)
at oracle.jdbc.driver.OracleStatement.getBytesInternal(OracleStatement.java:3555)
at oracle.jdbc.driver.OracleStatement.getStringValue(OracleStatement.java:3808)

Ike Wiggins said...

Use the function from the

http://bipublisher.blogspot.com/2008/07/bi-publisher-let-go-my-logo.html

when querying a blob from the database. It works, it's a common solution that's been kicked around in the BIP forum. If your still suck I would suggest posting your question in the forum. I think you'll get the same technical solution, if not, do me a favor and post the solution on this blog for others to see.

Unknown said...

Solution: I was not dealing with a blob data type hence the issue. once i converted to a blob data type, the error went away. but my picture still does not show.
Could it be a XML client issues with a plugin missing or something like that?

Ike Wiggins said...

Make sure your content type is correct. If it's gif use image/gif.

Also, make sure the xpath is correct for the select

fo:instream-foreign-object content-type="image/jpg"
xsl:value-of select="BLOB_CONTENT"

Unknown said...

Ike, Thanks for your suggestions: Couple of questions for you:
1)Does the value of select need to be just the XML element name, or does it need to have // in front as some posts mention it.
2) My concurrent request is failing with :
java.io.FileNotFoundException, when i add the blob (not UTF encoded)to the RDF report. If i take it out, i get the o/p.
Please help!
Sheena

Ike Wiggins said...

It might, depends on the xml structure.

Unknown said...

I tried everything but cannot get it to work! The image just does not show!
I encoded the data to be base64, did not conver to char as you said for 5.6.3 its not needed.
The data shows (characters) in the RTF if i dont change the code under status bar tab for the field. But its empty if I add the xslfo tags to it.
I tried various things. chaging the mime type and adding '//'. nothing works!
the files are big- 13k+.
thanks
sheena

Ike Wiggins said...

If you can find my e-mail on the blog, I'll look at it. Please send your format template and xml file.

Anonymous said...

Hi,
Can we have rowCount to be a calculated value instead of a fixed number?

rowCount="{count((for-each-group select=".//ns2:result" group-by="ns1:Meaning")/ns1:Meaning)}"

where my xml structure is
-ns0:GetIssues
-ns1:result
ns1:Meaning
ns1:Meaning2
...

Ike Wiggins said...

Yes, row count can be dynamically calculated.

Going forward please when post a guest make sure it's relevant to the actual post. As an example this would be useful be a good question in "Charts Tips & Tricks"

Anonymous said...

Hi,

This is Ramakrishna, trying to convert our oracle apps 6i report to BI Publisher.

We have user exits in our report. How can we handle it in BI Publisher?

Thanks in advance.

Thanks & Regards,
Rama Krishna. P

Ike Wiggins said...

You don't have to worry about it SRW in bip it's already taken care of.

http://blogs.oracle.com/xmlpublisher/2007/03/setting_organizations_for_data.html

Anonymous said...

Hi,

We are using standalone BI Publisher 10.1.3.4.1. Looks like we need to set it before calling the SQL in a pre-fetch trigger. I tried to call in a pre-fetch trigger and it not working.

Could you please help me how can I set it out?

Thanks in advance.

Thanks & Regards,
Rama Krishna. P

Ike Wiggins said...

Rama,

First lets start off with the srw does not exist OBIEE and it's not applicable in bi publisher ebs environment. With that said, the answer to your question about using srw is still no.

If you want to accomplish what srw did in reports6i there are two options.

Option 1: Setup the Oracle E-Bus Suite security model. This can be found in the setup doc. http://www.thoelen.net/doc/bi.1013/b40017/T421739T475591.htm#T479971

Option 2: Do an apps initialize and or set the org in the before report trigger.

Unknown said...

Is there any way to burst reports by batch script

Ike Wiggins said...

Yes, it is possible to burst reports through a batch script...

http://bipublisher.blogspot.com/2007/11/bi-publisher-common-use-for-bursting.html

Anonymous said...

hey

i tryed to convert a report but i have problem when i run .bat file.

i get a "pro0blem and needs to close" error it is about running rwconverter.exe and problem is in 90ca.dll file...

can u help me with this please?

i have oracle 10i reports and forms on server...

thanx

Ike Wiggins said...

Please make sure you have oracle reports9i or 10g installed.

That's the reason it's failing.

Ike

sateesh said...

Hi Ike,

This is about Integration of OBIEE with EBS (E-Business Suite):

I have downloaded OBIEE suite from oracle site and installed and configured in my local system (windows).
I have created one new report using “Oracle BI publisher Enterprise” and it is successfully generating the required output.

Now I want view this report by integrating EBS (R12) and OBIEE (10.1.3.3)

I have seen the steps in Meta link Id: 552735.1 (Integrating Oracle Business Intelligence Applications with Oracle E-Business Suite)

As per my understanding the below is the process:
1) Login to EBS
2) Click on the menu link that is created to open OBIEE Dashboard/ Answers (without giving credentials again to login to OBIEE, using SSO)

My doubts:


1) I am able to link to Dashboard/ Answers page in OBIEE from EBS, but how can I pin point(open) the exact report that I have created in local using Oracle BI Enterprise - BI Publisher, by just clicking on the menu link from EBS.

While creating SSWA JSP function, are we need to pass any extra paramaters?, in such case please provide the exact format of HTML Call fied value to open a report X created in a folder Y.

2) I have created a report ZZZ.xdo locally and I want to make available that report to any users which have access to that responsibility, how I can do that?

Is this nothing but shared folders concept? In such case how we can create a shared folder? and How we (administrator) controls the access/ execute permissions to that report for any user to run.

If it is possible, please provide the steps to link OBIEE from EBS through SSO and to directly open the required report from EBS as mentioned above.

Please help me out, as I am new to OBIEE.

Thanks in advance,
Sateesh…

Ike Wiggins said...

Please don't ask questions for unrelated posts, next time I'll reject your comment.

To answer your questions....

I have not worked in the EBS R12. I would suggest the following:

a. follow the steps outlined in oracle's documentation
b. search the forum and tim d. blog
c. log an informational sr.

if the documentation says you can do it, then you should be able to as long as your on the right patch set for EBS.

Ike

Anonymous said...

Thanks Ike for this great tool

Anonymous said...

Hi Ike,

Thanks a lot for such a great tool. I have not face any problem. Just one question, Is it possible to use the generated package directly to create the output file?

Many Thanks,
Raj

Bryan said...

hI Ike,
thanks for your article first!
I am converting reports from oracle report 6i to BIP10.1.3.4.1 using the convert utility tool.
after converted ,I got 2 kinds of files:data_templete.xml and layout.rtf.
the problem is ,my BIP system is standalone,not integrated with oracle ebs,so ,the BIP report file should contains:###.xdo,###.rtf. now,how can i load the converted files into BIP system?
thanks so much

Satya Ram said...

Hi,

I am trying to convert Report6i rdf to BI Publisher using BIP Conversion Utility.

The tool is not able to create rtf template.

Thanks,

Ike Wiggins said...

Please make sure you have oracle reports9i or 10g installed.

That's the reason it's failing.

Ike

Satya Ram said...

Hi lke,

I have 10g suite installed on my laptop.
The tool is creating XML file, pls and plb files but it is not able to create rtf template.

Thanks,

Saahiti said...

Hi Ike,

I am new to BI Publisher and its concepts. We have a requirement to convert our oracle reports 6i to BI Publisher. I just happened to visit your site and its a very nice blog on BI Publisher.We use Developer 6i for our forms and reports but I have recently installed developer 10g and
followed your steps.
1)Converted my rdf report6i to xml
2)Ran the batch script, report_batch_converter.bat and saw that they are converted to xml files.
3)Ran the Reportconversion.exe and the BIP Conversion client came up.
4)Selected my converted xml file with .rdf.xml file extension (MYXXX.rdf.xml)
5)Clicked on the Process button, but nothing happened after that. Progress bar didnot display to show the status of my conversion. I am stuck here. Can you please help me how to make the Process button work?

Thanks,
krishna

Saahiti said...

Hi Ike,

I am new to BI Publisher and its concepts. We have a requirement to convert our oracle reports 6i to BI Publisher. I just happened to visit your site and its a very nice blog on BI Publisher.We use Developer 6i for our forms and reports but I have recently installed developer 10g and
followed your steps.
1)converted my rdf report6i to xml
2)ran the batch script, report_batch_converter.bat and saw that they are converted to xml files.
3)Ran the Reportconversion.exe and the BIP Conversion client came up.
4)Selected my converted xml file with .rdf.xml file extension (MYXXX.rdf.xml)
5)clicked on the Process button, but nothing happened after that. Progress bar didnot display to know the status of my conversion. I am stuck here. Can you please help me how to make the Process button work?

Thanks,
krishna

Anonymous said...

Hi Ike,

I am new to BI Publisher and its concepts. We have a requirement to convert our oracle reports 6i to BI Publisher. I just happened to visit your site and its a very nice blog on BI Publisher.We use Developer 6i for our forms and reports but I have recently installed developer 10g and
followed your steps.
1)converted my rdf report6i to xml
2)ran the batch script, report_batch_converter.bat and saw that they are converted to xml files.
3)Ran the Reportconversion.exe and the BIP Conversion client came up.
4)Selected my converted xml file with .rdf.xml file extension (MYXXX.rdf.xml)
5)clicked on the Process button, but nothing happened after that. Progress bar didnot display to know the status of my conversion. I am stuck here. Can you please help me how to make the Process button work?

Thanks,
krishna

Ike Wiggins said...

Please verify that you have jre 1.6 or greater installed.

Unknown said...

Hello Ike, I am working on conversion to BIP, trying to download RDFtoBIP.zip but unable to do so. Can you please help me.

Thanks & Regards,
Phani.

Anonymous said...

unable to download the video..please upload it again..

Will said...

Love your blog! I've been doing a lot of research into BI Technology and this has really helped. Thank you!

BIP user said...
This comment has been removed by the author.
BIP user said...

Hi,

I converted oracle 6i report into bipublisher report using conversion tool.tool created datamodel and report in bipublihsre catalog.My datamodel is working fine and it is giving correct data.When i run the report no data is found.My oracle report have some formatting triggers and i came to know that tool will not convert any triggers into XSL code in RTF layout.I can see those are highlighted in Red color,i have no idea how to approach next step.When i load the XML in template and when i preview the report output is No data found .Could you please tell me how to to write PLSQl code manually in RTF layout to enable the formatting trigger or suggest me any other ways to do that

BIP user said...

Hi,

Could you please how to write code in the BI Publisher RTF Template to enable the formatting trigger functionality.I convered oracle report in bipublihser report using conversion tool.My Bipublisher datamodel is fine and iam getting correct data.But when i run the report ,output is no data found.I came to know that, tool will not convert any of the formatting triggers and I have some formatting triggers in Oracle report.Please tell me how to do this,i can see triggers in my converted RTF layout are in Red color .

Thanks,
Veera

Ike Wiggins said...

Veera,

PL/SQL code cannot be executed from format templates. You will need to either code the logic in your data template or format template. Oracle has quite a bit of documentation on how to do this.

Ike

Sufyan Bashri said...

Hi

when i am trying to download the zip file then i am getting the below.

404 Not Found!

Sorry, that page does not exist. Please try another location or you can search...

Sufyan Bashri said...

i am trying to download RDFtoBIP.zip, but it fails with the following


404 Not Found!

Sorry, that page does not exist. Please try another location or you can search..

Hilton Meyer said...

Hi Ike,
Came across the tool and would like to get it up and running. When I run the bar file I get the following error:
'rwconverter.exe' is not recognized as an internal or external command,
operable program or batch file.

I have added the path to the bin file in the environment variables, still no go. You have any suggestions please?

Ike Wiggins said...

You will need to run the rwconverter.exe manually it should be in your oracle home on your windows machine.

Adnan Azeem said...

Can anyone help in running BIP Converter utilitiy? I am trying to run it but I get empty files (xml, pls) with nothing inside.

Thank you.

Adnan Azeem said...

Can anyone help in running BIP Converter utilitiy? I am trying to run it but I get empty files (xml, pls) with nothing inside.

Thank you.