Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Crystal Report Summing-Grouping Prob Post Reply Post New Topic
Author Message
Krazy Kasper
Newbie
Newbie
Avatar

Joined: 11 Jul 2007
Location: United States
Online Status: Offline
Posts: 23
Quote Krazy Kasper Replybullet Topic: Crystal Report Summing-Grouping Prob
    Posted: 26 Feb 2009 at 1:37pm

Crystal Reports 10.0 Summing-Grouping Problem

Contract # 12345 has four machines (111, 222, 333, and 444)

Contract # 67890 has two machines (999 and 888)

Each machine has a “base amount” (e.g., Machine 111 has a base amount of 20,000)

Each machine has multiple invoices (some have one invoice others have two, three…ten)

Each invoice shows the “base amount” plus the monthly usage

Invoices do not always have a number assigned

I need to sum the usage and total the base amounts for each Contract

Following is an example of the data for two contracts:

CONTRACT

INVOICE

MACHINE

BASE AMOUNT

USAGE

12345

99999

111

20,000

1,000

12345

No Number

111

20,000

500

12345

77777

111

20,000

500

12345

No Number

111

20,000

1,000

 

 

 

 

 

12345

66666

222

30,000

200

12345

55555

222

30,000

200

12345

44444

222

30,000

200

 

 

 

 

 

12345

No Number

333

20,000

400

 

 

 

 

 

12345

No Number

444

100,000

600

12345

No Number

444

100,000

600

12345

33333

444

100,000

1,000

12345

22222

444

100,000

1,000

12345

No Number

444

100,000

600

12345

11111

444

100,000

600

 

 

 

 

 

67890

45454

999

10,000

300

67890

No Number

999

10,000

300

67890

No Number

999

10,000

500

 

 

 

 

 

67890

32323

888

5,000

200

67890

87878

888

5,000

800

 

The summed/totaled amounts should look like:

CONTRACT

 

 

BASE AMOUNT

USAGE

12345

 

 

170,000

8,400

67890

 

 

15,000

2,100

 

I can sum the Usage for each machine (Group 2 on Machine – SUM Usage); and

use the Base Amount (without summing) which brings me closer to the desired results:

CONTRACT

 

 

 

 

12345

 

MACHINE

BASE AMOUNT

USAGE

 

 

111

20,000

3,000

 

 

222

30,000

600

 

 

333

20,000

400

 

 

444

100,000

4,400

67890

 

 

 

 

 

 

999

10,000

1,100

 

 

888

5,000

1,000

 

But when I try to SUM the Base Amount (Group 1 – Contract) I get:

CONTRACT

 

 

BASE AMOUNT

USAGE

12345

 

 

790,000

8,400

67890

 

 

40,000

2,100

 

OR if I try to put the Base Amount field into the report (Group 1 – Contract) I get:

CONTRACT

 

 

BASE AMOUNT

USAGE

12345

 

 

20,000

8,400

67890

 

 

10,000

2,100

 

Is there some way to “sum” the amounts shown in Group #2 (Machine) into Group 1 (Contract)?

Or some other way to achieve the desired results?

(I played around using a Sub-Report for the machine data but that did nothing.)
Krazy Kasper
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 26 Feb 2009 at 2:24pm

I think if your groups are set right you can use a running total and set it to sum on change of group and reset on the next group higher and it will only sum the field once (kind of like a distinct sum).
IP IP Logged
Krazy Kasper
Newbie
Newbie
Avatar

Joined: 11 Jul 2007
Location: United States
Online Status: Offline
Posts: 23
Quote Krazy Kasper Replybullet Posted: 26 Feb 2009 at 2:52pm
Assuming I'm doing it correctly, it doesn't work. (Create a Running Total field in Field Explorer Window - Field to Summarize is Base Amount - Type of Summary is SUM - Evaluate "On Change of Group" - Reset "On Change of Group".  I put the Running Total field in Group 1 - Contract.
 
As you can see from the tables above the Base Amount is repeated in each invoice. A running sum at the contract level sums the Base Amount from each invoice. For Contract 12345 in the above example that totals 790,000 when it should be 170,000.
 
I also tried "Averaging" the Base Amount in Group 2 (Machine) and then doing the Running Total (SUM) on the calculated field but it wouldn't let me select the calculated field.
 
Is there a way to use a "Conditional" Running Total wherein I could use the "Averaged Base Amount" from Group 2 (Machine)? I know you can use a formula such as {FieldName} >1000 but not sure how to include a "calculated" field.
 
I also tried calculating the "Average" Base Amount and then putting it into a formula to see if I could SUM the averaged amounts (and then put that field into Group 1 (Contract) but Crystal Reports won't let me calculate the "Sum of Averages". I believe Crystal does not allow you to combine Summary functions.
Following was what I tried in the formula:
      SUM Average ({Table1.BaseAmount}, {Table1.Machine})
Also tried
      SUM (Average ({Table1.BaseAmount}, {Table1.Machine}))
 
I appreciate any and all ideas to help me solve this problem.


Edited by Krazy Kasper - 27 Feb 2009 at 6:50am
Krazy Kasper
IP IP Logged
BrianL
Newbie
Newbie


Joined: 16 Feb 2009
Location: United States
Online Status: Offline
Posts: 6
Quote BrianL Replybullet Posted: 09 Mar 2009 at 11:13am
Originally posted by Krazy Kasper

Assuming I'm doing it correctly, it doesn't work. (Create a Running Total field in Field Explorer Window - Field to Summarize is Base Amount - Type of Summary is SUM - Evaluate "On Change of Group" - Reset "On Change of Group".  I put the Running Total field in Group 1 - Contract.
If I'm understanding you correctly, I'd triple check that you're evaluating the running total "On Change of Group" for the Machine group.  The numbers you're giving look to me like they're evaluating for each record.  If it's set to evaluate on change of machine group, the only other thing I can think of is to set a global variable to act as a running total.  You can then specify the
 
Global NumberVar RunTotal := 0;
In the Contract group header to reset the variable, then
WhilePrintingRecords;

If RecordNumber = 1 or ({Table1.Machine} <> Previous({Table1.Machine}))


    Then NumberVar RunTotal := {Table1.BaseAmount}


Else RunTotal := RunTotal;


RunTotal
In the detail section and contract group footer.
 
Hope this helps, or at least points you in the right direction.
IP IP Logged
Krazy Kasper
Newbie
Newbie
Avatar

Joined: 11 Jul 2007
Location: United States
Online Status: Offline
Posts: 23
Quote Krazy Kasper Replybullet Posted: 10 Mar 2009 at 4:51am
Thanks for your help. I'll check it again.
If needed, I'll try using the global variable.
 
Krazy Kasper
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.047 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.