Freedom to format in OBIEE

I’m finally back to my blogging business J. This article deals with the usual OBIEE business of formatting. OBIEE gives a lot of formatting options by letting us write javascript/HTML code in a spectrum of places such as narrative, static view, text’s in dashboards, data format tab of columns etc.
I am sure you can list a lot more examples of places where we can write javascript/HTML in OBIEE.

Apart from this, OBIEE also gives a lot of formatting options like conditional formatting. In spite of all these, we might need more freedom in formatting, because let’s face it, business intelligence is not just about extracting meaningful info from the warehouse. Business intelligence is also about presenting info to the user in such a way that critical info is properly projected so that it catches the attention of the user.

In this article I will describe a mechanism to control different columns on the same row and also to control different columns in different rows. Now I’m sure there might be other ways of doing this thing and some of them might be better, in fact it will be kool to know about other similar techniques. Please leave a comment, if you happen to know any such technique.

This process has a lot to do with the HTML generated by the tool. We will get a handle on the rows in which we are interested. We will then bookmark the row of our interest and then use it as a reference to format a different row. I am using a row as a reference only because, I intend to do relative formatting here.

The beauty of this method is that, you can do this kind of formatting based on your custom logic by using javascript.

Ok, now let’s start digging

I am formatting a table view from a static view. The important thing is the mechanism used to get a handle because this method can then be applied to format other views as well

I am using samplesales repository and I have selected 2 columns from one of the subject areas

‘Year’ has a drilldown associated with it while revenue does not have a drilldown. I have pasted a part of the entire HTML below. This HTML displays the data in the table

<tr>

                <td dir="ltr" scope="row" style="font-size:9px;"  class="OOLD">

                                <a class="ResultsTableLink" href="javascript:void(null)" style="font-size:9px;" onclick="return 
                                 RTDr(saw_442_3,0,0)">2007</a>

                </td>

                <td dir="ltr" style="background-color:#FFFFFF;font-size:9px;" class="OORD">11,371,280</td>

</tr>

<tr>

                <td dir="ltr" scope="row" style="font-size:9px;"  class="OOLD">

                                <a class="ResultsTableLink" href="javascript:void(null)" style="font-size:9px;" onclick="return 
                                RTDr(saw_442_3,1,0)">2008</a>

                </td>

                <td dir="ltr" style="background-color:#FFFFFF;font-size:9px;"  class="OORD">13,531,764</td>

</tr>

The process:

1.       We will start by getting the handle on the anchor tag which displays “2007”.

2.       Use it to change the background color of “2007”.

3.       Book mark the table row tag “<tr>”, to which this anchor tag belongs, by setting a name for it. Tagging is a good way to
  reach the same element again, if required. If tagging is not done then we will have to run the loop for all tags and this will
  be costly.

4.       We then increment the loop index to go the next row.

5.       Since we want to format the second column so we change our loop index accordingly to get an handle on this element

Let me now share my javascript code which does this

// I am using simple java script here. The code can be simplified and shortened by using JQuery

<script type="text/javascript">

var anchor_handle = document.getElementsByTagName('a');  // helps us get the handle on all anchor tags.

/* Any logic can be coded at this point. We are iterating through all the anchor tags and searching for the one
which is used to display the text “2007”. We then change its background color and give a name to its parent’s
parent. The parent of the anchor tag is a “<td>” tag and the parent of the “<td>” tag is a “<tr>” tag. This is
obvious from the HTML written above*/

for(var t=0;t< anchor_handle.length;t++)

{

if(anchor_handle[t].innerHTML == '2007')

{

anchor_handle [t].style.backgroundColor="#00DD00";

anchor_handle [t].parentNode.parentNode.name = 'ABC';

}

}

/* Since we know that we have book marked a “<tr>” tag so we now search for the tag which we 

have just named. We then change the color of font in the same row. We increase the loop index
“table_row_handle[g+1]” to reach the next row. Since the loop index in the loop “for(var i=1; i<
table_row_handle[g+1].childNodes.length; i++)” is initialized to 1 so we directly reach the 2^nd
column of this row. We then change the color of this as well*/

var table_row_handle = document.getElementsByTagName('tr');

for(var g=0;g< table_row_handle.length;g++)

{

if(table_row_handle [g].name == 'ABC')

{

for(var i=0; i< table_row_handle[g].childNodes.length; i++)

{

table_row_handle[g].childNodes[i].style.color = "#00CC00";

}

for(var i=1; i< table_row_handle[g+1].childNodes.length; i++){

table_row_handle[g+1].childNodes[i].style.color = "#00CC00";

}

}

}

</script>

I am placing this in a static view and I am placing this static view under my table view. Let me show you the difference between my placing the static view above the below the table view

 

Note:

1. The code in the static text view is the same but its placement makes a difference. If we place it above the table view then the visibility of the java script code is not enough for it to get a handle on the table view.

2. script type = "javascript" worked for me but script language = "javascript" didn't. I don't have any justification for this. All I can say is that  'script language' is deprecated so its best that we do not use it

3.The class for the column without drilldown is “OORD” while that for the column with drilldown is “OOLD”. We can use if( tds[t].className == ‘OORD’ ) in conjugation with the document.getElementsByTagName('td');  to apply some formatting on all the texts in the table. Similarly, if( tds[t].className == ‘OOLD’) with document.getElementsByTagName('td');  can be used to format all the links in the table