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 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.
FMT Staff Note: IF you would like any of Kevin's tip or techniques incorprated into a solution or project contact him at his website below.
Hello and welcome. I’m Kevin Frank, and I’ve been using FileMaker Pro since the late ’80s… professionally since 1995. The expression “hack” has both positive and negative connotations. Here it is defined as “a tip, trick or technique that helps you solve a problem,” and the best hacks are the ones that can be re-used and modified to meet a variety of challenges. Or, put another way, as I read recently in Street Fighting Mathematics, “A tool is a trick I use twice.”
Kevin Frank and Associates provides custom FileMaker database solutions for business, government, education and non-profit clients. With over 20 years experience, we are certified FileMaker Developers with a solid reputation for high quality results. We invite you to call us today for a no-charge, no-obligation phone consultation.
To find many more in depth articles from Kevin and some must have tools and services visit his site at the link below:Website: www.filemakerhacks.com