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
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): 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.

17 Responses to “Setting Custom Data Format in OBIEE Answers”

  1. Carl says:

    Tried this #,#.0;-#,#.0;0 and I still get null values. Any suggestions?

  2. Rafal says:

    Hi Carl,

    The described solution should work.

    The custom formatting may be not visible if there are security-sensitive columns. In that case unauthorized users may always see the values in such columns as nulls. Is that the case here?

  3. KN says:

    Hi Rafal,

    I tried #,#.0,,M to show values in a graph along an axis with a thousands separator, one decimal point and label M.
    The issue I am facing is that it works for a few axis fields on some graphs while it does not work for axis fields of other graphs. What can be the reasons?

  4. Rafal says:

    I haven’t faced this issue, but perhaps there are some differences between these graphs like formatting, graph and column properties or the type of the graph that prevent the custom display of the values. Hope that helps!

  5. Eric Brosseau says:

    Thank you Rafal for this very informative post. I am using custom number formatting to display the currency code based on the user’s preference ( [$:currencyTagColumn=Subjectarea.table.column] ), I am wondering whether a mask can be applied in this case to control the number decimals displayed…

    Thank you,

    Eric

  6. Rafal says:

    Hi Eric,
    If you use a currency tag [$:currencyTagColumn=Subjectarea.table.column] in Custom Number Format you can’t specify further the decimals.

  7. Anson says:

    Hi Rafal,
    Is there a way to use the custom numeric format to display 5 decimal places that is not rounded? For example, I want 0.99999999 to show up in the reports as 0.99999 (instead of 1.0 as it is right now) In other words, I’m looking a function to truncate 5 decimal places, and not do any rounding.
    I’m imagining something like “select trunc(0.9999999, 5) from dual” to show up as 0.99999. Is this possible?
    Much thanks,

  8. Anson says:

    I found the answer to the 0.99999999 trunc issue. I used the truncate function found in the Edit Formula section. Just choose edit formula, and use the Truncate(your_table.your_column, 5)

  9. Pathak says:

    how can we show the revenue numerical values in terms of indian currency in obiee 11.1.1.7..for eg currently value with 1000’s separator is coming as 122,333,456,678 but I want to display as1,22,33,34,56,678…thanks in advance

  10. Mukti says:

    Is there a way to format text and display in “Proper” case. The data is in UPPER case.

    Thanks,
    Mukti

  11. Rafal says:

    Hi Mukti,

    If the transformation is relatively simple this can be done with OBIEE string functions. This may make sense for example if we only want to lowercase all the characters except for the first one.

    So we keep the first letter uppercase and lowercase all the rest:

    LEFT(“Column Name”,1)||LOWER(SUBSTRING(“Column Name” FROM 2))

    If a more complex logic to change strings is necessary, it can be done using other OBIEE string functions like LOCATE (to locate the position of spaces) or analytic functions like INSTR or Regular Expressions to query directly the data warehouse.

    However please keep in mind that such transformation would put some burden on the BI Server (string functions) or data warehouse (when using analytic functions), so it would be advisable to get the proper data format on the ETL stage.

  12. Galav says:

    Hello Rafal,

    I have data which indicates numeric data in GB or TB usage. I wanted to define this values based on GB or TB or Bytes.

    Can you please suggest me something for this kind of data.

    Example: Space Allocated : 500GB if its 2 TB
    Space Used : 200GB if its 2 TB then my data shows depends how much data user used. May be 100GB so based on Allocated I have to give label to data.

    Please advise.

    Thanks,
    Galav

  13. Rafal says:

    Hi Galav,

    I’m not sure if I understood your requirement but you can either do this with CASE WHEN statement (so if column_value >= 1000 THEN column_value ||’TB’ ELSE column_value ||’GB’) or you set the custom formatting to be displayed in GB or TB (500GB or 0.5TB) as it in example for millions or billions mentioned in the article.

  14. Jim says:

    How can I left justify the $ for currency while the value is right?

  15. Rafal says:

    Hi Jim,

    I don’t think it’s possible with custom format settings.
    What you can do is to add a dummy column with ‘$’ and place it before the actual measure column in the table. That way you can have the $ justified left and its values justified right. You can also remove the right cell borders for the dummy column and remove left cell borders for the measure so it would appear visually as one column.

  16. Jack says:

    To change the date format for everyone I use Catalog Manager, System Folders, metadata, _datatype_formats.
    Edit the XML: customFormat=”MM/DD/YYYY” />
    Click OK.
    Close the catalog.
    Done

  17. Shibu says:

    Hi,
    I want to display another character column value if the numeric column in NULL

    ##0;-##0;NA

    ##0;-##0; (need to display another column char value)

Leave a Comment

privacy policy - Copyright © 2000-2010 ClearPeaks

topnav