Formula based off of summed value incorrect

[Luvsql]

I have the following report based off of 2 tables (CR 8.5). Table 1 links to Table 2 equal join on AccountNo. Table 1 contains the details from the AccountNo and Table 2 contains the endingbalance for each Account on a one to one join.

GH1 Account No from Table1
Detail contains Sales from Table 1
GF1 Sum(Sales)
GF1 also has a field from Table 2 called endingbalance. I then created a calulated field=Sum(sales)-ending balance and this is placed in the GF1.

When I preview the report, the summed totals equal the endingbalance amounts, and the Calc field=0.00 for most of them except a few, which it is printed (0.01). If the Sum(sales) shows as say $100.00 and the endingbalance shows as $100.00, why does the calc field not equal zero? If it behaved this way for all groups, then I would assume it was an issue, but why would it only calculated only a certain groups incorrectly?

 

[synapsevampire]

Sounds like a rounding concern to me.

Try adding a decimal of precision, you'll likely find something unexpected.

It may also be that there are different datatypes involved, and a currency vs. a numeric is the culprit.

In that case I would create a SQL Expression and make sure that both values are CAST to the same data type.

-k

 

[Luvsql]

I have found one record in Table 1 that has more than 2 dps. However, I have formatted the sales amount in the detail to decimal 1.00 rounding 1.00, but it is ignoring this format when it is calculating the sum.

 

[synapsevampire]

Yep, it'll do that.

Instead of basing the sum on that field, try using:

val(totext({table,field},2))

as the field to sum on.

This depends on how you'd like to handle rounding of course.

-k