FileMaker Weetbicks

Displaying Percentage of Records Within a Sub-Summary Part

February 2, 2010 By Daniel

Recently on the Cafe Forum, someone asked if it was possible on a sub-summary report, to display the percentage of records contained within that sub summary part. 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 May be Thinking…

Why not just use the "fraction of total" summary field?

The reason is because that summary field on it's own will only give you percentages based on a number field on the records, not the actual records themselves.

For example if your table was Invoices, and each invoice record had a date and a dollar amount, you could use the fraction summary field to tell you what percentage of money was invoiced in a given month, providing your sub-summary break field was the Invoices Month.

But what do you do if there is no amount field, or indeed any number field of any kind? What do you do if you purely want to obtain a fraction of the records themselves?

A bit of Background on Sub-Summary Reports

If you know what a sub-summary report is, you can skip this section.

In FileMaker, reports are run on a given set of records, known as 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.

The Solution & Example File…

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.

Download the Example File.