SubTotal on one field within a Group 1

[beemer1]

I have a Group titled Loan Status. Within Loan Status there are 9 different status's. Whatever is selected for that particular loan is what pulls into the report. Example. My report is grouped by Loan Status. Loan Status's include Loan Originated, Post Closing, Cancelled/Denied. I have a Grand Total loan amount for all of these but I also need a separate Grand Total loan amount for the Cancelled/Denied. I am lost..... Any suggestions? I created this formula:
if {LOAN_STATUS_CD.loan_status_dsc} = "cancelled/Denied" then sum ({LOAN_APPLICATION.loan_amt}). I inserted this formula into the report but is not working. It is still giving me a Grand Total of all the loan status's.

 

[rosemaryl]

Dear Beemer1,

Create separate formulas for each status you need to evaluate and place in the detail section (you can suppress if you do not want it to show). The click on the formula in the detail section and choose sum.

The formula would be:

if isnull{LOAN_STATUS_CD.loan_status_dsc})
then 0
else
if {LOAN_STATUS_CD.loan_status_dsc}= "cancelled/Denied"
then {LOAN_APPLICATION.loan_amt}
else 0

Regards,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[beemer1]

It says the keyword then is missing. It wants me to add then after isnull. but when I do it asks for something else. Crystal 8.5 sorry

 

[Barkley564]

I believe you just need to add a ( after isnull as in this example:
if isnull({LOAN_STATUS_CD.loan_status_dsc})
then 0
else
if {LOAN_STATUS_CD.loan_status_dsc}= "cancelled/Denied"
then {LOAN_APPLICATION.loan_amt}
else 0

 

[rosemaryl]

Good Catch Barkley564,

I missed the opening paren when copying.

Thanks,
ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[beemer1]

I added the ( and it is now only pulling 0 (zero) it is not giving me a separate total for the loan status of cancelled/denied. I am using crystal reports VS 8.5. I inserted the formula in the details section, right clicked selected insert/subtotal and I have tried insert summary and insert grand total and it still will not pull in the dollar amount. I am exhausting myself. Anyone have any prozac? This seems so simple. Am I dramatizing or what? Please HELP!!!! Thank You

 

[lbass]

Try laying out the fields loan_status_dsc and loan amount in the details section. The report back with some sample results. Specifically, I'm wondering whether the loan amount is present in the same record as the cancelled/denied status. You might also try putting Rosemary's formula in the detail section and report back on the values returned for some sample data.

-LB