Group Footer SUM only includes last record in group 1

[melburstein]

I ORIGINALLY POSTED THIS IN FORUM 2, BUT I THINK THIS FORUM MIGHT BE MORE APPROPRIATE.


What I will describe below is not my actual report, but an attempt to simplify it.

ACTIVITY Table COMMIT Table
-------------- ------------
Activity (number) Activity (number)
Description (text) Amount (number)

There is a one to many relationship and the tables are joined with a left outer join (by Activity number).

My Report looks like this:
--------------------------
PH Activity Description Amount
GH1 [Supressed]
D 110051 Project A $1,000
D 110051 Project A $6,000
GF1 110051 Project A $6,000

While not shown, the difference in the detail records is that one is for one month and the other for another month.

As you can see the Group Footing total should be $7,000. Once I get the total working, my intent is to supress the detail lines from printing. But I can not get the total to sum properly, even though it is the proper SUM.

I would greatly appreciate help. I am relatively new to Crystal Reports, so tell me in detail what I must do.

Thank You.

 

[rangersmom]

To make sure I don't make any assumptions...I assume you're either inserting a summary field on the Amount Database Field or creating a formula that is:
SUM({tbl.fld),{Group})

I created an ACCESS database, using the tables (as your simplified example illustrates), then created a report using a left outer join for the link between the ACTIVITY fields. Grouped on the ACTIVITY field, inserted the fields, as per your example & inserted a summary field on the AMOUNT field.

Since I got the correct values, I suspect there is a complexity to the report that I'm not getting.

If you'd like to e-mail a copy of your report (with data saved, of course AND a SMALL set of data, at that!) along with a screenshot of the LINKS, I'll be able to analyze it a bit better.

I'd like to help you, but I'll need more clarification.


Annie Mayes
Crystal Reports Trainer

http://www.crystaluser.com

 

[rangersmom]

That e-mail address is:

annie@crystaluser.com

Annie Mayes
Crystal Reports Trainer

http://www.crystaluser.com

 

[melburstein]

Thank you "rangersmom" for taking the time to help me find a solution.

After all day yesterday, not being able to find the problem, I awoke this morning to find your message and an idea that popped into my head as to what the problem might be. While I was sure that I was using a Summary field on the GF1 line -- guess what? -- I was not. Now the report works.

Mel

 

[melburstein]

I thought my problem with the totals was solved, but it is only partially solved. If anyone is still willing to offer me guidance, I will sure appreciate it.

As you will read earlier in this thread, I described two tables linked with a Left Outer Join. The one on the left is ACTIVITIES and the one on the right is COMMIT. In the case of a typical Activity Number (the key to each table), the report finds one record in the ACTIVITIES table and two matching ones in the COMMIT table. I extract a Commit Dollar Amount and it appears on the detail line of my report. But, as I said, there are two records in the COMMIT table, with different dates, so I end up with two detail lines with different amounts. The two detail lines are followed by a Group Footer which now correctly calculates the SUM of the two amounts. My intent is to suppress the detail lines so the user will only see the Group Footer with the total. The above works fine.

Here is my problem. Each detail line on the report includes more than just the Activity number and the Commit Dollar Amount, each such line also includes a Paid Amount which is extracted from the ACTIVITIES table. But since the COMMIT table has two records, I end up with two detail lines and each includes the "Paid Amount". There is only one record in the ACTIVITIES table, but the Paid Amount appears on both detail lines. The number on the Group Footer line is twice what it should be. So while my total for Commit dollars is now correct, the SUM for Paid dollars is doubled.

How can I prevent the distortion of these totals?

Mel

 

[lbass]

You need to use a running total for the second total. Go to insert->running total, and select {Activities.PdAmt}, sum, evaluate on change of field (if Activities has an ID field, use that), reset on change of group. This way the Paid
Amount will only be evaluated when the associated ID field changes, indicating a new record (not a duplicated one).

-LB