I need to display the total of a sum based on a field value. I basically need to sum all records where a field = "Credit and all those where a field ="Debit" and then subtract the credit total from the debit total to get the overall total. Can you advise how to do this please. i've tried using groups, running totals and formulas but am getting myself in a mess. Any help would be greatly appreciated.

 

Thanks

Esther

 

Sorry just to add, there may be multiple credit and debit records, will this still work?

 

Thanks

if so then you just need to insert a summary as a SUM for each of the formulas at each of the footer levels you want to see results in.

if {SRS_FDU.FDU_CORD}='C' then {SRS_FDU.FDU_AMNT}

place it on the detail section.

when you preview the report you should see the actual credit amount on every row that is a credit and a zero on all debit rows.

 

to get totals for just credits, click on the sigma sign (the blue E in the toolbar) to insert a summary.

select the "Credits" formual field

calculate as a SUM

Summary location will be group 1 (customer)

this will create a field in the group footer that is the sum of credits for each customer.

If you want a sum for the whole report repeat teh last step put for the summary location select Grand Total (report footer) as the location.

 

Repeat the process for "Debits" using

if {SRS_FDU.FDU_CORD}='D' then {SRS_FDU.FDU_AMNT}

 

finally create the "Together" using

if {SRS_FDU.FDU_CORD}='C' then {SRS_FDU.FDU_AMNT} else {SRS_FDU.FDU_AMNT} *(-1)

when you place this on the detail row you should see the amount on eery row but if it is a credit is is a positive value if it is a debit it is a negative value.