Conditional Sum in group header

[alie123]

Hi All

I'm wondering if anyone knows how I can do a conditional 'sumif' type of formula in a group header. I have a set of data that contains nominal numbers from an accounting package and each nominal number has a debit and credit amount. The records are grouped together by a transaction action number, what I require is a 'sumif' type of formula that sums all the debit amounts if the nominal number equals a certain number. So data for each transaction might look like this:

Nominal No Debit Credit
92400 15.00 0.00
95000 25.00 0.00
92100 15.00 0.00
95000 15.00 0.00
96000 0.00 70.00

I want a formula to sum all the debit amounts if the nominal code equals 95000, so this should give me 40.00 in total. This needs to be in the group header.

I have tried a formula along the line of

if {Nominal Code} = "95000" then sum{{Debit_Field},{Transaction Number})

However, this does not work correctly. Only if the first record is 95000 does it work, and when it does it sums all of the debit amounts in the transaction, rather than just the 95000 records. If possible I would like to avoid entering an 'if' formula in the details section that looks "95000" and then shows the debit amount, as the report needs to be dumped into excel and this 'if' formula might confuse people.

Any help appreciated. I have also tried using running total formula, but this doesn't seem to give me the total of the transaction when a transaction carry overs to the next page.

 

[lupins46]

I think you would need to use a subreport to get this.
In your subreport you use the same grouping as your main report but you only select nominal 95000 and simply create a group total. Then put the subreport in the group header of your main report.

 

[alie123]

Unfornuately this is a subreport, and a subreport can not have a subreport - which is really annoying about crystal reports.

 

[GJParker]

Insert a formula field i.e.

if {MyTable.Nominal Code} = "95000" then {MyTable.Debit_Field} else 0

You can then insert a summary on this formula.

HTH


Gary Parker
MIS Data Analyst
Manchester, England

 

[alie123]

Thanks Gary, I assume that the formula needs to be placed in the datailed section. Is there a way of doing it without placing a formula in the detailed section?

 

[GJParker]

The formula doesn't need to be place on the report all. Once the summary has been created in the header section the formula field can be removed from teh report.

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[alie123]

Thanks Gary, your correct. It does work.

One other problem I'm having is that where I have grouped the transaction. When you drill down the 'details' section has vansished. The group header and footer are okay, just no detailed section?

Anyone know what might have casued this? The group above is also grouped by transaction number