By Daniel Wood, 15 December 2010
A sub-summary report, is a special kind of report where records are grouped together by a specified field known as the "break" field (because it acts as a break between groups of similar records).
As an example for this article, consider a very basic table containing 3 fields:
Each record comprises a country, a city within that country, and that cities population. There may be multiple records for a given country, as that country may have many large cities. Others however may only have one or two cities.
A sub-summary report could be run on this table, with a break field of "Country". The output would display countries, and their cities & populations beneath, such as the following:
This is a very typical sub summary report. The sub summary part is in blue, and the body part is in green to illustrate the respective parts (this is just test data, the populations are well off).
This is fine, but what happens if you have many countries with only one city? As you can see in the example picture, England, Japan, and Fiji all have one City listed. Each country thus requires 2 lines on the page - 1 for the country, and 1 for the city.
Wouldn't it be nice if you could somehow show the country and the city on the same line in these situations? Well you can with a little jiggery pokery.
Here is the layout mode view of the report above, blown up to 300%. The key concept in this technique, is that as long as the top edge of the sub-summary field is in the sub-summary part, then it will render on screen when in preview mode.
The technique thus involves reducing the size of the sub-summary part to 1-2 pixels in height, and placing just the top edge of the sub-summary field inside the sub-summary part, as shown below.
This is a modified version of the original report, at 300% a couple of things have changed:
As you can see, it appears as though the body-part records begin directly to the right of the sub-summary field. For those countries with 1 city, only one line is used. In actuality, the country is offset by 1 pixel higher than the city and population, but this is barely perceivable to the human eye.
This is a great paper saver for long reports, and not to mention it looks tidier for sub-summary parts with only one record in them.
A Couple of Things to Note¦
As mentioned earlier, fill colours were removed from the sub summary part, and the body part. This is important because otherwise the technique does not work. The reason the technique works in the first place is due to the way preview mode renders the report. When it renders, it renders from the top down. Anything it renders is placed in the top of the ordering on the page.
It just so happens that the sub-summary field renders over-top of the body part just fine, and this gives the illusion that the sub summary part, and body part are in-line with each other (albeit 1-2 pixels out). However when the body part then renders, it's contents are placed at the top of the order on the page. Thus anything in the body part will draw over anything already drawn. Most importantly this means any fill colours used in the body part will appear over top of the sub-summary field, hiding it from view (as an alternative, you can use things such as line separators for between records).
Also note that while the sub-summary field, and body fields appear to be in-line, they can never truly be vertically aligned. The closest you can get is a 1 pixel difference in height, however when viewing a printed report, this difference is negligible.
This technique does not work in Browse mode in FileMaker 10 or 11, and is only useful if you are viewing in preview mode or printing/saving the report. If you try to view in browse mode you will see something like the following:
Please find attached an example file. This file was used for all the screenshots in this article, and is fully commented to help you fully understand what is going on in the file.