Heat Maps in OBIEE

OBIEE 10.1.3.4.1 has a host of ways to add visual impact to reported data to aid its interpretation. But some things are not so easy to achieve, or at least not by just clicking on a few format buttons on the Answers web page.

One of my customers had the need to report a day's figures and colour the results based on change since yesterday. I also had three additional design challenges to meet: not to change the RPD (this is only a proof of concept, RPD changes might come later), avoid having to laboriously conditionally format each coloured cell (the colour rules could become very complex and based on multiple columns) and allow the use of "normal" and pivot tables on the dashboard.

Firstly, as I am looking at the difference between the a measure over two days I can not use a pivot report for this as it is not possible to calculate differences between measure columns in the pivot. But we can use the 'FILTER' button on the column formula editor to restrict the values returned to just those that match the filter. By doing this twice, once for each day of interest, we get two columns in the same Answers Request TABLE view that we can do the maths on. Here we need to calculate the percentage difference between the two values.

Now I can calculate the values I need for my heat map - the day's measures, and the percentage change since yesterday. Next I need to colour in the cell backgrounds.

Of course it very possible to just use conditional formatting, but this gets quite unwieldy with complex cell colour rules, and of course the request to make the colour mapping reusable across multiple Answers requests and dashboards. I decided to tackle the problem by adding a text column to the request and changing its format to HTML. To change the background colour of a cell you need to make the cell contain the following HTML:

<div style="background-color: rgb('|| cast(255*RND() as varchar(3))||','||cast(255*RND() as varchar(3))||',cast(255*RND() as varchar(3))|| ');" > -some value to output' || </div>'

You must output something in the cell as OBIEE does not colour in NULL values. I decided to use the RGB() form of the colour selector as it is probably simpler to calculate three numeric values than to build colour codes in hexadecimal.

Although it is feasible to code this colour mapping in OBIEE it is a lot of effort. Here I simplified things greatly by writing a database function that takes the value to display and a value that controls the colour (and here it is simply percentage change over two days) as the two arguments and returns a varchar2 string that formats the cell. Although the colouring coding algorithm can be as complex as you like ultimately we populate a string and return it.

[code omitted]
vHTML_STRING:='<div style="background-color: rgb('||vRED||','||vGREEN||','||vBLUE||')" >'||TO_CHAR(pValue,'FM999,999,999,999')||' </div>'
return vHTML_STRING;

To use this we simply set the cell contents to be the results of the OBIEE EVALUATE

Evaluate('ORCL.F_my_colour_map(%1,%2)' as varchar(100), [some OBIEE expression that evaluates to the colour],[some other expression that gives the value to display]))

function formatted as HTML.

Of course, this method is only suitable for the current OBIEE release, older versions may not have 'EVALUATE' and  the future OBIEE 11 may not behave in the same way with rendering HTML within Answers tables.