Please post an example of what you want and what you are actually getting when you try to do this. You can probably get your point across with a post that looks like this:
Sub Summary sorted by------
//list sub summary fields
//list body fields
Trailing Grand Summary-------
//list grand summary fields
You may also need to document exactly how your tables are related.
Okay, I am working from my Projects Table and my Savings Table. They are related by the key Project_ID. There can be multiple Savings Amounts per Project_ID. The report that I have created Shows Records From The Projects Table and is as follows:
Sub Summary By Project_Type (Leading)
Sub Summary By Project_Objective (Leading)
Project_ID, Project_City, Project_State, Square_Feet, ::Savings_Amount
Sub Summary By Project_Objective (Trailing)
Total <<Project_Objective>> Projects, Square_Feet_Summary, ::Savings_Amount_Summary
Sub Summary By Project_Type (Trailing)
Total <<Project_Type>> Projects, Square_Feet_Summary, ::Savings_Amount_Summary
Trailing Grand Summary
Total Completed Projects, Square_Feet_Summary, ::Savings_Amount_Summary
The report is Sorted By Project_Type, then Project_Objective, then by Project_State, and then by Project_City.
The Square_Feet and Square_Feet_Summary fields add up perfectly. The problem is with the ::Savings_Amount and ::Savings_Amount_Summary fields. The ::Savings_Amount field isn't adding up the total savings for each Project_ID record and the ::Savings_Amount_Summary fields aren't adding up the Sub Summaries or the Trailing Grand Summary properly. It appears that the ::Savings_Amount_Summary field is only adding the last record from each grouping.
UPDATE: I think I just solved part of my problem. I changed the field ::Savings_Amount to ::Savings_Amount_Summary in the Body and that did help some of the groupings add up the savings correctly, however, some are still not adding up correctly.
Thank you for any and all suggestions.
You might try using a calculation field in Projects that uses the Sum function instead of referrring to the summary field.
Could be used in place of the reference to the Savings::Savings_Amount_Summary
If this calculation gives you identical results, I'd double check my relationship definition between the two tables.
PhilModJunk, Wow, using the Sum function worked! Thank you! I created the Sum(Saving::Savings_Amount) field in the Projects Table and then I created a Summary Field of that field and then put them into my layout and it worked! I have one follow up question. I'm trying to understand why this worked. Can you give me some insight on this? Also, how does FMPro know to sum up "multiple" savings occurrences for each Project_ID when using the calculation Sum(Savings::Savings_Amount)?
Thanks again for all you help!
Sum(relatedTableOccurrence::field) and other aggregate functions such as Max, and average use the relationship linking the table where you define this calculation to the Related Table Occurrence you specify inside the parenthesis. In a sense, Sum says find all records in the related table that match this record and add up the values found in "field".
PhilModJunk, you are the FMPro Master! Thanks Again!