Non-Breaking Reporting Groups

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.

1-20-2015 8-45-43 PM

We have a requirement to produce a report showing personnel grouped by state, like so:

1-20-2015 4-16-27 PM

In addition to the personnel table, there is also a 50-record state table…

1-20-2015 8-49-35 PM

…and the two tables are related like so:

1-21-2015 11-26-42 AM

A standard approach to building this report would be to base it on the personnel table…

1-20-2015 9-26-49 PM

…with leading and trailing sub-summary parts defined like so:

1-20-2015 9-29-05 PM

So let’s choose all states…

1-20-2015 9-44-59 PM

…and run the standard report.

1-20-2015 9-37-09 PM

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…

1-20-2015 10-10-16 PM

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

1-20-2015 10-05-31 PM

And sure enough, when we get to page 4, there is no awkward break.

1-20-2015 10-11-50 PM

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”.

1-21-2015 8-08-02 AM

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.

4 thoughts on “Non-Breaking Reporting Groups

  1. Daniel Wood (@FMweetbicks)

    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

    Reply
    1. Kevin Frank Post author

      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

      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