Setting Custom Data Format in OBIEE Answers

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.  In this article I will provide 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
DDDay of the month in two-digit format (01)
DDDAbbreviated name of the day of the week in the user’s locale (Mon for Monday)
DDDDFull name of the day of the week in the user’s locale (Monday)
MNumeric month (1 for January)
MMNumeric month in two digit format (01 for January)
MMMAbbreviated name of the month in the user’s locale (Jan)
MMMMFull name of the month in the user’s locale (January)
YYYear in two-digit format (01 for 2001)
YYYYYear 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): http://docs.oracle.com/cd/E23943_01/bi.1111/e10544/format.htm#BIIEDDAE

 

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.

 

 

Conclusion

 

Using Custom Data Format gives us the possibility to change the display of columns in tables and pivot tables in reports where default data format is not suitable. By applying small changes in data masks we can enhance the presentation of data in a report.

Rafal O
rafal.ostaszewski@clearpeaks.com