How to Count Subsummaries in a Report

In a subsummary report, it’s very straightforward to count or total the records from the Table Occurrence on which the report is based, just by using a Count or Total summary field, which will work in whatever part of the report it is placed – subsummary, grand total, etc.

But often we need a count of the subsummaries themselves.  For example:

A report listing Attendances is based on the ATT Table Occurrence.  (Attendances are related to People (PPL).)  The report can include a subsummary, when the data is sorted by ATT__kf_PPL_ID, showing the total number of attendances for each person.  It then makes sense to provide a total number of subsummaries in the report (i.e. in this example the number of people), so that the total shows that there were, say, 123 attendances in the period, and that 15 people attended.

From there, we can also start to work out the average number of attendances per person, etc.  Surprisingly, Filemaker doesn’t make it immediately easy to do this, but, with a bit of trickery, it’s straightforward enough.

Additional Fields

To achieve this, we need 3 additional fields in the source table on which the report is based.

ATT_s_Count

This may well already be there.  It’s a summary field, which counts ATT__kp_ID (or other such “always present” field).

ATT_c_OneXth

This is an unstored calc, defined as:

i.e. ONE, divided by the total number of attendances for this person (when a report layout is sorted by ATT__kf_PPL_ID).

So, if there are 5 attendances for an individual, the value of this field for each attendance will be 0.2. Crucially, the total of these fields for each person will be ONE.

ATT_s_TotalPeople

This is another summary field, which is defined as the total (not the count) of ATT_c_OneXth.

Since the value of ATT_c_OneXth will always be ONE, ATT_s_TotalPeople is in effect a “count” of the people in the report, but derived via a bit of mathematical trickery.

The point is that for summary fields in the subsummary, we have to use fields from the table occurrence on which the report is based.  So, by creating a field in the ATT table which, in a sub-summary, will always hold “1”, we are able to emulate a count by using a total.

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s