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.