Setting
Custom Data Format in OBIEE Answers
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. Here provides some examples on how to deal with
custom data formatting for numeric values, dates and null values.
Introduction
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.
Custom Data Format
for numeric values
Click on Data Format tab and check
Override Default Data Format. Select Treat Number As Custom from the list.
Here are some examples of using custom
numeric masks:
- We can display the values in
thousands with K (kilo) letter
#,#.0,K
- Values in millions with M
#,#.0,,M
- Values in billions with B
#,#.0,,,B
- 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 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.
- We can indicate positive values with
plus sign (+) and negative values with minus sign (-)
+ #,#.0;-
#,#.0
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. We can change that using Custom Data
Formatting.
Change the Custom Date Format to
DD-MMM-YYYY. The results are displayed as following:
We can separate the date using
characters such as slash (/) or hyphen (-). Here is a short list of
formats used for date display:
[FMT:dateShort]
|
Date
in the locale's short date format
|
[FMT:dateLong]
|
Date
in the locale's long date format
|
DD
|
Day
of the month in two-digit format (01)
|
DDD
|
Abbreviated
name of the day of the week in the user's locale (Mon for Monday)
|
DDDD
|
Full
name of the day of the week in the user's locale (Monday)
|
M
|
Numeric
month (1 for January)
|
MM
|
Numeric
month in two digit format (01 for January)
|
MMM
|
Abbreviated
name of the month in the user's locale (Jan)
|
MMMM
|
Full
name of the month in the user's locale (January)
|
YY
|
Year
in two-digit format (01 for 2001)
|
YYYY
|
Year
in four- digit format (2001)
|
For more date formatting see the Custom
Format Strings in Oracle® Fusion Middleware User's Guide for Oracle Business
Intelligence Enterprise Edition 11g Release 1 (11.1.1):
Dealing with null
values in numeric fields
Using Custom Data Format we can also
specify what should be shown instead of null values. As we’ve seen before, 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.
No comments:
Post a Comment