Case Study: Complex Reporting In FileMaker

Anyone that has worked with FileMaker Pro knows that one of the most powerful aspects of the software are the reporting capabilities. It can be very easy to generate summary reports with classifications, sub-totals, totals and many other types of analysis.

For those not that familiar with FileMaker reporting; a report is usually based on a group of records within the database. The records are then sorted and displayed. This is all that is required to create a simple report.

However, things often get more difficult - what happens when a client needs a report that does not fit into the standard search, sort and display format; adding totals, summaries and other types of analysis?

A current project involves the need to create a quarterly statement for each of their clients. The statement is divided into several sections; an account summary, account activity for the previous quarter and a summary for the entire portfolio.

The activity summary is the easiest portion to complete - search for records that fall into the previous quarter. The quarter is calculated based on the current date. The information is sorted and displayed. As they say, "Bob's your uncle".

The portfolio summary created a few wrinkles - it required a different set of records and needs to display a different set of columns than the activity summary. Both sections needed to be displayed on the same page. Now the fun began.

Here were the requirements:
  • A Client will have two sets of records to summarize
  • Record set A is in a found set of records for the previous calendar quarter for a Client
  • Record set B is in the entire set of records for a Client
  • Each record set needs to display a different set of fields
  • Both record sets will contain an unknown number of records that will grow over time
  • Both record sets need to be displayed on a single page, unless the number of records exceeds the printed page
  • Record set C will be a series of calculations based on the results of set A and B

Individually, each component of the report was quite easy to put together. The first version consisted of running the first report, printing, running the second report and printing. This however did not allow for the final set of numbers (Record set C) to be calculated as the first reports were done on the fly.