Summary Reporting Tricks

One of the best ways to learn about a particular FileMaker feature or behavior is to build a demo. You might build one in response to a client request, or to try to answer a question somone has asked, or just to see what happens. At any rate, today we’re going to look at three demos, each of which explores some aspect of summary reporting. For reasons of backward compatibility today’s demos are in .fp7 format, but you can convert them to .fmp12 format if you are so inclined.

What these three demos have in common is multiple sub-summary parts on reporting layouts… so what you see on the report depends on how you sort it.

Demo 1: table view reporting

With the release of FileMaker 10 in January 2009, developers gained the ability to produce summary reports in browse mode. At the time I threw together a demo to explore this capability in table view, and was pleasantly surprised by how powerful and easy it is. E.g., here I’ve clicked the Code button…

…and here I’ve clicked the Vendor button:

Here it is in layout mode… and note that in table view FileMaker displays a leading grand summary (circled in red above) even though there is no such part on the layout.

We see it even if we aren’t sorted…

…but we certainly don’t see that phantom leading grand summary when we view as a list rather than as a table.

Demo 2: faux body in subsummary

Have you ever wished you could optionally show or hide a body part in a report? FileMaker doesn’t offer an obvious way to do this, so developers will frequently build two nearly identical versions of the same report… one with a body part and one without. But here we’re using a single layout to show a) summary + detail…

b) just summary data…

…or c) just the detail.

Well, it turns out there’s a fairly easy work around: you can “fake” the body part by using a subsummary instead. Just ensure that it only has one record to summarize per group, and you can do it all with a single report layout.

This report must be a list, and not a table, because table reports require a body part. Also, this works because my product names are unique. If product names were not unique, I would use prod_id (or some other guaranteed-unique value) as the break field rather than product.

Demo 3: conditional subsummary

In this demo, when an item’s Status is “Scheduled” it will have a value in the Substatus field — otherwise Substatus will be blank. So the challenge is to generate a summary report showing Substatus only where appropriate.

If we take the standard approach to the problem, i.e.,

…note the superfluous gray rows under Pending, Cancelled and Completed.

If we instead use GetSummary to calculate the Substatus…

…the unsightly gaps will be eliminated.

Note that this demo allows us to subsummarize (actually “sub-sub-summarize”) by salesperson as well…

…but this isn’t the same as the “faux” body trick in demo #2 because each salesperson entry here represents multiple summarized records.

[Update 22 Sep 2013: I neglected to mention that this particular report only displays properly in preview mode. Joel Shapiro’s comments below prompted me to investigate doing this report in browse mode via the virtual list technique — see Conditional Subsummary Report in Browse Mode.]