6 Replies Latest reply on Mar 6, 2014 8:41 AM by philmcgeehan

    Summary of a Summary field

    philmcgeehan

      Title

      Summary of a Summary field

      Post

           Hello,

           I have a report layout that totals up the number of different types of estimates done by different Staff in a given period.

           The report is based on my Staff table

           It is sorted by the Staff ID.

           Each row has a portal to the Estimates table.

           In the Estimates table I have a EstimateType field and a Calculation field for each type,
           i.e. c_Count_BoQ  =  If (EstimateType = "Bill of Quantities" ; 1 )

           Then a Summary field which is the Total of c_Count_BoQ which counts up the number of BoQs that each Estimator has done.

           What I'd like now is to be able to total up the Summary fields. But I'm unable to select a summary field in the summary field options.
           Is there another way of achieving what I need?

      What I've circled in Red is what I want! I drew this in using MS Paint for illustration only!

      Untitled.png

        • 1. Re: Summary of a Summary field
          philmodjunk

               Assuming that there are no related tables holding data to be summarized in this report, define a calculation field with this expression:

               Summary field 1 + Summary field 2 + ....

          • 2. Re: Summary of a Summary field
            philmcgeehan

                 Yeah, that works for calculating my last column. Thanks smiley

                 But... 
                 I was after a way of adding like a Trailing Grand Summary part below the last row, or the footer maybe, that would total the numbers in that column.

                 Any ideas?

            • 3. Re: Summary of a Summary field
              philmodjunk

                   Is each row a record or a sub summary layout part?

                   If it's a sub summary layout part such that a summary field placed in the layout part shows the sub total, just put that same exact summary field in your grand summary, header or footer to show a grand total over all the records in your found set.

              • 4. Re: Summary of a Summary field
                philmcgeehan

                     Each row is in the Body part of the layout with a single row portal to the Estimates table showing the Summary fields.

                     If I add a the portal to the grand summary part it just has the same values as the last record in the report, and not the total of all of them.
                     If I take the field out of the portal it summarises all of the records, but not just the ones within my specified date range.

                     The portal is filtered to show only estimates within my specified date range — I suppose it's this that's confusing me.

                • 5. Re: Summary of a Summary field
                  philmodjunk

                       That's why I asked if each row was a sub summary layout part. What I suggested works in that case not in yours. Please note that from the beginning that I said "Assuming that there are no related tables holding data to be summarized in this report."

                       To me, it looks like basing a summary report on the Estimates table and using sub summary layout parts would make for a simpler layout design.

                       Getting a grand total from the context of a related table will be complicated as your total will be controlled by a relationship (and possibly a portal filter) to match to the correct records in your table. If you need a grand total based on every record in Estimates, this is simple. If you need to match to only a sub set of those records--such as all records dated in a particular range of dates, it's more complicated.

                       But these are complications that you can avoid if you base your layout on estimates instead of using one row portals to get your subtotals. (and yet, there may be very good reasons for using one row portals--you'll need to let me know if that's the case here.)

                  • 6. Re: Summary of a Summary field
                    philmcgeehan

                         Thanks for your help Phil,

                         I was obviously making it more difficult than it needed to be.

                         I've started a new layout from scratch based on the Estimates table and it all seems to summarising correctly so far.

                         Thanks again. yes