datawarehouse concepts and related blogs: 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>