Everything Oracle

Introduction

Let’s start off with the same example we used in the previous article on conditional formatting. Let’s suppose we have a table that shows sales volume by calendar year and calendar month, and let’s suppose we want to colour code sales volume cells within the table according to the following criteria:

If the sales volume for a table cell is less than 20% of what it was a year ago then the cell should be coloured dark blue;

If the sales volume for a table cell is less than 20% of what it was a month ago then the cell should be coloured blue-green; and

If the decrease in sales volume in the forthcoming quarter is greater than 20% then the table cell should be coloured light blue.

This time we want the data to be shown in a pivot table format, and we want the pivot table to display a summary row, a summary column, and a grand total.

To make the requirement a little more realistic let’s add some more rules:

Within a summary row if the sales volume for a table cell is less than the average for the preceding month then the cell should be coloured mauve;

Within a summary column if the sales volume for a table cell has not increased by at least 10% from the average for the preceding year then the cell should be coloured mauve;

The table cell with the lowest sales volume should be coloured red, and the row and column values that intersect at that cell, together with the corresponding headers, should be in italics;

If the yearly sales volume has increased year on year over the last three years then the grand total should have a red border; and

The master header should show the percentage growth in sales over the last year.

In other words, we want a table that looks like this:

Formatted Report

In practice, the analytic conditions that drive the formatting are likely to be more complicated, such as deviations from linear or non-linear regression functions, but as our focus here is on conditional formatting, and not on analytics, a set of simple conditions will suffice.

Limitations of Default OBIEE Functionality

If we start off using the table view we created in the previous article:

Starting Point

and then change to a pivot table view based on the same request we can immediately see that OBIEE falls a long way short of providing the functionality that is typically needed for BI reporting:

Pivot Table View

While the colour coding defined in the request was applied in the table view it is not applied in the pivot table view. Pivot tables don’t display cross-column conditional formatting, but since the predicates available to define formatting conditions are so limited, cross-column formatting using selection columns is usually required.

Now a partial workaround for this problem would be to pivot the data in the data source. But in doing so we’d be trying to solve the problem in the wrong place, and we’d have to add 36 selector columns. This approach would be a great waste of developer resource since it would not leverage the power of OBIEE to quickly build the basic pivot table for us. Even worse, this approach would not allow us to conditionally format the summary rows and columns, the header rows and columns, or to use conditional formatting that depended on settings that were selected by a user at runtime.

The solution we need is one that:

Leverages existing OBIEE functionality, so that we can maximize developer productivity;

Is front-end based, rather than repository or data source based; and

Is generic, allowing us to completely alter all HTML elements that appear on a dashboard page.

What we need is a solution that uses Javascript and HTML DOM.

Post-processing using Javascript and HTML DOM

The approach we’ll use to workaround our conditional formatting problem can be applied to solve almost all non-graphical dashboard formatting and display problems.

Let’s first justify why using Javascript and HTML DOM is the only generic approach to dynamic front-end customization. Changing XML messages or CSS styles won’t work since changing these would only have data independent effects on the dashboard display. Using the OBIEE property sheets won’t work because they support such a limited range of conditional functionality – as we can see in the case of our pivot table. The implication is that we must post-process the HTML page after it has left the Presentation Services. We could do this in the HTTP server, but by far the best place to do so it is on the end user’s PC. By doing it on the PC we’re not adding any additional loading to the key OBIEE component servers – end user’s PCs are vastly overpowered and have plenty of CPU cycles to spare.

We also need a convenient way of installing our post-processor on multiple computers. By far the best way to do this is to deliver the post-processor along with the web page that is to be post-processed. With this approach we can install just those components of the post-processor that are relevant to the web page at hand. Now, we want to leverage functionality that is already part of a web page, rather than adding something alien. Hence, we should use Javascript – as Javascript is already used by OBIEE in any case, we can guarantee that the end users’ PCs will support it.

The outline of the solution is a Javascript function that:

Is called once after all the relevant data on a page has been rendered;

Parses the web page tags using HTML DOM and extracts all the relevant decision making data;

Analyses the data and decides what elements within the web page need to be changed; and

Inserts, updates, and deletes web page elements as required using HTML DOM to achieve the desired result.

Many aspects of this process can be encoded in library functions that can be reused, so that only the analysis step needs to be customized on each occasion.

Javascript Storage Location

The first thing to consider is where to store the Javascript functions. If you open any dashboard page, right-click on the page, and select “View Source” from the pop-up window that appears you’ll find the HTML source code that is being rendered by the browser. Close to the beginning of the page you’ll find a section that looks like this:

