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 familiar with FileMaker reporting; a report is usually based on a group of records within the database. The records are then sorted and displayed.

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 project, in FileMaker Pro 11, involved the need to create a quarterly financial statements. The statement is divided into several sections; an account summary, account activity for the previous quarter and a summary for the entire portfolio. All of the different sections had different search and field display requirements.

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. The activity section then had to be extended to include transactions that happened in the previous quarter but didn’t complete until the current quarter. The record set also had to be constricted for transaction that happened in the current quarter but won’t complete until the next quarter. It started to get a little more complex.

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.

The solution was to create the set of records, export them, import into a temporary area. This was repeated for each set of records. By flagging each set on import as set A, B, or C FileMaker could create the different sections required for the statement and the proper totals could be calculated.

Results: Prior to using FileMaker, the company would spend several weeks creating the statements with another database application, a spreadsheet, and finally into a word processing application for printing. Using FileMaker Pro the statement process dropped from 3 weeks to under 20 minutes. The entire user interaction was selecting the quarter they needed statements created for and loading paper into the printer. Most of the 20 minutes was spent waiting for the printer. Each statement was also automatically saved as a PDF that could be emailed to the client and for archival purposes. Needless to say, the client was very happy with the outcome.

Update: Newer versions of FileMaker introduced more robust reporting tools that would make it much easier to find all of the necessary records without the export requirements. The reporting would become a lot faster and the database application less complex as a number of temporary items are required in the original method.