4 Replies Latest reply on Jan 22, 2013 3:13 PM by philmodjunk

    Calculations in Sub Summary Report

    ghfarm

      Title

      Calculations in Sub Summary Report

      Post

           I’m having a problem getting the correct totals for certain parts of a sub summary report.  This is a simple sales summary report for a single sales day based on two main tables (Orders and Invoice Details).  I want to count the number of customers at a specific delivery site and do a trailing grand summary of the invoice totals.  The problem is that I can’t do both at the same time on a single report. 

           I can count the invoices for a particular delivery site when the report is based on the Orders Table, but the trailing grand summary returns only the last value for the invoice totals.

           Similarly, when the report is based on the Invoice Details Table the trialing grand summary calculates correctly but the invoice count for each delivery stop reverts to 1.  I can’t base the count on the Invoice Details table because it will obviously return the number of line items rather than a simple count of the number of invoices.

           I’ve tried all manner of calculated fields in both tables but with no success.  I’ve attached a sample of the report to illustrate the problem.

        • 1. Re: Calculations in Sub Summary Report
          ghfarm

               Here's the report sample

          • 2. Re: Calculations in Sub Summary Report
            philmodjunk

                 Is this your data model?

                 Customers-----<Orders-----<InvoiceDetails

                 Customers::__pkCustomerID = Orders::_fkCustomerID
                 Orders::__pkOrderID = InvoiceDetails::_fkOrderID

                 If so, Are Deliveries being counted via a summary field defined in Orders?

                 And how are you computing the order totals shown as a column of dollar amounts? Is this a summary field in InvoiceDetails or a calculation field in Orders?

                 With your layout based on Orders. a Count of Summary field defined in orders should be able to count your deliveries in both the sub summary and trailing grand summary layout parts.

                 If you define a calcualtion field in Orders that computes the total of the related amounts from InvoiceDetails, A summarry field defined in Orders can then be defined to compute the total of this field and it can be placed in your grand summary part to show the grand total.

            • 3. Re: Calculations in Sub Summary Report
              ghfarm

                   Thanks Phil,

                   Everything you surmised about the design of this report is correct (you're good.)  The one thing I did not try was your last suggestion which solved the problem.  I was using calculation and summary fields based in the InvoiceDetails table for the grand summary.  I simply created calculation fields in the Orders table by bringing over the calculation fields in the details table and then set up the summary fields there.  I'm still getting used to the contextual nature of FM.  Unfortuantely the worst part about getting accurate numbers from a report is realizing how pathetically little money I made this week.  of course, it's 12 dgrees out today and my money actually does grow on trees and other plants.

                   By the way, I gave up on the probelm you tried to help me solve (page breaks in sub summary reports) because I was spending too much time on it - although I did learn a lot about GetNthRecord.  I found a not so elegant solution using calculated fields in the header.  I have seen similar suggestions to yours but no concrete example.  Someone could make a couple of bucks figuring this one out.

                    

                   tony

                    

                    

              • 4. Re: Calculations in Sub Summary Report
                philmodjunk

                     You might find this information on summary field evaluation context useful:

                     A "non Running" Summary field produces an aggregate value (a value from more than one field in one record). The value returned is determined by the context in which it is used/displayed:

                Summary field is referenced on a layout based on the table in which it was defined:

                A group within a FoundSet

                     If you place the summary field in a subsummary part that specifies the "break" field that grouped the records when the found set was sorted, you get a subtotal--the total for that group.

                     In a calculation, you can use the getSummary function to access the same group based sub total.

                All the records in a FoundSet

                     If you put that summary field in a layout part other than the sub summary part, you get the total for all the records in the current found set.

                     If you refer to a summary field in a calculation field defined in the same table as the summary field, it will also return a total for the current found set. (Which is why we have the GetSummary function to get sub totals in calculations.)

                Summary field is referenced on a layout based on a table related to the table in which it was defined:

                Not in a Filtered Portal

                     If you place the summary field on a layout based on a related table or refer to it in a calculation defined in a related table, the relationship controls the value that is computed. It will be based on all the records in the summary field's table that are related to the current record in this table.

                     Think of it this way, if you put a portal on this layout to the summary field's table, you'd see all the records in this portal that are used to compute the summary field's value in this context.

                In a Filtered Portal (FileMaker 11 and newer only)

                     If you place that summary field inside a portal with a filter, you no longer get a value based on all the related records. Instead, you see a value based on all related records for which the filter expression evaluates as True.

                     This is a special case use of a summary field that is often implemented by putting a single row copy of a filtered portal below it with the summary field inside so that the user sees a value based on just the records visible in the larger portal.

                     This is a "Display Only" trick as you cannot refer to the value of this field in a calculation and get the same value shown on the layout--you get the result described in "Not in a Filtered Portal" above.

                     Note that this does not just apply to "total" summary fields, Average, Count, Maximum, standard deviation, etc all follow these same rules.