Numeric values rounded automatically in DeskI Report

[Former Member]

Dear Friends,

Need your expertise in solving the following issue while doing a report using u2018Desktop Intelligenceu2019.

One of the report automatically converts (Round off) the Spend Amount (Numeric) to the nearest thousands.

Whereas the same query gives the full values in u2018Webiu2019.

I need to show the actual value without any rounding in the Desktop Intelligence report.

Actually I didnu2019t use any rounding function while designing the universe.

I have tried many methods like formatting inside the report, formatting the object in universeu2026but nothing works.

Iu2019ve also verified the actual table value and it consists the exact value without any rounding.

Hope you can advise me how to overcome this issue.

FYI, I am using BO XIr3 & the database is MS SQL 2008.

Thanks in advance.

- Sivakumar

Singapore

[Former Member · Accepted solution]

Hi Guru,

Could you please review the following information?

This is by design that BOXIR3 is calculating the values and rounding it off.

What is happening in BOXIR3 it is taking into account the decimal values up to 15

decimal places.

I found that the 0.00 which are displayed in the report is not exactly zero it is

something 0.00000000000002870 and it rounds of to 0.002 when I checked the list of

values then for the values like 0.1or 0.002 it is appending u2013 before the digit that is why

you are getting the issue itu2019s a IEEE floating point limitation which is explained below.

Solution1:

= If Round (<Totaal faktuurbedrag>, 2)

Soluion2:

=ToNumber(FormatNumber(<Totaal faktuurbedrag>,u201D0.00u201D))

The issue

Error when doing subtraction or comparison between two float values.

Sometimes running a sum gives incorrect results when positive and negative numbers should give zero.

Letu2019s consider the list:

359.00

62.33

735.20

-359.00

-62.33

-735.20

This list of values gives 0 as the result of the sum within Ms Excel.

The same list gives 0.000000000000113686837721616 within BusinessObjects.

The cause

This is a limitation of the IEEE standard with float values. According to the IEEE

standard, a double type float number can only have 15 significant digits.

Look at our example, the difference of two variables are not zero but in the range of

the specification,

359.0000000000000000000000

^ digitals behind can't be trusted

62.33000000000000000000000

^ digitals behind can't be trusted

735.2000000000000000000000

^ digitals behind can't be trusted

-359.000000000000000000000

^ digitals behind can't be trusted

-62.33000000000000000000000

^ digitals behind can't be trusted

-735.200000000000000000000

^ digitals behind can't be trusted

-

---

0.000000000000113686837721616

^ so digitals behind can't be trusted

Even if the result within Ms Excel is correct for the list of values from the example

above, if we now consider the list below, you could notice that applying a SUM gives

2.38742359215394E-12 within Ms Excel instead of the expected value 0. *

20359.00

62.33

735.20

-20359.00

-62.33

-735.20

• Make sure to add enough digits after the decimal separator to notice this strange result.

The C++ code below shows that we can get the same strange strange behavior too without involving any libraries from BusinessObjects. You can use and run the C++ project from the attachment as well.

// FP00.cpp : Defines the entry point for the console application.

//

#include "stdafx.h"

int main(int argc, char* argv[])

{

double d = 0.0;

d += 359.0;

d += 62.33;

d += 735.2;

d += -359.0;

d += -62.33;

d += -735.2;

printf ("%.15g", d);

return 0;

}

This code returns -1.13686837721616e-013 as the result of the sum. This is what BusinessObjects displays.

Useful Links

Here are some interesting links that talk about IEEE standard for floating point numbers. The first link is most useful.

http://support.microsoft.com/kb/q42980/

http://www.math.byu.edu/~schow/work/IEEEFloatingPoint.htm

http://docs.sun.com/source/806-3568/ncg_math.html

http://cch.loria.fr/documentation/IEEE754/

Workaround could be to use Binary Coded Decimal types instead of Floating Point types. This could help to avoid the limitation of 15 digits imposed by the floating point types on 64 bytes that BusinessObjects uses.

Regards,

Sarbhjeet Kaur

[Former Member · Accepted solution]

Dear Kaur

Thanks for you quick response! Appreciate it!!

But, I am facing different kind of issue.

This is what I get from Desktop Intelligence:

Keyperiod Mediatype Market Spend

200301 Outdoor Australia $4.59

200301 Magazines Hong Kong $52.10

200301 Magazines India $25.61

The Query result this way as a report in WebI

Keyperiod Mediatype Market Spend

200301 Outdoor Australia $4588.33

200301 Magazines Hong Kong $52095.60

200301 Magazines India $25607.1

The data in Table are as per the Webi.

I didn't use any rounding funtion either in Universe or Report design.

When I view query result in 'View Data' result page, the figures comes to report as a rounded value.

Please help to resolve this if you have any ideas.

Thanks again.

- Sivakumar

Omnicom Media Group Inc. Singapore

[Former Member · Accepted solution]

Hi Guru,

It seems that some problem is there with the display of the number in the particular format.

-Format Cell and add the format as ###.## and select the format for the language.

-Double click on the file $default$.ret present in the language folder in the Templates and check the format.

-Also try to use Format Number function so deski doesnu2019t round off the number.

-Test the issue by using comma as a separator instead of Decimal.

I hope this will help you.

Regards,

Sarbhjeet Kaur

[Former Member · Accepted solution]

Hi Guru,

Could you please also test the following solutions:

Solution1:

1.Change the object definition in the designer by adding Zero (0) or by multiplying with One (1).

Ex: SalesRevenue*1

Or

SalesRevenue+0

2. Save the Universe and test the issue.

Solution2:

Use database-level syntax to convert the numeric database field to a string so that Deski Reports will interpret it as a string type, and display all of the characters.

For example, in Oracle, cast the number using to_char () and in Microsoft SQL Server use CAST() or STR() prior to adding the field to the report.

Regards,

Sarbhjeet Kaur

[Former Member · Accepted solution]

Thanks Sarbhjeet Kaur

The CAST() option really works!

I'd changed the Spend Column (Numeric) in Universe design from SUM(apbidwh.Visa.CompetitiveSpendSum.Spend) to

Cast(apbidwh.Visa.CompetitiveSpendSum.Spend as float) and it WORKS!!!

Thank you very much!

Have a Nice Day@!

- Sivakumar

Omnicom Media Group Inc.

Singapore