Setting default number formats in OBIEE 11g

You can change the system wide default data format for basic numbers.

I’m not sure if there is a standard default setting, I’ve seen 0 decimal places with commas, 1 decimal place without commas recently. Either way there is an xml file in the catalog which controls this and you can change it.

The file is called _datatype_formats and is stored in the catalog directory (e.g. C:\app\obiee\instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\catalog\SampleAppLite) under \root\system\metadata

If you edit this file (take a backup first!), it comes as a single line set of xml tags, a bit of re-formatting displays the tags as:

<?xml version="1.0" encoding="utf-8"?>

<sawsavedformat:metadata xmlns:sawsavedformat="com.siebel.analytics.web/savedformat/v1.1">

<sawsavedformat:datatypeSavedFormats>

<sawsavedformat:datatypeFormat xmlns:saw="com.siebel.analytics.web/report/v1.1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" datatype="GDT:real">

<saw:displayFormat>

<saw:formatSpec suppress="repeat" wrapText="true" visibility="visible" hAlign="right" vAlign="top" imagePlacement="right" interaction="action">

<saw:dataFormat minDigits="2" maxDigits="2" commas="false" negativeType="minus" xsi:type="saw:number"/>

</saw:formatSpec>

</saw:displayFormat>

<saw:tableHeading>

<saw:displayFormat>

<saw:formatSpec/>

</saw:displayFormat>

</saw:tableHeading>

<saw:columnHeading>

<saw:displayFormat>

<saw:formatSpec/>

</saw:displayFormat>

</saw:columnHeading>

</sawsavedformat:datatypeFormat>

</sawsavedformat:datatypeSavedFormats>

</sawsavedformat:metadata>

If you look at the <saw:dataFormat tag, 10 lines from the top there are some parameters:

minDigits, MaxDigits, commas and negativeType

minDigits and MaxDigits control the number of decimal places. Not sure what the difference between the two are, but it seems to prefer them both set to the same number, i.e. 2 if you want 2 decimal places.

Commas controls whether the thousands separater comma is displayed, valid values are true or false

negativeType controls how negative numbers are displayed. Valid values are:

minus                   black numbers with the minus sign

redMinus            red numbers with the minus sign

paren                   black numbers inside parentheses

redParen             red numbers inside parentheses

Once you saved the file just restart presentation services to see the effect.

Here’s a simple example using the sample sales subject area:

With default settings of 0 decimal places and commas = true

After changing the file to show 2 decimal places and commas = false

There’s also a tag in this filed called formatSpec with some of the standard formatting settings, I’ll have a play with these sometime soon.