Obiee Cross conditional formatting on a pivot

OBIEE does not support cross column conditional formatting in Pivot Tables.
For instance, it's not possible to set a red background of a column based on the value of an other column.

Below is a workaround to be able to perform this kind of formatting and the principal idea is to transform a table view in a pivot table.

What is a pivot table ?

A pivot table is just the same table where :

• the pivot column has been suppressed
• for each distinct value of the pivot column, a column with a conditional sum is created.

Example :
If I want to pivot this table from the SH schema :

Promo Category	Channel Desc	Amount Sold
internet	Direct Sales	2,658,953
internet	Internet	642,251
internet	Partners	1,097,558
NO PROMOTION	Direct Sales	115,549,450
NO PROMOTION	Internet	26,154,910
NO PROMOTION	Partners	51,682,188
post	Tele Sales	554,853
TV	Direct Sales	696,703
TV	Internet	4,166

If the pivot column is the column “Channel Desc”, we have 5 distinct values :

Direct Sales
Internet
Partners
Tele Sales

And the pivot table will result of this sql statement :

SELECT
   "Promo Category",
   sum(case "Channel Desc" = 'Direct Sales' then Amount Sold else 0 end) "Direct Sales",
   sum(case "Channel Desc" = 'Internet' then Amount Sold else 0 end) "Internet",
   sum(case "Channel Desc" = 'Partners' then Amount Sold else 0 end) "Partners",
   sum(case "Channel Desc" = 'Tele Sales' then Amount Sold else 0 end) "Tele Sales"
FROM
   FactTable,
   DimensionTable,
   ...
GROUP BY
   "Promo Category";

Transformation of a table in a pivot table

For the query above, we need 6 column :

• one for the “Promo Category”
• 4 formula columns for each distinct value of the “Channel Desc”
• one for the cross conditional formatting

In each formula column and for each distinct value of “Channel Desc”, enter a filter formula.

Below an example for the value 'Direct Sales' :

FILTER("Sales Facts"."Amount Sold" USING (Channels."Channel Desc" = 'Direct Sales'))

And here for the value “Internet” :

Conditional Formatting

After completing the last three columns, it's finally possible to apply a formatting and to hide the conditional column.

The Result

This method has a weak point is that you must know the number of distinct value in advance. For instance, if a new distinct value appear in the column “Channel Desc”, you must change your report.

To resolve this issue, you can also achieve a pivot statement with a stored procedure or wait for this functionality.

Below the pivot view :

First Part : I hate Null : Null and Number

Why I hate Null !

C:Documents AND SettingsNicolas>sqlplus gerardnico/Password
 
SQL*Plus: Release 10.2.0.4.0 - Production ON Wed Apr 8 15:41:24 2009
 
Copyright (c) 1982, 2007, Oracle.  ALL Rights Reserved.
 
 
Connected TO:
Oracle DATABASE 10g Enterprise Edition Release 10.2.0.4.0 - Production
WITH the Partitioning, OLAP, DATA Mining AND Real Application Testing options
 
gerardnico@orcl>create TABLE IHateNull (id number NOT NULL, val1 number NULL, val2 number NULL);
 
TABLE created.
 
gerardnico@orcl>insert INTO IHateNull VALUES (1,2,NULL);
 
1 row created.
 
gerardnico@orcl>insert INTO IHateNull VALUES (1,NULL,1);
 
1 row created.
 
gerardnico@orcl>insert INTO IHateNull VALUES (1,0,0);
 
1 row created.
 
gerardnico@orcl>insert INTO IHateNull VALUES (1,1,1);
 
1 row created.
 
gerardnico@orcl>select * FROM IHateNull WHERE ( val1 + val2 ) <> 0;
 
        ID       VAL1       VAL2
         1          1          1
 
gerardnico@orcl>select * FROM IHateNull WHERE ( nvl(val1,0) + nvl(val2,0) ) <> 0;
 
        ID       VAL1       VAL2
         1          2
         1                     1
         1          1          1

I hate Null (null, decode, date and toad)

When you use Toad with a date and a decode statement, you can have the surprise that two different date format appear. Why ?

The case

First Statement : Date Format : 01/11/95

Second Statement : Date Format : 11-01-95

Where and why we have this difference ?

Why this difference in the date format ?

