Display Sparkline/Graphs in OBIEE 11g in columns

My aim was to display revenue across regions over past 3 months and an additional column for displaying the graphs (just like Sparkline feature in office 2010).

Requirements:

1. OBIEE 11 installed and running

2. You need to have Internet connection to the machine on which you have installed OBIEE 11g else see step 3. You need the Internet connection in order to refer the JavaScript library files available over the Web (instead of downloading and installing them).

3. If you don't have Internet connection then

a. Download jQuery from http://jquery.com/

b. Download Sparkline plug-in for jQuery located at http://omnipotent.net/jquery.sparkline/1.6/jquery.sparkline.min.js

c. Install them on your Weblogic server.

Note: I am using sample application for the demo. You should be able to correlate it with your application.

Note: I used jQuery 1.5.2 and Sparkline 1.6 version.

jQuery and Sparkline plug-in:

jQuery is a popular JavaScript library. I don't expect an OBIEE developer to know about jQuery and I have covered enough information required for this process. Sparkline plug-in is a library which runs on top of jQuery for displaying graphs in a Web browser using JavaScript.

The logic of displaying graphs is very simple

Step 1: You need to have HTML container like div or span tag with values separated by commas. For e.g.,

<span class="myClass1">120.9,140.6,100.3</span>

Step 2: You need to add the libraries mentioned in requirements and add an additional script to the HTML page

Add JavaScript libraries like this

<script type="text/javascript" src="Path/jquery-1.5.2.js"></script><script type="text/javascript" src="Path/jquery.sparkline.js"></script>

Additional script:

<script type="text/javascript">// <![CDATA[
$(function() {
$('.myClass1').sparkline('html',{type:'bar', barColor:'green'});
});
// ]]></script>

Note: Replace Path with the location where you have saved these two files.
You should see a graph in place of the those numbers like this

Explanation of additional script:

$(function(){ …. }); This statement tells browser that run this script only after loading all the JavaScript libraries, CSS files, HTML code etc.
1. In jQuery, you address anything using a jQuery operator called '$'.
2. $('.myClass1') means find all the containers which have CSS class as 'myClass1'.
3. $('.myClass1').sparkline('html',{type:'bar',barColor:'green') would let us draw a bar graph with color green (type:'bar' displays bar graph and color is green). So if you say type:'line' then you would see a line graph.
For more details, please see http://omnipotent.net/jquery.sparkline/#s-doc

Create Analysis:

Now create a new analysis with following columns in criteria tab
1. Customer Region
2. Per Name Month
3. Revenue
4. One Month Before Revenue with column formula as

AGO("Base Facts"."1- Revenue",1)

5. Two Month Before Revenue with column formula as

AGO("Base Facts"."1- Revenue",2)

6. SparklineCode with column formula as below. I am using the JavaScript libraries deployed over the web if you are using local libraries then update the src values. You should include single quotes present at start and end of the column formula

'<script type="text/javascript" src="http://ajax.aspnetcdn.com/ajax/jquery/jquery-1.5.2.min.js"></script><script type="text/javascript" src="http://omnipotent.net/jquery.sparkline/1.6/jquery.sparkline.min.js"></script>
<script type="text/javascript">// <![CDATA[
$(function(){
$(".myClass1'||cast(rcount(1)  as varchar(3))||'").sparkline("html",{type: ''line'', width:''40px'', minSpotColor:''red'', maxSpotColor:''green''});
$(".myClass2'||cast(rcount(1)  as varchar(3))||'").sparkline("html",{ type: "bar", width:''40px'', barColor: "green"});
});
// ]]></script>'

7. Line Graph with column formula as

''
||CAST("Base Facts"."1- Revenue" AS VARCHAR(50))||','
||CAST(AGO("Base Facts"."1- Revenue",1) AS VARCHAR(50))||','
||CAST(AGO("Base Facts"."1- Revenue",2) AS VARCHAR(50))
||''

8. Bar Graph with column formula as

''
||CAST("Base Facts"."1- Revenue" AS VARCHAR(50))||','
||CAST(AGO("Base Facts"."1- Revenue",1) AS VARCHAR(50))||','
||CAST(AGO("Base Facts"."1- Revenue",2) AS VARCHAR(50))
||''

Note: For 4, 5, 6, 7, 8 columns, I have added Revenue column repeatedly and changed their names and column formula.

Note: For 6, 7, 8 columns, I have changed their folder name as “Sparkline” as they represent Sparkline code.

Very Important Step: Since columns 6, 7, 8 contain HTML and JavaScript code, I have changed the Data Format for each of these columns as HTML using column properties option



So your criteria tab should look like this



After doing good amount of beautification on the results tab, I could finally get this view.
Note: Don't try to exclude or hide the Sparkline code column (6) just try to mask it.



Drop a comment if you face a problem or have any question.