sum on records with null values

rbuser

Hi

I noticed a problem on our reports when i do a sum a field where some
records have null values.

Suppose i want to print detail records from a table and do a sum from a
numeric field with a dbcalc, and some of these records have null in the
numeric field the sum is correct, in fact the sum is the sum until the first
null record instead of the sum of all records.

Any ideas how to prevent this?
Thanks in advance

Best regards,
Franky

nicocizik

Hi Franky,

Which version of ReportBuilder are you using? In my testing with RB 7.03
with Delphi 7, this seemed to work correctly. Try creating a minimal
example and see if the behavior you described continues. If so, please send
a copy of the example in .zip format to support@digital-metaphors.com and
I'll take a look at it.

--

rbuser

Thanks Nico

Yes we use Rb 7.03 also and i do have the problem.
But making a small application with just a query and a reportlink sounds
like a good idea.
If i have the problem there too i can always use that to demonstrate the
problem and mail it to you..

I'll keep you posted on it here as well.

Franky

rbuser

Ok, i'm sorry but i see now that my error description wasn't correct.
The problem is not with null values but with a master-detail relation.
There are groups on the report on master fields and in the footer or summary
of the report i do a dbcalc of a detail field.

The sum is correct as long as there is no master record on the report that
doesn't have detail records.
When there is a master with no corresponding detail records the sum fails,
in fact the sum is the sum that was reached until that record.

Any ideas?

Franky

nicocizik

Hi Franky,

I am a bit unclear about how your report is set up. What fields exactly are
you summing and how is the sum wrong? By default ReportBuilder will skip
all master records that do not have any detail records attached to it. If
you would like to turn this off, you need the set the
DataPipeline.SkipWhenNoRecords property to False.

--

rbuser

Hi Nico,

The sum is in the summary on the report and sums a field from the detail
table which is in the detail band.
Example:

Nr 21236 Group header
Product1 200 Detail section
Product2 100
Nr 21237
Product1 350
Nr 21238
Nr 21239
Product1 150
Product2 120
-----------------
Sum Summary or report footer

The sum should be 920 but is 650, the sum only sums the values until it
reaches a master record without detail records, in the example master record
21238 has no detail records.

I hope my problem is more clear now

Thanks for your help

Franky

rbuser

Correction, tried it again now.
The sum value is the sum from the Master record withhout details, so the sum
would be 270 in the example.

Sorry for the confusion

Franky

nicocizik

Hi Franky,

From the way your report is working, it looks like you are summing over a
value that is traversed inside a subreport. Unfortunately the DBCalc
component can get lost when doing so. As an alternative, you should use two
TppVariables, one in the Detail band of the subreport (with it's visibility
set to False) and one in the summary band. Then in the first variable's
OnCalc event, update the second variable's value with the existing sum and
the current field added in. Below is an example of how to do this.

http://www.digital-metaphors.com/tips/TotalsFromSubReport.zip

--