Javascript Files

The Javascript file “common.js” is loaded for each page so any Javascript functions added to this file will be available on all dashboards. The active version of file “common.js” is stored in directory:

<OracleBI Home>\oc4j\j2ee\home\applications\analytics\ analytics\res\b_mozilla

for the J2EE OBIEE configuration:

Javascript Files Directory

If you can’t find the active file there for your particular configuration then do a search.

You can add in custom Javascript functions at the beginning of file “common.js”. As a test we’ll add a simple alert:

Javascript Test Function

Note that this file may be overwritten when OBIEE is upgraded, so you may need to reinsert any functions you add here from version control after an upgrade is completed.

Web Page Post-Processor Invocation

We need to call our web page post-processor from each dashboard page and we need to do so after the rest of the page has been rendered, and in a manner that does not take up any screen real estate.

We’ll start by creating a request, say “Web Page Postprocessor” – any request will do but for simplicity we’ll choose one that returns a single column and a single row, “Product Total” from “Products” and add a narrative view based on the request to the “Compound Layout”:

Post-Processor Request

The narrative view will just contain the Javascript function call in the prefix and nothing else (note that the “Contains HTML Markup” check box has been selected):

Post-Processor Narrative View

On our sample dashboard we add the “Web Page Postprocessor” request after the last request on the page:

Dashboard with Post-Processor

From the “Show View” menu item we select just the narrative view. We save the dashboard and immediately our alert appears:

Test Alert

followed, when we press “OK”, by our pivot table.

The narrative view adds just about one millimetre of whitespace after the pivot table and before the end of section border, and so it won’t be noticed (we could always post-process it out of the way if required).

All we need to do now is to modify our Javascript function to process the web page as required. Testing changes is particularly easy: keep the editor open, save the changes made, and then refresh the web page to see the results.

Parsing using HTML DOM

Completing the remainder of our pivot table task does not depend on OBIEE. So if you’re familiar with navigating HTML DOM, then you can safely ignore the rest of this article. If you’re not, we’ll go through the key steps involved in HTML DOM navigation.

The first task is to extract the HTML corresponding to the pivot table from the web page. If you right-click on the dashboard page, and select “View Source” from the pop-up window that appears you’ll find the HTML source code. A subset of the HTML for our pivot table is as follows:

Pivot Table HTML

The recommended way of navigating the HTML DOM is by using the “getElementById” method. Unfortunately, as you can see from the code, HTML element identifiers are generated dynamically at runtime, and so we can’t use this method of navigation. Instead we must use the “getElementsByTagName” method.

We need some text that uniquely identifies the HTML table that corresponds to our pivot table. We can see that one of the “td” elements contains the master column title “Sales Volume”. So if we can locate this element, then we can locate the table to which it belongs. If we had multiple requests containing the “Sales Volume” column name then we could simply label them in some manner, such as “Sale Volume [01]”, “Sale Volume [02]” ... to ensure uniqueness. With a generic naming convention we could automatically strip out the label during post-processing – as you can see we are already post-processing the header in the case of the pivot table to add the sales growth rate.

We can generate a collection of all the “td” tags on the web page using the method:

document.getElementsByTagName( ‘td’ )

In order to search we need the total number of elements in the collection, which is given by:

document.getElementsByTagName( ‘td’ ).length

We need to know when we have reached the “td” element that has “Sales Volume” as it contents. The contents of a “td” element is given by

document.getElementsByTagName( ‘td’ )[ <inx> ].innerHTML

where “<inx>” is the index of our Javascript loop.

Once we’ve found the correct “td” element we need to find the address of the node of the containing table. We can do this using:

document.getElementsByTagName( ‘td’ )[ <inx> ].
parentNode.parentNode.parentNode

If you look at the HTML for the table this may seem a little puzzling: the parent of the “td” element is a “tr” element, and the parent of the “tr” element is the “table” element, which is what we’re looking for. However, the HTML DOM adds in a “tbody” element, which is missing from the code; that’s why we need three invocations of “parentNode”.

Now that we’ve found the table node we can find the address of any “td” node within the table using:

<table node>.rows[ <inx> ].cells[ <jnx> ]

Bear in mind that the headers are also part of the HTML table, so that, for example, the red cell below has its node addressed by (zero-based):

<table node>.rows[ 3 ].cells[ 4 ]

Formatted Report

We can extract the contents of a cell using:

<table node>.rows[ <inx> ].cells[ <jnx> ].innerHTML

We can then analyse the contents of all cells in the table to make a decision on formatting.

