Strange number format/rounding in Excel Output

[CommandoCognos]

Hi,

when exporting a Report to Excel it turns out that somehow Excel alters the number being stored in Excel and adds unnecessary and strange decimals to the number. See screenshot attached or example below:

Code Select Expand


DB Value: -111.05
Excel: -111.049999999999



The value is stored with 2 decimals in the Oracle Database as NUMBER. FWM recognize the data type as being Float64. In the FWM there is one case statement on this column AB:

Code Select Expand

case <nVarChar field>
when to_nchar('XY') then
          AB else
0
end

Also the totals are wrongly calculated and there is instead of a 0.00 a value close to 0, i.e. 3,231238787-E8 (probably the result of the strange values)

Cognos Version: 10.2.1
Excel 2007, 2002

When I extract the query in the database it works fine. Have you experienced something similar? Do you have any idea how to fix it?

Thanks and cheers,
CommandoCognos

[cvamarley]

Why not use _round function to round off to 2 decimals at data item level - just to replicate the column as in Oracle DB ?

[CommandoCognos]

Hey,

thanks for you reply. Actually I tried that and it solves indeed the first issue within the cells. But still, Excel calculates somehow with the strange number since the total is still not correct. But I'm mostly wondering where the figure comes from when it is purely stored with 2 decimals in the database. Is it possible that the number gets altered when applying case statements on it?

[cvamarley]

Sorry for the late reply..did you try casting the column to number ? cast([dataitem],number)