Replace Null Values in an OBIEE

Hello All,

Suppose user wanted to display numeric values in specified format, replace the null values in table, or pivot table with zeros or custom text.

Output Format: Below is the screen shot he is looking to generate. He wanted to put the Name in front of Cost Value

Step:

1.       Create a simple report. I am using the Patil RPD here.

2.       A pivot table view of this data would look something like this by default:

3.      

4.       Now, go to Criteria tab and Go to edit column properties. (Using custom    data format we can specify the custom formatting of columns used in an analysis. This formatting will be applied in report’s tables and pivot tables. To edit a data format for a given column, right click on it and choose Column Properties.)

5.      Select the Data formate  and Check the “Override Default Data format” and Select the Cutom and put the format Name as per screen shot

Here are some examples of using custom numeric masks:

- We can display the values in thousands with K (kilo) letter

#,#.0,Rakesh

- Values in millions with Patil

#,#.0,,Patil

- Values in billions with P

#,#.0,,,Patil

- Add a currency symbol

£# or $#

Explanation

‘#’ is a number sign indicating significant digits

‘#,#’ adds a comma separator for thousands

‘.0’ indicates the number of decimal places (‘0’ for round number, ‘.0’ for one decimal place, ‘.00’ for two, etc.)

‘,’ is a thousand separator (‘,’ for thousands; ‘,,’ for millions, ‘,,,’ for billions etc.)

‘Rakesh’,Patil’,’P’, ‘$’,‘£’ are characters that can be added after or before the number mask

The syntax for custom data formatting for numeric values is:

positive value mask ; negative value mask ; null mask

If we specify the mask without semicolon, the changes will apply to both positive and negative values.

- We can indicate positive values with plus sign (+) and negative values with minus sign (-)

+ #,#.0;- #,#.0

6.       Click on Ok and check the report you will see same output how user was looking.