Since we can access all elements within the web page we could also make the formatting decision based on the results of other requests. In many cases the data needed for decision making is the data that is displayed. If additional data is required as part of the decision making process it can included in another dummy request and hidden from view using any of the standard HTML techniques for hiding data. Information gathered on one dashboard page can be stored and subsequently used to help format another.

When it comes to changing the contents of a table cell we can use “innerHTML”, as above. To change the style associated with a table cell we can use

<table node>.rows[ <inx> ].cells[ <jnx> ].
style.<property> = <value>

For example, the following method calls:

<table node>.rows[ <inx> ].cells[ <jnx> ].
   style.color = “white”

<table node>.rows[ <inx> ].cells[ <jnx> ].
   style.backgroundColor = “red”

<table node>.rows[ <inx> ].cells[ <jnx> ].
   style.fontStyle = “italic”

will set the cell font colour to “white”, the cell background colour to “red”, and the cell font style to “italic”.

Summary and Recommendations

The great advantage of using Javascript and HTML DOM is that they offer the flexibility to fully customize an OBIEE dashboard, while still retaining access to native OBIEE functionality (for example, the drill down links that are dynamically generated as runtime can be accessed and manipulated).

Limitations

HTML DOM, just like HTML in general, can function differently with different browsers, so if you’re developing reports for a multi-browser environment, restrict yourself to a subset that will work on the relevant browsers (this is not unduly onerous as OBIEE is only certified to run on a very limited number of browsers in any case).

However, one important caveat, using Javascript in the manner described in this article is neither supported nor documented by Oracle. So make sure you do a “proof of principle” exercise for the particular configuration that you’ll be using before starting out on a major project.

The extent to which your use of HTML DOM will be “upgrade proof” will depend on what types of modifications you make to your HTML pages. The type of changes we’ve employed in this pivot table example are likely to be “upgrade proof” since they involve access to, and modifications of, a HTML table – it’s unlikely that pivot tables in future versions of OBIEE will be implemented using some other mechanism. If, however, you start to make use of Oracle’s own Javascript functions by calling them or by moving them from one HTML element to another, then you’re on more shaky ground. The names of these functions or their parameters may well change in the future. However, in most cases, you can be reasonably confident that a corresponding function call will still exist. For example, drill down involves a Javascript call, such as the following:

PRTDrl(saw_100_4,1,0, event)

called from the “onclick” event. If you need to move this call to, say, an icon instead, then you’ll future proof your application if you use a wrapper function to parse and return the function call, and then call the wrapper function throughout your application. If the Javascript function call changes in a subsequent release, then you’ll just have to make a single change to your custom Javascript library.

Charts are generated dynamically and don’t appear as HTML elements on the web page. You might be able to make a data independent change to a chart by editing the associated “cxml” files (though editing these files is not supported by Oracle). For “heavy duty” graphics in statistical or engineering applications you’ll probably need to use a more sophisticated graphics application and embed the resulting image in the dashboard or make it available through a link, and you’ll either have to pass the data from the web page to the graphics application or else pass an identifier that allows the graphics application to retrieve the relevant data in parallel.

Development Effort

Post-processing using HTML DOM requires a “one-off” development effort to put in place the functionality needed to support conditional formatting. But once this functionality is in place, using HTML DOM is quite efficient in terms of the development effort required. The post-processor narrative view can be reused and dragged to the bottom of any dashboard page. Most of the functionality needed to navigate and modify the HTML DOM can be coded using generic Javascript functions that can be reused, as required. The number of lines of code needed to implement most formatting requirements is modest – in the case of our pivot table example the number of lines of code per formatting condition is typically less than five: a loop, a test, and a few set style function calls. For many common formatting conditions, the coding effort can be reduced still further by creating a parameterized Javascript function. And the testing of Javascript code changes is very fast: save the file and then refresh the browser.

So, after the initial learning curve is out of the way and a small Javascript library has been built up, implementing data dependent functionality using Javascript and HTML DOM is nearly as fast as using a set of property sheets would be.

The Future

Perhaps one of the most puzzling aspects of OBIEE is why Oracle, or Siebel before them, did not provide the sort of functionality that we’ve discussed in this article. For example, it would not involve much effort to automatically provide a property sheet in which Javascript code could be entered and then automatically run at the end of each dashboard page. It would not be difficult to ensure that the contents of tables and pivot tables were available, pre-parsed, in Javascript arrays, so that all a developer had to do was to change the contents. We’ll have to wait and see what the next version of OBIEE will bring!

Everything Oracle

Home

Everything Oracle