OBIEE - How to mix String and Number data type in one column and obtain a sum ?

> Reporting and Data Access Tools > Oracle Business Intelligence (OBIEE/Siebel Analytics) > OBIEE - BI Presentation Service (SAW/OBIPS)

1 - 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 ?

3 - 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.

Advertising

4 - 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 ?

5 - Add a conditional formatting

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

6 - The Result

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

7 - Support

7.1 - Don't work with all browser

See here for the compatibility : :before/:after and content browser compatibility

Advertising
dat/obiee/obips/datatype_string_number_in_one_column.txt · Last modified: 2019/02/09 11:41 by gerardnico