Adding up values from Appends

[mpschein]

My report prints 1 or more detail lines with summary info above, which I am grabbing using the Append template. There is a numeric field in the summary info that I want to sum for the entire report, and it's a field that is NOT included in the detail below, so I can't ignore it and simply sum the info from the detail lines.

So the problem is that the value from the summary appears with each detail line, and when I sum up that field, my sum is way too high because I only want to include one value per summary level, not each detail line. Easy fix?

[Steve Caiels]

Hmm. I've not tried this and I don't have a report handy to try it on, so give me a "virtual slap" if it doesn't work.

Try building the summary and double click on the summed field. Then change the calculation to average. I think this should do it.

If not,add the count as a summary field, then at least you can take it into Excel and divide the sum by the count.

Regards
Steve
Datawatch Europe Ltd

[mpschein]

Thanks for the suggestion. Actually, I had already done that, but the grand total was giving me an average instead of the actual grand total, so I dropped that idea. It's dollars and therefore critical to have the total dollars for the field.

I was getting set to export to Excel, and if no one else has an idea, that's where I'll head. Thanks again.

[Mike Urbonas]

Hang on...give this a try:

Set up your summary as follows:

* Add the Key Field you want
* Include the append value in question as a SUM summary field
* Include "count" as a SUM field (this will count the number of times your Key Field appears in your Table View)
* Include the append value in question again as a summary field. Double click on this and change the Field Calculation to a Ratio:
-- Click on the "Ratio" radio button
-- Click on the drop-down menu and select "count"
-- Click OK all the way out

You should now have a Summary with:

(Col. 1) The Key Field you want,
(Col. 2) The append value you want (but repeated/summed several times which we don't want),
(Col. 3) the count column (count of # times the Key Field appears in Table)

and finally...

(Col. 4) "Ratio" column dividing col. 2 by col. 3...SHOULD be the figures you are looking for!

Please reply if this works...I think I solved it
:cool:

Regards
Mike

[mpschein]

Worked exactly as you laid it out; pure genius, yet it looks so simple. Thank you so much (except now I have to get back into that project!).

[Mike Urbonas]

Originally posted by mpschein:
Worked exactly as you laid it out; pure genius, yet it looks so simple. Thank you so much (except now I have to get back into that project!).:D