sum it and group by with a wild card value

[fredong]

I know there are some experts out there can help me this. I am on Crystal 8.0. I am trying to create a formula that will allow me to get sum of a field (PostAmt)that contains all debit amount and group by where the field(PostCmnt)with first letter start with P.

I tried this and it did not work

if {Tek_sp_rpt_GenerateTranReport;1.PostAmtHC} >= 0 and {Tek_sp_rpt_GenerateTranReport;1.PostCmnt} like 'P?' then {Tek_sp_rpt_GenerateTranReport;1.PostAmtHC}


My data below look like this

PostAmt PostCmnt
------- --------
439.83 Invoice: 0000075855-IN: Cust: LEC026
828.85 Invoice: 0000075856-IN: Cust: LEX035
72.8 Invoice: 0000075857-IN: Cust: MID083
1174.86 Invoice: 0000075858-IN: Cust: DIG069
2399.66 Invoice: 0000075859-IN: Cust: IMA041
-2275 Invoice: 0000076079-CM: Cust: IMA001
-198.78 Invoice: 0000075572-CM: Cust: THE024
-18797. Payment: 82822-CR: Cust: COL098
-6825.4 Payment: 82863-CR: Cust: COL098
-0.18 PmtAppl: 681086-CR: Cust: PIT052
298.55 PmtAppl: 174355-CR: Cust: PRE075


Thus, The PostAmt can be a debit or credit in a Payment and PaymentAppl. Thanks.

 

[synapsevampire]

Try:

if {Tek_sp_rpt_GenerateTranReport;1.PostAmtHC} >= 0 and {Tek_sp_rpt_GenerateTranReport;1.PostCmnt} like 'P*' then {Tek_sp_rpt_GenerateTranReport;1.PostAmtHC}
else
0

Now you should be able to use this formula to provide aggregate functions.

-k

 

[fredong]

Thanks, it works.