Recently on the Cafe Forum over at FileMaker Today, someone asked if it was possible on a sub-summary report, to display the percentage of records contained within that sub summary. Having never needed this functionality before, I decided to investigate how this might be done. What I came up with is a fairly simple and straightforward way to implement this.
You can download the example file for this article Here. (right click / save as)
First, a little background on sub-summary reports. In FileMaker, reports are run on a given set of records, called the found set. They come from a single table, however if the records contain information in other tables via a relationship, then that information can be displayed if needed. Within the found set of records, there may be certain records that can be grouped together. A classic example would be a Customers table that contained among other things, a field to record the country a customer is from. Now, lets say the user wishes to produce a report of their customers. The easiest way would be to just create a layout, based on a customers table occurrence, and place some common customer fields on the layout, such as the customers name, country, and contact details.
Now, let’s say the user wishes to group customers by their countries on the report. This would make sense from a sales perspective. It would be nice to see all customers for a given country grouped together. It would also be nice to see how many customers there are for each country, and finally it would be handy to see what percentage of the overall customer base each country produces.
All of this can be accomplished very easily by using a special layout “part” called a “sub summary”. Think of a sub summary part is a header, but for a sub set of records (in our case, country will be the header for our sub-sets of customer records).
There are two important things to know when using a sub-summary part for a report, these are:
• In the sub-summary part setup box, you must specify a “break field”. This is the field that “breaks” up the records into various groups (in our case, the Country field)
• In order to correctly display the sub summary report, your records must be sorted by this break field.
In versions of FileMaker prior to 10, there is an added requirement in that you must be in Preview mode. FileMaker 10 has a new feature in that sub-summary parts will display correctly whilst in browse mode, though you must still ensure your records are sorted by the break field.
Back to the issue at hand. Normally in answering a question for a user, the best way to convey the solution is to build a small example file to demonstrate the technique, and this is exactly what I have done.
Enjoy!
Weetbix, hi
Tried to download the sub-summary example, but Savefile is down, with no resumption date in sight.
(As you might assume, I need help on sub-summary parts too!)
Cheers
Hi Freeflight,
Still haven’t got a decent hosting sorted sorry. For now I have uplaoded the file to the following url:
http://www.megafileupload.com/en/file/138497/Sub-Summary—Showing-Fraction-of-Total-fp7.html
My apologies for the ridiculous hosting service and having to wait to download it.
If you still have trouble downloading, just flick me an email to daniel@digitalfusion.co.nz and I”ll email it to you.
note that the example file is for a specific solution to request someone had for a sub summary report. So it may or may not help you figure out what a sub summary report is, but if you go into layout mode and see how the report is setup, and the fact you need to sort it by the sub summary part break field, you should figure it out.
Cheers