Monday, January 31, 2011

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

No comments:

Post a Comment