Sum group level field in Crystal Reports

crystal-reportsgroupingvisual studio 2010

I'm trying to create what I feel like should be a very simple report. I have a result DataTable similar to this:

ID    Amount    ChildID     Name    Discount
1     200       1           Billy   $10
1     200       2           Bobby   $20
2     100       1           Kenny   $10

I need to subtract the sum of the discount from the amount at the group row level. Then at the end I need to summarize those amounts. So it will look like this:

ID: 1 Amount: $170
    Billy      $10
    Bobby      $20
ID: 2, Amount:$90
    Kenny      $10

Total:        $260

I have created a formula for subtracting the sum of the discounts from the group's amount. Now how can I sum these values in the report footer?

Best Answer

You'll need to keep track of the running subtotal via a global variable. A group footer formula like this should do it:

whileprintingrecords;
numbervar subtotal;

//Update subtotal with the group's amount minus the total discounts for that group
subtotal := subtotal + ({table.amount} - sum({table.discount},{table.ID}))

In the report footer, you're now free to use the variable's total.

Related Topic