Integration with Google Chart and Map!

image image

Have you ever wished you could use different chart options other than the ones that come with BI Publisher ? Especially these days, as the visualization world is flourishing like never been seen before, there are so many different ways of looking at the data and visualize it to obtain more insights. We have added many additional chart types with 11G but what if that’s still not enough ?

Let’s take Google as an example. Google provides something called Chart API, which lets you dynamically generate charts with an URL string. It provides a different set of chart types than the ones that BI Publisher supports. Now the question is, is there any way to integrate the Google charts into the BI Publisher reports ? The short answer is Yes!

All the information required to generate the Google charts such as data, color, format, label, chart type, size, etc, can be placed in the URL and Google Chart API server returns an appropriate image based on the information. So what you need to do is to transform the XML data that BI Publisher internally uses into the Google’s data format then you can embed the returned image inside of your report.

Let’s take a look how to do such with an example of using the Google Map Chart type.

Google Chart API Basics

First, here is a basic sample URL for your first exposure to the Google Chart API with ‘Hellow World’.

https://chart.googleapis.com/chart?chs=250x100&chd=t:60,40&cht=p3&chl=Hello|World

You can type the URL into your browser then you’ll get a sample image like this.

image

The above URL is passing the following parameters and values.

  • chs – size
  • chd – data
  • cht – Chart type (p3 is 3D Pie Chart)
  • chl – Label name

You can check the Google Chart API site for the detail.

Google Chart API - Map

Now with this example, I’d like to use a Google Map chart type to visualize each country’s account balance. So I need to generate an URL something like below with the data.

image 

Here is the list of the parameters used in the above URL. And you can find the detail from the Google Chart API Map site.

  • chs – Size
  • chtm – Map type (e.g. world)
  • cht – Chart type (t is map)
  • chld – Country or region code
  • chd – Data
  • chds – Data Range
  • chco – Chart color
  • chf – Background color

And here is the data I have. It has a country code column, ‘ISO’, and value columns such as ‘DATA2006’, ‘DATA2007’, etc.

image

First, I pick ‘world’ as the map type (chtm). Next is the Country code. The API accepts the country code in ISO 3166-2 format. (e.g. US for United States, GB for UK, FR for France, etc) so I need to somehow transform the XML data and prepare into this format. I’ll talk about that in a minute. And the next is the data, which is the ‘chd’. This is a comma separated values and I need to transform the data into this format as well.

And the last thing is ‘chco’, you can use this to set a color range. Basically it’s sort of the conditional formatting. For this, I set something like this.

&chco=FFFFFF,FF0000,FFFF00,00FF00&chf=bg,s,EAF7FE

And this means, each country will be colored in 4 different colors based on the values. Now it’s time to prepare the Country and Value data.

Prepare the Data with the RTF Template

In order to make this work I’ll use the RTF template (MS-Word), with which I can do a lot of custom stuff very flexibly and easily. And I can transform the above XML data to the data format that is expected by the Google API.

As you see the sample XML data I have ‘ISO’ element for the Country and ‘DATA2010’ for the value. And here is the sample code.

<?variable@incontext:total;sum(DATA2010)?>

<?variable@incontext:min;xdoxslt:minimum(DATA2010)?>

<?variable@incontext:max;xdoxslt:maximum(DATA2010)?>

<?for-each:ROW?>

<?if:position()<last()?>

<?xdoxslt:set_variable($_XDOCTX, 'country', concat(xdoxslt:get_variable($_XDOCTX, 'country'), substring(ISO, 1,2)))?>

<?xdoxslt:set_variable($_XDOCTX, 'value', concat(xdoxslt:get_variable($_XDOCTX, 'value'), DATA2010, ','))?>

<?end if?>

<?if:position()=last()?>

<?xdoxslt:set_variable($_XDOCTX, 'country', concat(xdoxslt:get_variable($_XDOCTX, 'country'), substring(ISO, 1,2)))?>

<?xdoxslt:set_variable($_XDOCTX, 'value', concat(xdoxslt:get_variable($_XDOCTX, 'value'), DATA2010))?>

