6 Replies Latest reply on Nov 23, 2009 11:42 AM by DJD

    Sub-Summaries Issue

    DJD

      Title

      Sub-Summaries Issue

      Post

      I am having trouble getting my sub-sums and my trailing grand sum to add up my columns on a report layout that I am creating.  I have created the layout under my Projects Table, but I am trying to sub-summarize and grand summarize a summary field from my Savings Table.  I believe that I have properly created a relationship between the two tables with my common key Project ID # because my other layouts without sub-sums are working fine.  The Savings Table field (Savings Amount) is showing the correct amount for each record in the body, however, the sub-sums and the grand sum using the Savings Amount Summary field are not adding the amounts correctly.  Any and all help is greatly appreciated.  Thank you.

       

      Using FMPro v.10.03

      Windows XP Pro

      FMPro Skill Level (1 to 10)= 6

       

       

        • 1. Re: Sub-Summaries Issue
          philmodjunk
            

          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

          Body------

          //list body fields

          Trailing Grand Summary-------

          //list grand summary fields

           

          You may also need to document exactly how your tables are related.

          • 2. Re: Sub-Summaries Issue
            DJD
              

            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)

                 <<Project_Type>>

            Sub Summary By Project_Objective (Leading)

                 <<Project_Objective>>

            Body

                 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.

            • 3. Re: Sub-Summaries Issue
              philmodjunk
                

              You might try using a calculation field in Projects that uses the Sum function instead of referrring to the summary field.

               

              Sum(Savings::Savings_Amount)

               

              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.

              • 4. Re: Sub-Summaries Issue
                DJD
                  

                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!

                • 5. Re: Sub-Summaries Issue
                  philmodjunk
                    

                  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".

                  • 6. Re: Sub-Summaries Issue
                    DJD
                       PhilModJunk, you are the FMPro Master!  Thanks Again!