Oracle BI Publisher Blog

A quick followup on the Google charts I covered last week. They have a chart type that Publisher does not support out of the box - that is Maps. Not your regular Google Maps (saving those for another day) but color coded static maps. Oracle does have its MapViewer solution that will be more powerful certainly and I intend to cover that another day too. For now I thought I would try and get to grips with the Google versions. Getting to grips is a good expression here, they are not as easy as they might be, but they follow the same pattern as the other charts with a twist. Here's what we want.

it color codes the states based on their populations. So a quick trip to the Census Bureau web site got me an Excel file of the latest (2007) data - after much screaming and crying I got Excel to give me some XML - I ended up using our Excel APIs to get the data out - Excel is a complete pig around XML data or Im stupid. Maybe the latter but Im not that stupid! With data in hand its a case of creating those concatenated strings to feed the Google API. for the maps you need: chld=CATXNYFLILPAOHMIGANYNJVAWAMAINAZTNMOMDWIMNCOALSDLA KYOROKCTIAMSARKSUTNVNMWANEIDMENHHIRIMTDESDAKNYVTWY chd=s:iYTSNMLKKJJIGGGGGGGGFFFEEEEEEDDDDDDCCCBBBBBAAAAAAA the 'chld' string holds the two character state codes in descending order of population i.e. California (CA) first with ~36.5 million and Wyoming (WY), last with `500K. This was easy enough to do with a variable:

<xsl:variable name="vState">
 <xsl:for-each select=".//Row[position() > 1]">
  <xsl:value-of select="Data[2]"/>
 </xsl:for-each>
</xsl:variable> 

The chd string holds the data for the states. Google needs you to encode your data, its not enough to pass a comma delimited list. Im not going to get into it here, suffice to say I did the encoding in the data layer. I took the raw population data and rounded it to the nearest million e.g. CA=37 and then mapped 37 to the alpha character Google supports, this happens to be 'i' - its all covered in their web pages. Once I had these two pieces it was a case of concatenating the strings with the rest of the URL in a dummy image in the template:

url:{concat('http://chart.apis.google.com
 /chart?chco=f5f5f5,edf0d4,6c9642,365e24,13390a&chd=s:',$dList,'&chf=bg,s,eaf7fe&
  chtm=usa&chld=',$vState,'&chs=440x220&cht=t')}

and Bob's your long lost rich uncle! Here's my effort:

You can control the color shading to get better contrast than I did, titles, etc. the only annoyance is that you are limited to maps of a maximum, 440x220 pixels. you can of course insert a larger dummy image in your template to get bigger maps, just remember, we'll be scaling those out for you so you'll lose some clarity in the final output, but it works. If you want to see the template and result, get em here. Firewall proxy settings still apply - see the previous Google Chart entry for details.