Today we’re going to look at a reporting challenge that comes up from time to time, and a technique that can be useful to have in your developer bag of tricks.
In the demo file, Non-Breaking-Groups, we have a simple personnel file containing employees spread across all 50 U.S. states, with between two and nine employees per state.
We have a requirement to produce a report showing personnel grouped by state, like so:
In addition to the personnel table, there is also a 50-record state table…
…and the two tables are related like so:
A standard approach to building this report would be to base it on the personnel table…
…with leading and trailing sub-summary parts defined like so:
So let’s choose all states…
…and run the standard report.
Things look pretty good till we get to the top of page 4, at which point we can see that Georgia inconveniently spans two pages…
…bringing us to the reporting challenge I mentioned at the outset. Is it possible to produce this report so that all entries for a particular state remain together on a given page? Yes it is possible, as we can see by clicking the “Non-Breaking Report” button.
And sure enough, when we get to page 4, there is no awkward break.
The trick is to base the report layout on the state table, display the employees in a portal based on personnel, make sure the portal has more rows than any one state will never need, and that it is set to “slide up based on all objects above” and “also resize enclosing part”.
Note that the layout consists of only a body part and a footer; also that the portal has no line and no borders. The horizontal rules have been manually placed: one inside the portal and one just above.
At the risk of stating the obvious, this technique should be used only in situations where the maximum number of child records is a) known, and b) not too large. It has limited applicability, because you must set the maximum number of portal rows, and because (unlike a standard report) you cannot have variable height rows, but as we’ve seen, in certain situations it can come in very handy.
Thank you. This is the best explanation of the solution to this common problem I’ve seen.
I appreciate you saying so David.
Love this technique and have used it quite a bit recently. 13 I believe has significantly extended the layout height so for most reports you can make it large enough to cater for all records without any fear of it missing some
Hi Daniel,
Thanks for commenting. I haven’t tested in a while, but my recollection is that the portal can span multiple pages as long as portal row boundaries are cleanly aligned with page boundaries.
Kevin
Kevin, thanks for pointing me to this article. Of course, the use case I have right now does require variable height rows. There is an optional 2nd field that will appear below the main one. That 2nd field could be multiple lines of text, or if blank, the part slides up to cover it. FileMaker could solve this by having options in layout parts to “keep with next X lines/parts” like InDesign or word processing programs. Or if portal rows could slide up, then this “hack” would work for this use case.
Oh well, I’m sure that I’ll find a use for this technique on another project. Thanks!
You’re absolutely right — this technique is not a good fit for variable-height rows.