KPI Partners Blog

Visually Presenting Data in Tables and Pivot Tables

Posted by KPI Partners News Team on Thu, Nov 03, 2011 @ 11:39 AM

by Kurt Wolff

There are times when you might want to visually present data directly in tables or pivot tables rather than create a chart view. For example, you may have too many values to show in a chart. Or the number of values returned by the query might vary, making the size of the chart sometimes too small to accommodate them all, sometimes too large. Or you may want to visually scroll up and down a table to compare values and would like a visual representation right there in the table.

This posting will present two ways to do that.

Here’s an example of data that we would like to represent visually.

Pivot   Picture1

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

A quick and easy visualization can be done using the text Repeat function.  The formula here will repeat the upper case “I” character as many times as there are thousands in the Amount Sold column.

repeat('I', case when "Sell Through Facts"."Amount Sold" is null then 0 else cast(round("Sell Through Facts"."Amount Sold"/1000,0) as integer) end )

Pivot   Picture2

 

 

 

 

You can easily change the format of the resulting text to get the look you want. For example, here is the  format that produces the visualization below.

Pivot   Picture3

 

 

 

 

 

describe the image

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

The formula casts to integer in order to avoid the following error message.

describe the image

 

 

 

 

 

A second way to do this is to use the Google chart API to draw a horizontal bar chart. Google’s API can produce a variety of chart types including bar, horizontal bar, pie, and line. Using a line chart in the right place can be especially powerful.

The simplest way to use Google, in my opinion, is first of all to normalize the data as a percentage of the maximum value in the result set.

Pivot   Picture6

 

 

 

 

The second step is to create another column using a formula that will generate a URL that conforms to the parameters in the Google chart API. The first part of the formula, represented here in yellow, is text that sets chart type, size, and color. The part after that is the data. The Cast converts the normalized data to text and Replace removes any “.” characters.

Pivot   Picture7

 

 

Here’s the text of the formula.

'http://chart.apis.google.com/chart?cht=bhs&chs=100x20&chco=4d89f9&chd=t:'||replace( trim(cast(ifnull(100*"Sell Through Facts"."Amount Sold"/max("Sell Through Facts"."Amount Sold"),0) as char(3))),'.','')

Set the Data Format to treat text as an Image URL.

Pivot   Picture8

 

 

 

The result looks like this.

Pivot   Picture9

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Google charts are described at http://code.google.com/apis/chart/

There you can find all the details you need to change colors, sizes, and chart types. Google’s API integrates very nicely with OBIEE.  Here’s the same data, represented as vertical bar charts in a pivot table. You can see that this technique gives you a way to visualize trends very easily.

Pivot   Picture10