How to Count Subsummaries in a ReportPosted: September 1, 2011
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.
To achieve this, we need 3 additional fields in the source table on which the report is based.
This may well already be there. It’s a summary field, which counts ATT__kp_ID (or other such “always present” field).
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.
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.