Suppressing Footer by Sum Summary Problem - MSSQL float field?

[Former Member]

I'm evaluating CR2008 and came to a problem I can't find an answer to.

I'm summarizing quantity of shares in a transaction log and if the sum is zero should suppress the Footer as there is no quantity to report. This works for 95%+ of cases but there are instances where the zero is not being supressed. I'm not sure this is a problem with MSSQL's float field or if it is a problem with CrystalReports.

I have a suppression formula as follows:

If Sum({Transactions.Quanity, {Securities.Description}) = 0 Then
    True
Else
    False

This works as I stated for most instances. But some it will display a 0.0000000000 (testing with the largest rounding and decimals to see if there is a rounding problem).

The values are stored in a MSSQL database and the quantity field is of type 'float'. As a test I queried the database for one of the failing instances with query "select quantity from transactions where portfolioid=XXXX and symbolid=YYYY". This returns 52.732 and -52.732. If I where to change the suppression formula to

If Sum({Transactions.Quanity, {Securities.Description}) <= 0.0000000001 And
Sum({Transactions.Quanity, {Securities.Description}) >= -0.0000000001 Then
    True
Else
    False

the suppression works correctly. Could someone shed some light on this issue?

[Former Member]

I would guess that some of the numbers in your database that are displaying as zero, are not truly zero and crytal has a limit to the number of decimal places it will display. You could test the fields by multiplying the number field by 1000000000000 and see what you get if you are curious.

[Former Member]

go to file

report options

check off CONVERT NULL VALUES check boxes, this pulls in the null values

[Former Member]

Good point Sharon.

My favorite way to solve this is a formula

if IsNull() then 0 else

Edited by: DebiHerbert on Nov 10, 2010 3:44 PM

[Former Member]

Debi is the one that is correct but it still is something weird about it. When I do the multiplication by 10000000000, I get some trailing fractions converted into a zero value as expected like 527320000001. But if I do the same multiplication in an query on MSSQL, ie. "select quantity * 10000000000 from transactions where portfolioid=XXXX and symbolid=YYYY" I do not get the trailing fractions but the numbers I would hope to get 527320000000. Somewhere along the way the float is converted into a number on CrystalReports and the fractions are not kept as is on database. Null values are not allowed on database schema so those would not be an issue.

[Former Member]

So it looks like you will need to use this (your) formula for your formating.

If Sum({Transactions.Quanity, {Securities.Description}) <= 0.0000000001 And
Sum({Transactions.Quanity, {Securities.Description}) >= -0.0000000001 Then
    True
Else
    False

[Former Member]

You could talk to your database designers/administrator and ask where the number comes from. It looks like it is calculated somewhere.

[Former Member]

Yes I think you are correct. Thanks for your help though.