Crystal Report Summary Field Problem

I've created a formula field for a detail line on a Crystal 5.0 report.  I'd like to summarize this formula field to display a total at both a group break and again at the end of the report.  If I were summarizing a regular database field, it wouldn't be a problem, but Crystal is not allowing me to create a Sum on this formula field.  Here is what the data looks like, followed by what I want it to look like on the report:

Bob 555-1111  5/1/1999   1,000
Bob 555-2222  5/1/1999   1,000
Bob 555-3333  5/1/1999   1,000
Jim 222-1111  5/1/1999   1,000
Jim 222-2222  5/1/1999   1,000

Bob 555-1111  5/2/1999   1,500
Bob 555-2222  5/2/1999   1,500
Bob 555-3333  5/2/1999   1,500
Tom 222-1111  5/2/1999   500
Tom 222-2222  5/2/1999   500

Bob 555-1111  5/1/1999   1,000
      555-2222            
      555-3333  

Jim 222-1111  5/1/1999   1,000
      222-2222
                     5/1/1999   2,000

Bob 555-1111  5/2/1999   1,500
      555-2222
      555-3333

Tom 222-1111  5/2/1999   500
      222-2222
                     5/2/1999   2,000

                       Total      4,000

The amount is only valid for one person on a given day, but it is duplicated on additional detail records, as I want to print each of the different phone numbers for the person.  So, I've created a formula field that checks to see if the previous record's key (person and date) is the same as the current record, if so, then the result of the formula is zero, else it is the database column value (I had to put in a special check for the first record, and to make any null database values 0).  The formula field works great, and I can verify that every detail line has a valid number in it (either some amount or zero - which I'll eventually surpress).  Now, life would be great, if I could just sum this formula field at the ProdDate Group Footer and Report Group Footer, but Crystal complains that I cannot summarize this field (even though it knows that it is a numeric).

Here is the formula field formula, in case this helps:
if IsNull({SPC_RPT_LIST;1.FEE}) then 0
else if RecordNumber = 1 then {SPC_RPT_LIST;1.FEE}
else if Previous ({SPC_RPT_LIST;1.EMPLOYEECODE}) = {SPC_RPT_LIST;1.EMPLOYEECODE} AND
Previous ({SPC_RPT_LIST;1.PRODDATE}) = {SPC_RPT_LIST;1.PRODDATE}THEN 0
ELSE {SPC_RPT_LIST;1.FEE}

Please tell me how I can summarize this fomula field at the Group Footer for the ProdDate and at the Report Footer.  Be aware that Sum({@EmpFee}) does not work.

Good Luck Crystal Hacks!
LVL 18
mdouganAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
peterdcConnect With a Mentor Commented:
Resubmited answer:
Have a look at the Seagate knowledge base for Crystal Reports, and in particular the articles
      entitled:
      * Running Total Formulas in the Report Designer Component
      * Creating a Running Total Subtotalling by Group (not page)

Thank-you, and glad to have helped. Totalling formula fields has caused me a lot of problems as well, and it took a while to track these reports down, since the Seagate search engine always seems to return too much. Still...now we know!
0
 
mdouganAuthor Commented:
Edited text of question.
0
 
mdouganAuthor Commented:
Edited text of question.
0
 
peterdcCommented:
Have a look at the Seagate knowledge base for Crystal Reports, and in particular the articles entitled:
* Running Total Formulas in the Report Designer Component
* Creating a Running Total Subtotalling by Group (not page)


0
 
mdouganAuthor Commented:
Hey!  The info in these articles worked perfectly.  Please resubmit your comment as an answer for credit.  For the benefit of anyone else who might have this problem, here is the solution:

In the Group header I have the following formula to zero out a variable called SubTotal at group break time(in all cases below, the line with the @ is just the name of the formula, it is not included in the formula itself)  You need to include the formula in the Group Header, but you should surpress it, so that it is not visible:
@ZeroFeeTotals
WhilePrintingRecords;
NumberVar SubTotal;
SubTotal := 0

On the detail line I have my main formula that keeps the Fee value from being repeated for detail records that are really pointing to the same person/day, just different phone numbers (it is not surpressed).  Note, this is the only formula here that is actually evaluated WhileReadingRecords - the default.  The others are indicated to evaluate WhilePrintingRecords.  This is what makes the whole thing possible:

@CalcFee
if IsNull({SPC_RPT_LIST;1.FEE}) then 0
else if RecordNumber = 1 then {SPC_RPT_LIST;1.FEE}
else if Previous ({SPC_RPT_LIST;1.EMPLOYEECODE}) = {SPC_RPT_LIST;1.EMPLOYEECODE} AND
Previous ({SPC_RPT_LIST;1.PRODDATE}) = {SPC_RPT_LIST;1.PRODDATE}THEN 0
ELSE {SPC_RPT_LIST;1.FEE}

This formula is also on the detail line, surpressed.  It keeps a running total for the group, and for the report:
@AddFeeTotal
WhilePrintingRecords;
NumberVar SubTotal;
NumberVar GrandTotal;
SubTotal := SubTotal + {@CalcFee};
GrandTotal := GrandTotal + {@CalcFee}

In the Group Footer, the following formula displays the subtotal (it is not surpressed):
@DisplayFeeTotal
WhilePrintingRecords;
NumberVar SubTotal;
SubTotal

In the Report Footer, the following formula displays the Grand Total (not surpressed):
@DisplayGrandTotal
WhilePrintingRecords;
NumberVar GrandTotal;
GrandTotal

Thanks!
0
All Courses

From novice to tech pro — start learning today.