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 [[dat:obiee:presentation_service:obiee_character_number_in_one_column|this solution]].

But with [[language/css/start|css]], we can go further and have a single column.

How ?
* using the content property of [[language/css/start|css]]
* using the conditional formatting of OBIEE

===== The Content property of Css =====

The [[http://www.w3.org/TR/CSS21/generate.html#content|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 :

This is a paragraph with the color blue

because it need a [[http://www.w3.org/TR/CSS21/generate.html#before-after-content|: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

===== Add a conditional formatting =====

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

{{:dat:obiee:presentation_service:obiee_conditionnal_formating_class.jpg|}}

===== The Result =====

{{:dat:obiee:presentation_service:obiee_table_mix_number_string_sum.jpg|}}

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

 

View the original article here