Conditional Count and Sum Formulas

[cbauer357]

I have a problem with some conditional formulas. I am using Crystal 8 and SQL Stored Procedures. The SQL Stored Procedure brings back all of the data needed. I have three formulas as follows:

If {spRptSubmittalsActivity;1.STATUS_ID} = 2 then
Count({spRptSubmittalsActivity;1.NEED_ID}, {spRptSubmittalsActivity;1.AcctName})

Sum({spRptSubmittalsActivity;1.NoPeopleC}, {spRptSubmittalsActivity;1.AcctName})

if {spRptSubmittalsActivity;1.STATUS_ID} = 2 then
Sum ({spRptSubmittalsActivity;1.NUMBER_OF_PEOPLE},{spRptSubmittalsActivity;1.AcctName} )

These formulas work fine. However, when I create three new fields using these formulas and only change the condition to be {spRptSubmittalsActivity;1.STATUS_ID} = 1, nothing shows in the additional formulas. The original formulas still work. I tried removing the fields from the report where STATUS_ID = 2 and keeping in the new fields, but it still won't calculate.

I tried using the Running Total expert, manual running totals, but still nothing. Does anyone have an idea.

 

[kenhamady]

Count({spRptSubmittalsActivity;1.NEED_ID}, {spRptSubmittalsActivity;1.AcctName})

This is a simple count, with no conditions, of the records in the current group that have a NEED_ID. Your formula doesn't tell it to only count those with a 2, it says that if the current record is a 2, print this groups regular total.

instead create the following formula:

If {spRptSubmittalsActivity;1.STATUS_ID} = 2 then 1
else 0

This creates a new column of 1 and 0 values.
Then create a subtotal or grand total (sum) of this formula to find out how many ones there are. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[cbauer357]

Ken,
Thanks for your reply. I tried your idea for the first formula, but it did not work. The other formulas in the additional fields are summing so, it would not work there.

Any other suggestions on how to troubleshoot this?

I know the SQL is correct and is pulling the correct information needed, but the Crystal side just won't work.

Thanks
Christine

 

[kenhamady]

What happens when you create the following formula and place it on the detail band?

If {spRptSubmittalsActivity;1.STATUS_ID} = 2
then 1
else 0


Can you create a Grand Total (Sum) of this formula? Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[cbauer357]

Hi Ken,
Thanks for the reply. I actually found the problem, solved it and it created another problem. The problem was nested in a little deep into the report. All is solved and working fine now.

Thanks again!