Some interesting decimal problem

OBIEE tips and tricks video from BICG
April 14, 2011
Excellent OBIEE 11g video in high definition
June 26, 2011

Someone was using the AVG function in a column. The result should be 4,7 but answers displayed only 4. He modified the data format and added to the configuration to show 2 decimal places but he saw this value 4,00. He was using the formula like this AVG([Column_name]).

Oracle’s support decided figured out that:

The AVG function works as a division (in a Math context).
If the number you divide is an integer, the result will be an integer.
If the number you divide is a double, the result will be a double, this is how answers works.

In this case, the column had integer values, so the result should be an integer that’s the reason why customer saw a 4 instead 4,7.

If you divide an integer you will get an integer as a result. If you divide a double you will get a double as a result.

And provided the instructions:

1.- Go to column and click on the “Edit Formula” option.
2.- Click on the Function button.
3.- Select the CAST function in the Conversion section and click on the OK button.
4.- The formula should be looked like this CAST(“[column_name]” AS Double).
5.- Click on the Function button again.
6.- Select the AVG function in the Aggregation section and click on the OK button.
7.-The formula should be looked like this Avg(CAST(“[column_name]” AS Double)).
8.- Click on the “Column properties” option.
9.- Click on the “Data Format” tab and check in the “Override Default Data Format”.
10.- Choose the value 2 in the “Decimal Places” section.
11.- Click on the Ok button and click on the “Display Results” button.

1 Comment

  1. Hy Andry,

    If you check this page of wikipedia on the Division of integer
    http://en.wikipedia.org/wiki/Division_%28mathematics%29#Division_of_integers
    You may suppose that the BI Server is written in C…

    A other global solution is to change the datatype in the repository (with the cast function).

    Cheers
    Nico

Leave a Reply