4 Replies Latest reply on Mar 29, 2013 11:29 AM by BryanN

    Trailing Grand Summary not calculating

    BryanN

      Title

      Trailing Grand Summary not calculating

      Post

           My report is based on the Jobs table, and each job can have multiple related records in the JobLineItems table (these are entered via a portal).  I created a summary field that summarizes all of the line items in each job (a line item has a qty X $price).  The summary field seems to be caculating fine on a per job basis from what I can see, as it's correctly calculating the sum of all the line items for each job.

            

           However, if I switch to the report layout, based on the same Jobs table, the trailing grand summary shows values from the last record in my find, not a grand total.  The grand total field is the same summary field used to total each job, and it's in a part defined as a trailing grand summary.  Help!

            

            

      Screen_Shot_2013-03-29_at_1.34.01_PM.png

        • 1. Re: Trailing Grand Summary not calculating
          philmodjunk

               Your summary field is evaluating as it is designed to do so, just not as you want it to.

               If you set up this report in a layout based on the line items table, your grand total would compute correctly. Summary fields from related tables compute a value based on the set of related records, not your current layout's found set. Put a field, any field--not just summary fields from a related table into a trailing grand summary or a footer and you will see data based on the values in the last record in your found set.

               You can also define a calculation field in Jobs that computes the total of each set of related line items. Then a summary field defined in jobs will compute your grand total. Such a calculation can use the sum function or it can consist solely of the name of the related table's summary field.

          • 2. Re: Trailing Grand Summary not calculating
            BryanN

                 Yeah I got ya.  I was hoping to summarize by each job, versus showing all the line items for each job... however, doing it that way does give me more granularity in reporting (we sometimes ship parts of jobs and bill out, so we could bill out that item on it's own).  I'm gonna go with it for now and see how far I can take it. Thanks!

            • 3. Re: Trailing Grand Summary not calculating
              philmodjunk

                   all the methods that I described permit you to "summarize by each job". If you base your report on the lineitems table, you can use a sub summary layout part with the summary field placed in it to show the sub total for that job. This requires sorting your line item records to group them by job. And you can choose to only list the subtotals without the individual line items being listed if you remove the body layout part from such a layout.

              • 4. Re: Trailing Grand Summary not calculating
                BryanN

                     PERFECT.  That's what I was looking for.  So all along I should have been using a sub field instead of the body.  Thanks!