Scenario:
Sometimes it is necessary to display numeric values
in specified format or replace the null values in table or pivot
table with zeros or custom text. OBIEE Answers and Dashboards give us
a possibility to customize the data masks for presentation. This
could be useful when we want to change the display of data for the
purpose of a given report. Using Custom Data Format feature we can
change the masks for numeric values, change the display of null
values or show dates in custom format.
Solution:
1) 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.
2) Click on Data Format tab and check Override Default
Data Format. Select Treat Number As Custom from the list.
3) Now wrt your requirement , type the below examples in "Custom Numeric Format" tab:
a) We can display the values in thousands with K
(kilo) letter
#,#.0,K
b) Values in millions with M
#,#.0,,M
c) Values in billions with B
#,#.0,,,B
d) Add a currency symbol
£# or $#
How it works is:
‘#’ 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 thousands separator (‘,’ for
thousands; ‘,,’ for millions, ‘,,,’ for billions etc.).
‘K’,’M’,’B’, ‘$’,‘£’ 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.
Dealing with null values in numeric fields
Using Custom Data Format we can also specify what
should be shown instead of null values, ie we can replace Null values with '0', 'No data' or 'N/A' .
1) For showing zeros (0) instead of nulls, use the below syntax:
#,##0;-#,##0;0
(which can be read as: number with one decimal place
for a positive value, minus sign (-) and number with one decimal
place for a negative value, zero (0) for a null value)
The null values will be replaced with zero.
2) Replace Null with custom text like ‘Not available’ or ‘No data’ or '-':
#,##0;-#,##0;no data
#,##0;-#,##0; -
Our pivot table now shows something other than null values.
Dealing with null values in text fields
For text values the syntax for custom text format is
non-null value mask, null value mask.
Set Custom Text Format to: @;No description
The null values will be replaced with ‘No
description’ text.
Cheers :)
