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