OBIEE Inline Microcharts using Google Charts API

By using a combination of techniques it is possible to display charts inline, within an OBIEE report. In other words we can achieve this type of ‘sparkline’ trend indicator instead of a plain table.

To achieve this we need to make use of Google’s excellent chart API. The following example is a bit of a clumsy hack but it illustrates some clever principles at work. In summary we need to use a combination of a native OBIEE request and some javascript to manipulate a dummy, placeholder image via the Document Object Model (DOM).

Firstly create a simple report. I’ve used the Sample Sales repository (11gR1) for this example but the technique also works on version 10.3.x.

I’ve created measure columns for ‘this month’ and ‘last month’ and have used the ‘filter using’ syntax to fix their data for 2 successive months. E.g.

LM column formula:
IfNull(Filter(“Base Facts”.”Revenue” using “Time”.”Per Name Month”=’2010 / 11′),0)

TM column formula is basically the same but fixed for month 12.

Note that the IfNull function is there so we get zero instead of null data – this is needed for the Google API.

Once you have your base columns, add an extra column to the request for the chart image. Set the formula to build up an string representing an HTML image tag. The formula should be as follows, including the single quotes.

'<img src="" id="trend_' || Cast(RCOUNT("Base Facts"."Revenue") as char) || '" />'

Notice how I include a row pointer (the RCOUNT part) so that our image can be uniquely identfied later by javascript.

Set the Column Properties > Data Format to treat the contents of the column as HTML

Next, add a second additional column to the request and edit its formula so that it contains a comma delimeted set of the last 6 months’ values. We must use the ‘Cast’ operator to convert each fact measure into a char equivalent. After the measure values we also concatenate another instance of the row pointer as per the earlier step.

Set the Column Properties > Data Format to be ‘Custom Text Format’ and enter the following:

@<script>buildGoogleChart(‘@’)</script>

Now we need to add the javascript to the report. On the results tab add a new view of type ‘Static Text’. Edit it and paste the javascript which handles the calls to Google for the images. Make sure you tick the box for ‘Contains HTML Markup’. The javascript is as follows:

<script language="javascript">
function buildGoogleChart(inputVals){

 arrVals = inputVals.split(',');
 var obiData = '';

 for (x=0;x<arrVals.length-1;x++){
  arrVals[x] = (isNaN(parseFloat(arrVals[x]))) ? 0 : parseFloat(arrVals[x]);
  obiData += arrVals[x].toString() + ',';
  }

 obiData = obiData.substring(0, obiData.length-1); // remove trailing comma

 var maxVal = arrVals[0];
 for (x=1;x<arrVals.length-1;x++){
 if (arrVals[x] > maxVal) maxVal = arrVals[x]; }

 var minVal = arrVals[0];
 for (x=1;x<arrVals.length-1;x++){
 if (arrVals[x] < minVal) minVal = arrVals[x]; }

 imgURL = 'http://chart.apis.google.com/chart?';
 imgURL += 'cht=lc:nda'; // line chart, no data axes
 imgURL += '&chco=005CB8'; // rgb colour of chart line
 imgURL += '&chs=65x18'; // image width x height
 imgURL += '&chf=a,s'; // transparent background
 imgURL += '&chd=t:'+obiData; // append data values
 imgURL += '&chds='+minVal+','+maxVal; // x axis lower and upper limits

 var imgID = 'trend_' + arrVals.pop().toString();
 imgObj = document.getElementById(imgID);
 imgObj.src = imgURL;
}
</script>

Add the static text view to the compound layout, above the table where the charts are to be displayed.

The final step is to hide the column containing the javascript in the results table. We can’t use the regular column ‘Hide’ checkbox or the script call won’t get written to the page. Instead we must ‘fake it’ by changing the borders and colours so the column blends in to the white background.

Neat eh? With a few minutes effort it would be easy to expand this idea as the beginnings of a nice dashboard.

Why not download the entire request as XML. You can paste this into the Advanced criteria tab (11g) and have a play.