2 Replies Latest reply on Apr 9, 2014 1:47 PM by JesseHenderson

    FMP 12 Tip: Summary Recaps (Portal Subtotals)

    philmodjunk

      Title

      FMP 12 Tip: Summary Recaps (Portal Subtotals)

      Post

      Here's a report detail that has been requested a number of times here in the forum:

      The developer needs to set up a summary report with sub-totals and sub-headings for records that break the data down by some type of category and they need to be able to do so for varying found sets of records such as specifying all records for a given range of dates.

      So far, so good-- a basic summary report with summary felds, sub summary layout parts and a sort order that properly groups the records by the values in the category field will do the trick.

      But then comes the added request for some sort of summary recap at the end of the report. Maybe the client wants quarterly reports with a year to date recap at the bottom or totals of all records in the table.

      This need often results in the question: "Can I put sub summary parts in a portal?" and the answer is no. Then I'd get to tell them how to do this with a portal where you have one record for each value used as a category linked via a relationship to a different occurrence of the same table used for the summary report. Throw in the need to get sub totals for only a subset of all the records in the table and this can result in a fairly complex combination of global fields, scripts and relationships in order to produce the needed subtotals.

      This same request is also posted in the forum when the developer wants to put such a list of sub totals inside a tab control.

      With Filemaker 12, we now have an alternative method for producing this list of category subtotals that is much simpler to implement:

      The ExecuteSQL function can be used to produce a list of Categories with the desired sub totals. You can examine this demo file:  https://www.dropbox.com/s/690brtxi2qc46js/SummaryRecapDemo.fmp12 to see how it can be done.

      There are two examples of ExecuteSQL in this file, both use the Sum aggregate function and the Group By clause to produce the desired sub totals. The second unstored calculation field demonstrates the use of a WHERE clause to limit the data being sub totaled and shows how data not known at the time the expression was defined can be included in the SQL statement.

      A key detail that makes this work on the layout is to specify Char ( 9 ) (The tab character) as the field separating character. Then you can set tab stops in the appearance section of the Inspector to get columns of data.

      Just like a portal, this field can be given a scroll bar and it can also be set to slide up/resize enclosing part so that it can adjust to display varying numbers of rows of data.

        • 1. Re: FMP 12 Tip: Summary Recaps (Portal Subtotals)
          bcf06877

               I'm struggling with the SQL to make this work Phil. I'll need a little more time to work on this. I'll try the tool you suggested and let you know how it works out. Thanks so much for your assistance.

          • 2. Re: FMP 12 Tip: Summary Recaps (Portal Subtotals)
            JesseHenderson

                 Phil, I took a look at this demo file and it seems like a great example. Can you break down the SQL a bit more and explain the ORDER BY 2 DESC portion? What's the 2 and the DESC part?

                  

                 I have tried implementing the cSummaryRecap field and it's kind of working. Here's my expression:

                  

                 ExecuteSQL ( "SELECT ItemType, Sum ( Summary_ItemAmounts ) FROM ProductionItems GROUP BY ItemType ORDER BY 2 DESC" ; Char (9) ; "")

                  

                 but I've come up with a result that looks like this:

                 Pages         11448

                 PDF files 1431

                 Images 4293

                 HTMLPages 4293

                 Citations 1431

                  

                 The strange part is that these numbers don't represent the Summary_ItemAmounts totals on the page in the section above them, but what you see here for Citations is the total of the Summary of all ItemType Amounts for records not even shown here. And the other numbers are 3 and 8 times that value. I'm stumped due to my limited knowledge of SQL.

                  

                 Also, wondering how you got the field for cYearlySummaryRecap to appear and disappear with the conditional formatting. I was trying to dig it out of your file, but can't seem to see how it's done. I don't have a developer's version, so maybe this limits me.

                  

                 Thanks.

                  

                 JLH