OBIEE 11.1.1.7- After Migration Formula Output Shows Integer rather than showing as Decimal

Long back when I was at customer site and helping business users to build ad hoc reports on OBIA Financial Analytics are, I have come across a problem, a formula column which is supposed to show decimal number was showing me integer. I initially thought this might be problem in the earlier version of OBIEE 11g which was not stable and has number of bugs to deal with.

Recently I have seen similar problem raised on technical forum by a consultant. I have tried number of things to resolve this issue, most often the solution works for couple of situations and then again starts showing integer rather than decimal. This blog is an attempt to list down all options which I have tried to resolve this issue which in vain and also list down the solution which worked as expected.

Problem Description

When two numeric values of data type DOUBLE (defined in BI repository) are calculated with the following formula "Facts - AP Balance"."Closing Amount" /"Facts - AP Balance"."Opening Amount" the result is shown as INTEGER rather than a number with decimal places. This is not customer would expected to see the result. A sample report shown below has a column Revenue/Unit Cost is shown as integer instead decimal number.

First Attempt

I have started looking into this issue; the first come in my mind is to change the format of the column. I have changed the format to decimal, but it did not work. I have also tried to use default formatting for columns or data types; it seems it does not work for calculated column. Setting up data format is very important, I would recommend to set decimal places as 2 as shown below.

In short setting up column format and data types did not work.

 

Second Attempt

For my second attempt I have decided to use CAST statement, which converts column format to another one (e.g. integer to double). I have used CAST statement to covert integer into decimal. It does work, but after couple of minutes it came back as integer same as what we started with.

In short CAST statement which I have used to convert the column format from integer to double is not an answer to deal with this problem.

Third Attempt

I have decided to go with a different approach, I have decided to multiply and divide by a constant to both the measures used in the formula e.g. 1.0.

"Facts - AP Balance"."Closing Amount"*1.0 /"Facts - AP Balance"."Opening Amount"*1.0

The formula which I have used for "Facts - AP Balance"."Closing Amount"*1.0 /"Facts - AP Balance"."Opening Amount"*1.0 is as below. The attempt worked the way I want it to work. The report shows the decimal rather than integer. 

 

I believe it does not work at global level, there is no setting which can ensure that the formula should return decimal and display in decimal format on the report.

I hope you find this information useful.