Sum group level field in Crystal Reports

Question

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?

Crystal Reports doesn't have an easy way (or a way at all, AFAIK) to look ahead and calculate the discount total per ID, and then subtract that from your amount. Off the top of my head, you'd either want to either (1) calculate the Amount ($170 and $90) for each grouping and then have a subreport to show the children, or (2) have your SQL query figure out the amount in advance, and simply display that instead of the base amount. Hope that makes sense. — LittleBobbyTables - Au Revoir
Thanks. It would be horribly inefficient to have to run a sub report for each group. So if I precalculate the 170 and 90, how do I sum those at the footer. I tried summing just the 200 and 100 but got a total of 500 cause it summed every row. — xr280xr

Ryan Ryan · Accepted Answer · 2013-02-01T22:12:10

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.

Sum group level field in Crystal Reports

1 Answers