<?end if?>

<?end for-each?>

For the Country code, I’m using ‘concat’ function to concatenate all the ISO values after using ‘substring’ function to keep only the first two letters from each of the value, and set the final value to a variable ‘country’.  And for the Value, again I’m using ‘concat’ function to concatenate al the DATA2010 values with comma ‘,’, and set the final value to a variable ‘value’. Also, I’m using an IF statement with ‘position()=last()’ to not add the comma ‘,’ for the last value.

Also, I’m setting ‘min’ and ‘max’ variables to set minimum and maximum values so that I can pass these values to the Google API later as the Min and Max values. And that’s it. Now we’re ready to call the Google API!

Call Google Chart API !

So by this time, I have created the following BI Publisher variables and set appropriate values as above.

  • min
  • max
  • country
  • value

Now, I can just use a dummy image or use the Chart Wizard with the Template Builder to set a dummy chart image.

image

And now I can set an URL link to this image so that this image will be dynamically generated.

Select ‘Size’ from the right click menu.

image

Then, type the following URL command, which is setting the above variables into the URL as expected by the Google Chart API.


url:{concat('https://chart.googleapis.com/chart?chs=440x220&chtm=world&cht=t&chld=', xdoxslt:get_variable($_XDOCTX, 'country' ), '&chds=', $min, ',', $max, '50&chd=t:', xdoxslt:get_variable($_XDOCTX, 'value'), '&chco=FFFFFF,00FF00,FFFF00,FF0000&chf=bg,s,EAF7FE')}

And that’s it. Now I can run this report after uploading to BI Publisher server. It will generate an output with a map image that is generated by the Google Chart API.

image

One thing to note is though, if you’re running this report inside your company or organization’s firewall then you will need to set a proxy server accordingly.

How to Set Proxy Server to Enable External HTTP Server Access

Depends on which application server you’re running your BI Publisher on but you basically need to set these two JVM parameters.

-Dhttp.proxyHost=<proxy.mycompany.com> -Dhttp.proxyPort=<port_number>

If you’re on BI Publisher 11G, then you can edit ‘setDomainEnv.bat’ (or setDomainEnv.sh for Linux) file, which can be found at %BI_INSTALL_HOME%\user_projects\domains\bifoundation_domain\bin, to add the parameters like the below.

set EXTRA_JAVA_PROPERTIES=-Dxdo.server.config.dir=C:\biee11\user_projects\domains\bifoundation_domain\config\bipublisher -DXDO_FONT_DIR=C:\biee11\Oracle_BI1\common\fonts -Dhttp.proxyHost=<proxy.mycompany.com> -Dhttp.proxyPort=<port_number> %EXTRA_JAVA_PROPERTIES%

Once you set the parameters then you need to restart your application server.

More Chart Options!

With this post I’ve introduce a very simple Google Map image integration, but the Google Chart API has a lot more to explore. The basic logic is the same, so now it’s up to you how far you want to go. And as you imagine, this doesn’t have to be only with Google Chart API. Any service provider provides similar service can be used with BI Publisher reports with the same logic mentioned above. So the potential is unlimited!

Please share your experience or any new solution with us by tweeting us @bipublisher or sending an email to ‘kanichiro.nishida@oracle.com’. Oh, and if you like to get the sample report feel free to contact me!

Follow bipublisher on Twitter

Comments:

Hi,
Great article but please can you confirm my suspicions that this service is now deprecated or have I gone to the wrong page.

https://developers.google.com/chart/image/docs/chart_wizard?hl=ko
shows "Image Charts (Deprecated)"

Cheers

Nigel

Posted by guest on September 11, 2012 at 04:21 PM MDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Follow bipublisher on Twitter Find Us on Facebook BI Publisher Youtube ChannelDiscussion Forum

Join our BI Publisher community to get the most and keep updated with the latest news, How-to, Solutions! Share your feedback and let us hear your voice @bipublisher on Twitter, on our official Facebook page, and Youtube!

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today