FMP 12 Tip: Summary Recaps (Portal Subtotals)
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.