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.

BI Publisher: Charts tips and tricks

The purpose of this post is a tips and tricks guide that explains effective and efficient ways of designing custom charts with BIP and BI Beans. When possible it's always preferable to use Oracle Charting Wizard in the Word plug-in. However if their is a requirement to do conditional logic or dynamic logic, the charting wizard was not designed for this. In this case you would have to manually manipulate the chart xml.

Before getting started on manually manipulating a chart xml file your going to needs some tools and resources.

Tools and Resources:

1. Use an XML Editor. Do not use Microsoft word to edit the chart xml. When you edit it in MSword your much more likely to make syntax errors, and there is no validation that your xml is syntactically correct. I highly recommend XML Pad as editor: http://www.wmhelp.com/

2. Get the chart DTD from oracle. While an XSD is eaiser to use than DTD, it's still better than the alternative which is nothing. The charting DTD is an excellent resource that gets passed over by most developers. Below are two links which should be helpful in your charting endeavors. The first one is Oracle Reports Graphing Faq and the second is the DTD. Note: You will need to copy the dtd and paste it in a file to use it with XML Pad.

I highly recommend reading the Oracle Reports Technical FAQ for graphs. This document is a hidden gem for setting and creating particular properties of a graph. As an example, I was able to find an obscure information like getting a reference line in a graph. I wouldn't have even been able to guess that this was an option by looking at the graph.dtd

http://www.oracle.com/technology/products/reports/htdocs/faq/Graph_FAQ_with_style.html
http://www.oracle.com/technology/products/reports/htdocs/getstart/whitepapers/graphdtd/graph_dtd_technote_2.html

When developers look at the graphing DTD, it seems like it's an impossible document to read and understand. In reality their is no need to understand the DTD. The real purpose of DTD is to assign it to an XML document. When it's assigned your xml editor will validate to make sure your creating a valid structure. The editor will also highlight available attributes for the applicable xml elements. This is one of the most important features of assigning the DTD because the heavy lifting is done automatically for you ie: the editor determines the required elements and attributes.

To assign a DTD in XML Pad go to the menu: XML->Assign Schema/DTD. See that wasn't to bad now was it! Now you should be good to go. Below is a screen shot (sorry for the blurriness, blame blogger, click it to view the whole image) . Notice to the left side of the screen there is a list of properties available. This is the benefit of assigning a DTD. When I clicked on a series line I was able to see all of the available attributes. It gets better though, when I clicked on the markerShape field, a drop down was provided with all the available shapes. Nice...





3. Use Oracle’s template viewer. This tool is another hidden gem and it’s extremely useful for debugging your format template charting issues. The tool can be found in the bi publisher directory in your program files. When running the template viewer be sure to change the “log (debug) level” on the Settings Tab to “STATEMENT” before running the template. As an example, I was able to figure-out a string comparison was being done to a number and the xml element could not be found because it was being treated as a literal string.

Caused by: java.lang.NumberFormatException: For input string: ".//P_ACTIVE_ENGAGEMENT_RATE"

Also, did you know that the template viewer is capable of running in any environment that has the jre? Below is an example the template viewer on my mac. All one would need to do is use the Mac OS X jar bundler and you can have this baby up and running in no time....perhaps a topic for another post :-)



Questions and Answers

This section contains some of the most common questions I have been asked. I tried to put in actual examples so it makes more sense. If you would like to ask a charting question please leave a comment and I'll see what I can do.

Q. Can I do if statements in charts.
A. Yes, remember this is xsl and you have to use that syntax over bip syntax.

Q. Can I do choose statements in charts.
A. Yes, remember this is xsl and you have to use that syntax over bip syntax. (See Example 1)

Q. Can I use xsl variables.
A. Yes (See Example 1)

Q. Do I have to use for-each-group for row data.
A. No . This is a common mistake that people think they have to group the data always. The only reason to group the data would be if you need to aggregate it. If your data is aggregated or you can use xpath with the sum function then the answer is always no. (See Example 1)

Example 1: Get the current month sales, using an xsl variable and default it to zero if there is no data for the month.
<RowData> <xsl:for-each select="MONTHS"> <xsl:sort select="current()/MONTH_ORDER" data-type="number" order="descending"> <Cell> <xsl:choose> <xsl:when test="sum($SALES[../../R_MY=current()/MM_YYYY]/amt) > 0"> <xsl:value-of select="sum ($SALES[../../R_MY=current()MM_YYYY]/amt)"> <xsl:when> <xsl:otherwise>0<xsl:otherwise> <xsl:choose> <Cell> <xsl:for-each> <RowData>

Q. If statements are not evaluating correctly or just plain not working.
A. It's likely the if statement may be evaluating the value as string and not number. To solve this use number() (See Example 2)

Q. If statements fail when I use in less than or greater than signs.
A. Use & lt; or & gt; (See Example 2)

Q. My chart keeps failing because the xml field is being recognized as literal.
A. Use the squiggles { } to avoid this issue. (See Example 2)

Example 2: If statement, number check, avoiding literal interpretations
<xsl:if test="number(//P_ACTIVE_ENGAGEMENT_RATE) > number(0)"> <Y1ReferenceLine> <ReferenceLine index="0" visible="true" lineWidth="2" text="Default Active Engagement Rate" value="{.//P_ACTIVE_ENGAGEMENT_RATE}" displayedInLegend="true" lineColor="#ff00"> </Y1ReferenceLine> </xsl:if>

Q. How do I mix static text with dynamic text without using append.
A. This is actually surprisingly simple. Create a “some text like this for {$YOUR_VAR or YOUR_XML_NODE} " (See Example 3)

Example 3: Dynamic substitution text
<Title text="Monthly Sales - {$CURRENT_MONTH_NAME}" visible="true" horizontalAlignment="CENTER"> <LocalGridData colCount="1" rowCount="5"><RowLabels>

Q. Can I use reports1og to create a chart.
A. Yes you can use repors10g to setup your graph. There is one caveat; it’s not runnable in BIP. However, it’s a great starting point for complex charting were the bip wizard can be sometimes lacking. When you’re done creating your mockup, save the report as xml file. You can rip out the nodes to figure-out what you need to do in chart in BIP.

This all I got for now. Like I said before if you have a question don't hesitate to leave a comment!

Ike