X

An Oracle blog about BI Publisher

  • RTF
    August 5, 2008

Googlin' Charts

goog.gifSome 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.

Join the discussion

Comments ( 6 )
  • espinete Monday, June 8, 2009
    where is xsl file ?? thanks
  • soledad fernandez vargas Wednesday, August 26, 2009
    el libro de los scretos
  • pottery barn teen Saturday, March 20, 2010
    Traditionally, paint was transferred to the painting surface using paint brushes, but there are other methods, including using palette knives and rags. Oil paint remains wet longer than many other types of artists' materials, enabling the artist to change the color, texture or form of the figure. At times, the painter might even remove an entire layer of paint and begin anew. This can be done with a rag and some turpentine for a certain time while the paint is wet, but after a while, the hardened layer must be scraped. Oil paint dries by oxidation, not evaporation, and is usually dry to the touch in a day to two weeks. It is generally dry enough to be varnished in six months to a year. Art conservators do not consider an oil painting completely dry until it is 60 to 80 years old
  • Joel Schrameck Wednesday, June 2, 2010
    This is often one additional well written scenario. I contain mainly a short while ago determined Googlin' Charts (Oracle BI Publisher Blog) and have always been however looking through these amazing Do it yourself written content there is listed here.
  • oil painting reproductions Thursday, August 5, 2010
    Art is the desire of a man to express himself, to record the reactions of his personality to the world he lives in. - Amy Lowell
  • Harris Kura Wednesday, October 6, 2010
    The thing that can be best done by you to assure that your home is safe is by trusting your belongings and your home on a successful and popular home security company.
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.