Oracle BI Publisher Blog

Some of you may have found the 'newish' Google Charting engine. I looked at it a while back not long after it came out. Looked cool but a little tough to use with Publisher. They have quite a few chart types and lots of bells and whistles for them. There are also some new and interesting additions, more on those later. Its still not 'easy' to integrate with Publisher but its doable. Im not suggesting you move across to Google charts - we can not support you for one thing but its an alternative that you might want to investigate.
You interact with the chart engine via a URL (there is no other interface) and it returns an image. That's easy enough for us to handle in the template, using our dummy image and url: approach we can construct the URL and replace the dummy image with the Google chart at runtime. The engine will be expecting data points and labels of course, this is a little more tricky as they need to be passed as delimited lists on the URL. Here's a full URL

http://chart.apis.google.com/chart?cht=bhs&chtt=Store+Sales&chs=600x400&chd=t:74,69,75,71,78,71,87,82,84,59,88,79&chxt=x,y&ch
xl=1:|Stores 1-50|Stores 51-100|Stores 101-200|Stores 201-300|Stores 301-400|Stores 401-500|Stores 501-600|Stores 601-700|Stores
701-800|Stores 801-900|Stores 901-1000|Stores 1000+

There's a lot going on in there but notice the delimited numbers (data points) and the delimited strings (labels). Everything else in there are just parameters such as chart type, colors, size and title. I'm not going to dwell on the chart parameter specifics, check out the Google docs. Im going to focus on generating the delimited strings in a template. Assume we have the following XML:

<STORES>
 <STORE>
  <RANGE>Stores 1-50</RANGE>
  <SALE>
   <WEEK>1</WEEK>
   <AMT>10</AMT>
  </SALE>
  <SALE>
   <WEEK>2</WEEK>
   <AMT>12</AMT>
  </SALE>
  <SALE>
   <WEEK>3</WEEK>
   <AMT>15</AMT>
  </SALE>
  <SALE>
   <WEEK>4</WEEK>
   <AMT>11</AMT>
  </SALE>
  <SALE>
   <WEEK>5</WEEK>
   <AMT>10</AMT>
  </SALE>
  <SALE>
   <WEEK>6</WEEK>
   <AMT>16</AMT>
  </SALE>
 </STORE>
...
...
<STORES>

I want to get a chart that lists the store ranges (RANGE) and their total sales (AMT) in a bar chart format. How do I get those delimited strings then? The following will create a variable named 'dList' and then creates the comma delimited list by looping over the STORE level and summing the AMT value. As long as its not the last value it will insert a comma:

<xsl:variable name="dList"> 
 <xsl:for-each select=".//STORE">
 <xsl:value-of select="sum(SALE/AMT)"/>
  <xsl:if test="position() != last()">
   <xsl:text>,</xsl:text>
  </xsl:if>
 </xsl:for-each>
</xsl:variable>

The above code generates: 74,69,75,71,78,71,87,82,84,59,88,79 For the labels we use:

<xsl:variable name="sList"> 
 <xsl:for-each select=".//STORE">
  <xsl:value-of select="RANGE"/>
  <xsl:if test="position() != last()">
   <xsl:text>|</xsl:text>
  </xsl:if>
 </xsl:for-each>
</xsl:variable>

This time we are delimiting with a pipe ('|') to get: Stores 1-50|Stores 51-100|Stores 101-200|Stores 201-300|Stores 301-400|Stores 401-500|Stores 501-600|Stores 601-700|Stores 701-800|Stores 801-900|Stores 901-1000|Stores 1000+ Then its just a case of building the URL:

concat('http://chart.apis.google.com/chart?cht=bhs&chtt=Store+Sales&chs=600x400&chd=t:'
,$dList
,'&chxt=x,y&chxl=1:|'
,$sList)

This will generate the correct URL for the chart, which we can then get into an RTF template by using a dummy image and then in the Web properties of the image using:

url:{concat('http://chart.apis.google.com/chart?cht=bhs&chtt=Store+Sales&chs=600x400&chd=t:',$dList,'&chxt=x,y&chxl=1:|',$sList)}

This will get us:

If you're testing in the Template Builder for MSWord and you are behind a firewall, you'll need to ensure you set the proxy correctly. Tools > Options > Preview > Java Option field:

-Dhttp.proxyHost=www-proxy.us.oracle.com -Dhttp.proxyPort=80

Google has a mass of parameters for their charts that you could quite easily make dynamic as you build the URL. If you're really committed to their engine you could build an extension to our XSLT engine to accept an XML stream much like the built in charts we provide. Here's the RTF and XML data.