About
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 to get the above, you may get this kind of formula :
CASE
WHEN Measures."Count Passed" IS NULL THEN 'No Data'
ELSE Measures."Count Passed"
END
The problem is then OBIEE will give an “Union of non-compatible types” 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 such 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 one possible solution.
But with the help of css, we can go further and have a single column.
How ?
- using the content property of css
- and using the conditional formatting functionality of OBIEE
Articles Related
The Content property of Css
The content property allow 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 the skin directory :
Your_skin\b_mozilla_4
To find your skin directory, see this article : OBIEE 10G/11.1.1.7G - How to create a New Style and Skin ?
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.
Support
Don't work with all browser
See here for the compatibility : :before/:after and content browser compatibility