Two sets of summary data, One report

By Jerry Salem

I was working on a project for a client recently.  They were replacing an old system with a new one built using Filemaker 9.   The system is a standard client, invoice database.  One of the reports requested was a summary of sales.  The report presents sales summarized by product type, in addition sales sumarized by product location.  It was presented as a nice one page summary to ‘the boss’ weekly.

Fig One (Original Report from old system)

Sounds like a nice summary report.  Unfortunately there is not an obvious way to present a set of records summarized by two different criteria in Filemaker.  As a first pass on creating this report, I wrote a script to present the data requested, that automatically ran two reports.  The work flow of the end user is to click a button and two reports printed out.  The first page, prints the sales records summarized by product type, the second page is the same report, but sorted and summarized by location.  When I showed the reports to the final user, she said.  ‘My boss wants the report on one page.  In the old system I had to cut and paste the two reports together.  We REALLY want them on one page’.

Fig Two (two pieces of paper)

Given this mandate from the user, it was back to the drawing board.  It was now obvious I can’t use the standard, sub summary/body type of report in FileMaker.  With the introduction of the list function in version 9 this type of report is now possible.  The list function concatenates a group of records (similar to a portal).  So to create this ‘report’ I need to create two calculation fields that show the data in a ‘table format’

The report is run from the context of a ‘system table’.  The first step is to create two relationships from the system table to the ‘helper’ tables (category and location).  For this report we will show all the categories and all locations using Cartesian join (‘x’) relationships.  In a real report, you would probably only want to show active categories and locations.  Then for each of those relationships there needs to be a relationship from the Category/Location table occurrences to their respective lineitems table occurrences so we can pull the related information.  These relationships need to be filtered by date range.  The dates are held in the global fields in each table.

Fig 3 Relationship graph

The next step is to create the reporter fields.  The list function only takes a single field as its parameter, so we must embed all the fields in one calculation.  For the Category lines the calc is called z_ReportLine and looks something like this;

Category & Char ( 9) & Sum(_sys_cat_LINEITEMS::Product_Quantity) & Char(9) &”$”& Sum(_sys_cat_LINEITEMS::Product_ExtendedAmount)

Where; Char(9) is a tab character (this is FMP 10 feature).  In a real example there would probably be separate fields to calc the number of items, and the sum of the sales.  But in this example I am just calculating this info on the fly.  Also note, the dollar amount field is not formatted to assure two significant figures after the decimal point.  Since this is a text field, you must manually do that.  The final result of this calc is a Category name, Count of items sold, and Total amount of items sold, each separated by a Tab character.

There is an almost identical calculation in the Location table, also called z_ReportLine.

The final Pair of calculations uses the List function.  They are in the system table.  The Category calc is called, CalTable and looks like this; List ( _sys_CAT::z_reportLine ).  The location field is called locTable; List ( _sys_LOC::z_reportLine )

Next draw the report in layout mode.  Put the two fields catTable and locTable on the layout, make them large enough to hold the max number of categories and locations you anticipate, align the left sides.  Don’t forget to use sliding on the fields to reduce whitespace on the printed report.  To get the tabs to line up use the text format tool, and put in tab stops in appropriate places.  Make sure the tab stops are the same for each .

Fig 4 Report in layout Mode

The final step is to create a script to fill in the global start and end dates in the tables.  There are may ways to accomplish this.  The most important part of the script is to also populate the global fields in the Category and Location tables.  This is the ‘hook’ that lets the calculations in the Category and Location tables get the sum of quantity and amount fields from the LineItems table.

Fig 5 Script

The final report.  One set of data, summarized using two different criteria.  All on one page <grin>

Fig 6 Final Report

The client loved the new report.  The work flow couldn’t be easier; click a button, make sure the dates are right, and print.  After I showed it to them, I called my contact aside.  I needed to find out how she created the report in the old system.  The previous system they had was in Filemaker 3.  Her words, “I needed to cut and paste the two separate reports together” haunted me.  Is there some now abandoned technique in Filemaker 3 that let you copy and paste report parts?  “Show me how you used to run the report in the old system?”, I asked.  Then she showed me two separate reports that printed out.  Opened her drawer pulled out a pair of scissors and cut the top off the first report.  Used some tape to put the reports together, then walked over to the copy machine.  “I used to use glue, but I found that using tape you don’t get a line between the two halves of the report,” she confessed.

True story.