Crystal Report - sum field group

totals.rptHi,

I'm trying to sum the field of @Cost per group.   For example, the sum of @Cost for  Heat Barrier Series Glazing Bead should be $320.92.

Thansk\ for your help....
Genius123Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

James0628Commented:
CR won't do a summary on a summary.  Your Cost formula uses Sum (), so CR won't summarize that formula.  Unless you can calculate the cost without using the sum for SUM_PIECE_COUNT, you'll have to use a variable to calculate the total cost manually.

 From your post, it seems that you're trying to get a total for group 2.  Do you also need a total for any other group, or for the entire report?  If so, you'll need a separate variable for each total.

 Where do you want to display the total cost?  The variable will be updated as the records are "printed", so you won't have a total until you get to the last record in the group/report.  IOW, the total will have to be displayed at the end of the group/report.

 With all of that in mind, this will get you a total for group 2:

 Create a formula like the following (call it whatever you like) to initialize the variable:

WhilePrintingRecords;
Global NumberVar total_cost;
total_cost := 0;
""

Open in new window


 The "" at the end is just so the formula doesn't produce any visible output on the report.  You could also put the formula in a suppressed section, or suppress that field on the report.  In my experience, the formula will still be evaluated if it's suppressed.

 Put that formula in a report header section (to declare the variable for the report), and in GH2 (to reset the total for each group 2).

 Change your Cost formula as follows:

WhilePrintingRecords;
Global NumberVar total_cost;
Local NumberVar cost;

cost := {qryCWProfiles.COST} * {qryCWProfiles.PIECE_LENGTH} * Sum ({qryCWProfiles.SUM_PIECE_COUNT}, {@AngleAndLength});
total_cost := total_cost + cost;
cost

Open in new window


 The cost variable is just so the formula doesn't have to calculate the cost twice (once to add it to total_cost, and once to display the cost on the report).

 Create a formula like the following (call it whatever you like) and put it in GF2 to display the total cost for group 2:

WhilePrintingRecords;
Global NumberVar total_cost;
total_cost

Open in new window


 That should give you a total for group 2.  Like I said, if you need totals for other groups, or for the entire report, then you'll need additional variables.

 James
0
mlmccCommented:
You can do this with a summary.

Create a formula
{qryCWProfiles.COST} * {qryCWProfiles.PIECE_LENGTH} * {qryCWProfiles.SUM_PIECE_COUNT}

Open in new window


Now create a summary of that formula over the appropriate group

mlmcc
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
James0628Commented:
mlmcc,

 Unless I'm missing something, that won't work.  You're calculating a value for the detail records and summing the result.  Changing the calculation to use the individual SUM_PIECE_COUNT, instead of the group total, will obviously change the detail values, giving you a different (probably much smaller) total.

 James
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

mlmccCommented:
Remember the distributive formula

A * B + A * C = A * (B+C)

A = {qryCWProfiles.COST} * {qryCWProfiles.PIECE_LENGTH}
B and C are {qryCWProfiles.SUM_PIECE_COUNT}
 B + C = Sum({qryCWProfiles.SUM_PIECE_COUNT})

By resetting on each group it is the group sum

mlmcc
0
James0628Commented:
But he's doing A * (B+C) on every record.  If there are 2 records in the group and B is SUM_PIECE_COUNT from record 1 and C is SUM_PIECE_COUNT from record 2, then the calculation for each record would be (A * record1) + (A * record2).

 To use a simple example:
 If you had 3 records in the group and the COST, PIECE_LENGTH and SUM_PIECE_COUNT in those 3 records were:
1 1 2
1 1 2
1 1 2

 His formula would give him:
1 * 1 * 6 = 6
1 * 1 * 6 = 6
1 * 1 * 6 = 6
 Total: 18

 Your formula would give him:
1 * 1 * 2 = 2
1 * 1 * 2 = 2
1 * 1 * 2 = 2
 Total: 6

 James
0
mlmccCommented:
True so it could become

Count({qryCWProfiles.COST}) * Sum({@NewFormula})

mlmcc
0
James0628Commented:
That works for my example, but only because it has the same values (for both A+B and C) in each record.  If they're not the same, it doesn't work.  For example:

1 1 2
1 1 2
1 2 3

1 * 1 * 7 = 7
1 * 1 * 7 = 7
1 * 2 * 7 = 14
 Total: 28

1 * 1 * 2 = 2
1 * 1 * 2 = 2
1 * 2 * 3 = 6
 Total: 10
 10 * 3 = 30

 James
0
Genius123Author Commented:
Guys, this all started to get really confusing for me.  I finally figured it out by making this formula as mlcc suggested, then summing it and then putting it in group footer 2.  

{qryCWProfiles.COST} * {qryCWProfiles.PIECE_LENGTH} * {qryCWProfiles.SUM_PIECE_COUNT}

I think mlcc would get credit for this. Please let me know if you disagree.  I've attached the file too.

sum.rpt
0
James0628Commented:
Yeah, mlmcc's formula (without the multiplication) does seem to work.  I tried it in the first report that you posted (that included some sample data) and got the same results that I got when I used a variable.  This confused me greatly.  :-)  I finally figured out where I went wrong.  You're calculating the cost (using the @Cost formula) at the group 4 level, but I got it in my head that you were working at the detail level.  Doing the calculation at the group level changes things, and I think mlmcc's simple summary will take care of it.

 Sorry for the confusion.  :-)

 James
0
mlmccCommented:
James - Your arguments made sense and I forgot the values were being calculated at the group footer level.

James did provide a reasonable solution.  You could split the points between the 2 and choose your final comment as the solution.

mlmcc
0
Genius123Author Commented:
Thank you both!  I really appreciate your time.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.