Because when you use a decode statement, Toad (of Oracle) return as type the type of the first argument from the decode function.

In the first case, a NULL value is the first returned and NULL have a data type of varchar.
In the second case, the column day with the data type of date is returned.

What we obtain in SQL Plus ?

The date format returned by Oracle is governed by the parameters NLS_DATE_FORMAT.

hr@orcl>SELECT value FROM nls_session_parameters WHERE parameter='NLS_DATE_FORMAT';

VALUE
DD-MON-RR

hr@orcl>select decode(0,0,day, NULL) from d_time
  2  where day is not null
  3  and rownum = 1;

DECODE(0,
11-JAN-95

hr@orcl>select decode(0,1,NULL,day) from d_time
  2  where day is not null
  3  and rownum = 1;

DECODE(0,
11-JAN-95

hr@orcl>

The same date format. So, the formatting occurs in Toad.

What govern the date format in Toad

Go to View > Toad Option > Data Grids > Data.

You see ?

Obiee – How to mix string and number data type in one column and get a sum ?

Sometimes, you want to mix number and string data type in one column because you want to send a conditional message :

For instance, :

• if value > 1000, write “To Big” else value
• if value is null, write “No Data” else value

If you write a function, on this way as :

CASE WHEN  Measures."Count Passed" IS NULL THEN 'No Data' ELSE Measures."Count Passed" END

You will have this error :

[nQSError: 10058] A general error has occurred. [nQSError: 22027] Union of non-compatible types. (HY000)
SQL Issued: SELECT "Business Rule"."Rule Name", CASE WHEN Measures."Count Passed" IS NULL THEN 'No Data'
ELSE Measures."Count Passed" END,  Measures."Count Passed" FROM "MDM Vendor"
nQSError: 10058 A general error has occurred. nQSError: 22027 Union of non-compatible types. (HY000)

If you have already used a software as crystal report, you can't mix of course two types of data in one field but you can have two fields one above the other. It's why I have done in this solution.

But with css, we can go further and have a single column.

How ?

• using the content property of css
• using the conditional formatting of OBIEE

The Content property of Css

The content property allow us to format an HTML element by adding an additional content (string, image, …) to a HTML element.

This property can not be use in an inline style such as :

<p style="color: blue">This is a paragraph with the color blue</p>

because it need a :before or :after pseudo-elements.

We will therefore add two class in the OBIEE stylesheet.

How to add a custom class to the OBIEE Style Sheet

First, we will open the file custom.css and add this two class to demonstrate this capabilities.

/* This file is intentionally empty.  Styles can be defined in a customized */
/* version of this file placed in the SiebelAnalyticsData/Web/Res folder;   */
/* and can then be referenced from the "Custom CSS Style Options" area      */
/* within some Siebel Answers Formatting Dialogs.  These styles will only   */
/* affect HTML content.                                                     */
 
td.ClassIsNull:before { content: "This value is null" }
td.ClassIsGreater:before { content: "This value is greater then zero : " }

The custom.css file is located in a standard installation in the repertory :

Oracle_BiHomeoc4j_bij2eehomeapplicationsanalyticsanalyticsanalyticsRess_NewFrontiersb_mozilla_4

You can check its location with a right click on a OBIEE dashboardpage, select show source code and search “custom.css”.

In my dashboard, you can see below that the custom.css is located here :

• OBIEEAnalyticsWebSiteRoot/analyticsRes/s_NewFrontiers/b_mozilla_4/custom.css

<link href="analyticsRes/s_NewFrontiers/b_mozilla_4/custom.css" type="text/css" rel="stylesheet"></link>
<script language="javascript" src="res/b_mozilla/browserdom.js"></script>
<script language="javascript" src="res/b_mozilla/common.js"></script>
 
<script language="javascript" src="res/b_mozilla/viewhelper.js"></script>
<script language="javascript" src="res/b_mozilla/menu.js"></script>
<script language="javascript" src="res/b_mozilla/xmluiframework.js"></script>
<script language="javascript" src="res/b_mozilla/common/drillinfo.js"></script>
<script language="javascript" src="res/b_mozilla/chartview.js"></script>
<script language="javascript" src="res/b_mozilla/pivot.js"></script>

Add a conditional formatting

Now that the worst is done, you can add a conditional formatting to the column in its property :

The Result

This solution has one limitation, it works of course only in HTML.