4 Replies Latest reply on Nov 2, 2012 7:33 AM by comment

    Sum Issue Problem

    fmmotorola

      Hello,

       

      I'm pretty sure this is a easy issue, but I'm banging my head trying to figure this out. Any help would be greatly appreciated. Here's my scenario:

       

      I have a large application that that has a summary page in a list format with financial information. On this page, there are group names and the summed finances for that grouping (ie.Capital and Expense). Creating this part was easy. That was up until they wanted a sum of the already summed items in the footer section.

       

      An example of this would be show all the financials for this year. The user would perform a search for the year and all the years groupings would appear in the list which also shows the summary of each of those groupings on the same line. I now want a grand summary in the footer section that would show the totals for all.

       

      I created and attached a very rough example of what I am trying to achieve. (Note: I am using FM12)

       

      I'm hoping this is a simple solution, but I am just not seeing it for this.

       

      Thanks in advance !!

       

      Glenn

        • 1. Re: Sum Issue Problem
          Mike_Mitchell

          Hello, Glenn.

           

          There are at least 3 ways to accomplish what you're after: The easy way that forces you to change some of what you have, a slow-performing way that preserves what you have, and a faster-performing way that preserves what you have, but requires you to learn some SQL.   

           

          The easy way would be to base your report on your Financials table. Use subsummary parts, sorted by the key to the Main table, without Body parts, to produce the totals by group. At the bottom, simply put your summary field, and you're done. The only hard part is changing your report so it's based on the Financials table instead of the Main table.

           

          The slow-performing way to do it from the Main table involves the use of calculation fields and the Sum function. In this case, you put a calculation in your Main table that reads, "Sum ( financials::amount )" and a Summary field of that. This will give you your totals in the Main table. Problem is, as your data load grows, it's going to be deadly slow.

           

          The third involves using an ExecuteSQL function to pull your data. You can use the SQL SUM function to pull your totals and display them from pretty much anyplace you want. It'll be substantially faster than option 2 (which I don't really recommend), but you'll need to learn a little SQL to make it work.

           

          Let us know which one sounds good, and I'll go into more detail if you need it.

           

          HTH

           

          Mike

          • 2. Re: Sum Issue Problem
            fmmotorola

            Good Morning Mike,

             

            Thank you VERY much for your response.  I actually created Option 1, but my users did not like that particuliar view and insisted on a view similiar to my attachement. 

             

            For Option 2, I tried that, but the Summary field of the Sum, still only shows the sum of what ever related record I'm on, and not the sum of all sums.  This is the solution I have been working on, but to no avail.  I think at least in this stage, this solution would work temporarily.   Data capacity would not be excessive for a year or so.

             

            Option 3, this option seems the best.  Using this method, would it show the summary of all the groupings in the list dynamically ??  I would like to solve this issue the correct way for the long term, and it sounds like this is the best way

             

            Glenn

            • 3. Re: Sum Issue Problem
              Mike_Mitchell

              Glenn -

               

              What exactly was the problem with Option 1 (subsummary parts)? I've attached an example. (See the "Report Copy" layout.)

               

              In order to use ExecuteSQL, what you'd need to do is write your queries for each Main record to sum up the amounts, then another query for your overall sum. Here's an example for your individual records:

               

                   ExecuteSQL ( "SELECT SUM ( financials.amount ) FROM financials WHERE financials.\"_kf Main ID\" = ?" ; " " ; " " ; _kf Main ID )

               

              Make that a field in your Main table.

               

              Then, for your overall total, you can use:

               

                   ExecuteSQL ( "SELECT SUM ( financials.amount ) FROM financials ; " " ; " " )

               

              You can either make that a global calculation in the Main table, or use a script to insert it into a merge variable when you go to that layout.

               

              HTH

               

              Mike

              • 4. Re: Sum Issue Problem
                comment

                fmmotorola wrote:

                 

                I would like to solve this issue the correct way for the long term

                 

                That would be option #1 above.