Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Crystal Report Summary Field Problem

Posted on 1999-07-19
5
Medium Priority
?
219 Views
Last Modified: 2013-12-25
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!
0
Comment
Question by:mdougan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 18

Author Comment

by:mdougan
ID: 1495466
Edited text of question.
0
 
LVL 18

Author Comment

by:mdougan
ID: 1495467
Edited text of question.
0
 
LVL 1

Expert Comment

by:peterdc
ID: 1495468
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
 
LVL 18

Author Comment

by:mdougan
ID: 1495469
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
 
LVL 1

Accepted Solution

by:
peterdc earned 800 total points
ID: 1495470
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Suggested Courses

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question