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.

9-11-2013 4-53-45 AM

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…

9-11-2013 5-00-00 AM

…and here I’ve clicked the Vendor button:

9-11-2013 4-57-57 PM

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.

9-11-2013 5-06 pm

We see it even if we aren’t sorted…

9-11-2013 5-47-43 PM

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

9-11-2013 5-34-47 PM


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…

9-11-2013 6-11-51 PM

b) just summary data…

9-11-2013 6-16-54 PM

…or c) just the detail.

9-11-2013 6-19-42 PM

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.

9-11-2013 7-48-46 PM

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.

9-11-2013 10-24-50 PM

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

9-11-2013 10-31-18 PM

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

9-11-2013 10-51 PM

If we instead use GetSummary to calculate the Substatus…

9-11-2013 10-53-08 PM

…the unsightly gaps will be eliminated.

9-11-2013 10-43-07 PM

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

9-11-2013 11-14-06 PM

…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.]

7 thoughts on “Summary Reporting Tricks

  1. Joel Shapiro

    Thanks for this Kevin. Your conditional subsummary is very clever and close to what I’ve been trying to create, although it looks like it’s only a Preview/Print thing (because of the sliding). Any chance you’ve come across a way to hide (or minimize?) those ’empties’ in Browse mode?

    -Joel

    Reply
    1. Kevin Frank Post author

      I appreciate your comment Joel. The ability to shrink or grow objects (or rows) in browse mode would open up a world of possibilities, wouldn’t it? So far, I haven’t been able to figure out a way to pull it off.

      Reply
  2. Betty Besio

    Hi Kevin, Thanks so much for this. It was weird but I was working on a Summary layout when your FMDiSC email came in and I checked it out. Instead of building a new layout, I only had to change a body to a sub summary, modify the sort and away I went. Thank you! Where’s your tip jar? I owe you at least a coffee or a beer! Best wishes!

    Reply
    1. Kevin Frank Post author

      So glad to hear that Betty. Next time I’m in town for an FMDiSC meeting, I’ll hit you up for a big glass of organic carrot juice.

      Reply
  3. creativebiz

    Wold love to see these demos update to later FM versions. Can’t open them! But it’s exactly what I was looking for!!

    Reply

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