How to create a total formula in Crystal report version 10?

[boboleft]

Hello everyone,

I am trying to create a report, which will provide list of active accounts with information such as
Account#, total of principal payments made for the last 3 months if there were any. I am having difficulty on creating the formula for the principal payments made for the last 3 month. Criteria for calculating principal payments made are :
“payment code=[“prn”, “prn1”, “prn3”] and (system date-trans date <=90). If the criteria were met then “the total principal payments made” will go to “Group by Acct#:” section, otherwise, a “zero” should be placed there. Below is how I had my report designed. Any help will be greatly appreciated. Thanks.

Group by Acct#:

Acct#, sum of total principal payments made for last 3 months, address, city, state, zip

Detail Section:

Acct#, Payment code, payment amount, trans date

123 INT
125 PRN
126 PRN1
128 INSUR

 

[lbass]

Create a formula:

if {table.payment code} in ["prn", "prn1", "prn3"] and
{table.trans date} > currentdate-90 then
{table.amt}

Place this in the detail section and right click and insert a summary on it at the group level. Drag the result into the group header.

-LB

 

[boboleft]

Thanks for the response. One more question, what if the record doesn't meet the criteria. how can i add else statement to show zero at the group header??

 

[AdamField]

Hey Boboleft

I presume that would be somthing like

if {table.payment code} in ["prn", "prn1", "prn3"] and{table.trans date} > currentdate-90
then{table.amt}
else 0

Greets

Adam

 

[lbass]

If all records in the group are 0 then 0 will appear as the sum in the group header. The default for the formula is 0--it is not necessary to specify it.

If you are not seeing a zero when you think you should, then one of your fields might be null.

-LB

 

[Madawc]

As lbass says, you probably have nulls, which stop a formula with blank output unless you first test for them. So try

if not isnull ({table.trans date})
and not isnull ({table.payment code})
and not isnull ({table.amt})
and{table.payment code} in ["prn", "prn1", "prn3"] 
and {table.trans date} > currentdate-90 
then {table.amt}

Nulls are most likley in a date, but can occur anywhere.

You can also set Crystal to covert nulls to zeros, spaces or low date values.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10