Crystal Reports Summing

Question

So I two groups tblTenant.ID tblTransaction.TenantID

In tblTransactions there are two fields I want to manipulate, AmountCharged and AmountPaid. How can I sum both fields and subtract them per group? I.E., each Tenant will have all their amountCharged's summed and then have their AmountPaid's summed and subtracted.

For example, the SQL would be:

SELECT SUM(tblTransactions.AmountCharged) - SUM(tblTransactions.AmountPaid) FROM tblTransactions
Where tblTrascactions.TenantID = x

Where 'x' is the tenant id of the group.

Answer 1

The easiest way I can think of is to create a formula field on your detail line that is AmountCharged-AmountPaid and make it hidden. Then just right click on it and insert a sum on the group.

Answer 2

can be done with one crystal formula...

SUM({tblTransactions.AmountCharged}, {tblTrascactions.TenantID}) - 
SUM({tblTransactions.AmountPaid}, {tblTrascactions.TenantID})

or

SUM({tblTransactions.AmountCharged} - {tblTransactions.AmountPaid}, {tblTrascactions.TenantID})

then display that formula in the tenantID group footer.