Post date: 01-Mar-2013 10:30:47
To customize data format of columns used in an analysis. This can applied in report’s tables and pivot tables. Right click on column to edit data format, and choose Column Properties.
For numeric values #
On Data Format tab, check Override Default Data Format. Select Treat Number As Custom from the list.
Here are some examples of custom numeric masks:
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 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.
Custom Data Format for dates #
By default the date is displayed as it is specified in user’s locale definition file which contains setting for displaying language, date format etc. To apply Custom Data Formatting:
Change the Custom Date Format to DD-MMM-YYYY. The results are displayed as following:
Date can be separated using characters like slash (/) or hyphen (-). Here is a short list of formats used for date display:
For more date formatting refer to OBIEE User’s Guide 11g Release 1 (11.1.1): http://docs.oracle.com/cd/E23943_01/bi.1111/e10544/format.htm#BIIEDDAE
Dealing with null values in numeric fields #
To specify what should be shown instead of Null values.The sytax for custom data format for numeric values is positive value mask; negative value mask; null mask.
For showing zeros (0) instead of nulls: #,#.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.
Instead of zero we can add a custom text like ‘Not available’ or ‘No data’: #,#.0;-#,#.0;No data
The null values will be replaced with ‘No data’ text.
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.