Summary reports using relationships instead of summary fields

[xochi]

Herein, I describe a technique for creating a summary report that uses relationships instead of summary fields. I came up with this myself, but I'm sure it's been invented before. Comments appreciated.

Scenario:

You have a student database for a large school system. One table contains a list of student registrations (student, name, campus, class, etc.) You want to summarize the # of students at each campus, perhaps also summarizing by some sub-categories (gender).

Traditional Method:

Most folks familiar with FileMaker would approach this by first creating a summary field: sCount = Count(StudentID, sorted by Campus). Then you make a layout with a sub-summary part sorted by campus, and a grand total part where you show the overall total. Once you sort by the sort field (campus), and switch to Preview mode, fileMaker will create the report you wish.

Pros:

1. It's traditional. Most filemaker developers understand this method and can maintain/modify it, even if it's not documented.

2. Data driven -- if you add a new campus, the report automatically changes.

Cons:

1. Requires one summary field to be added to the table for each field that you are summarizing.

2. Speed -- if you have hundreds of thousands, or even millions of records, this report can be slow to generate.

3. Live updates -- Every time you create the report, it is recalculated. However, once you are viewing it in preview mode, changes to the data table will not be reflected in the report.

4. Vertical layout... "Crosstabs" are difficult -- if you need to have more than one sort field category (such as Campus + Gender), your report layout can not easily be layed out in a horizontal fashion. I.e. instead of this:

  Campus  Male Female

   Northwood  901 333

   Southwood  555 666

you have to have a hierarchical report like this:

  Campus 

     Northwood

       Male 901

       Female 343

     SouthWood

       Male  555

       Femle 666

Same information, but many people prefer the table format to the hierarchichal format.

Relationship Method:

Instead of running the summary report on the table itself, we create a 2nd table that is related to the first table by our category variables. Set the right relationships between the two tables, and we can use calculated fields with Summary statistics (Sum, Count, Max, Min, etc.) via the relationship.

In this example, we'd create a second table simply contains a list of the campuses, and is related to the first table by Campus. Then, we create our summary fields as calculated fields that access the first table through the relationship, i.e. Enrollment = Count(Data::StudentID)

To get a sum total, you use the FileMaker multikey trick -- in addition to the N records (one for each Campus) create one additional record that contains every campus name, separated by a linefeed. Make the first line say "Total" or "Grand Total" or something, and it will even look good on the report (make sure that you don't have a Campus named "Grand" or "Total"!)

Pros:

1. No new fields are required in the original data table.

2. It's a bit easier to get a horizontal (spreadsheet-like) layout.

3. Multi-category summaries, including non-equality ones (less than, greater than) can be done by using complex filemaker relationships (i.e. ReportTable to DataTable by Campus, Gender, and Department for "A" students only).

4. Update control -- if you use regular calculated fields, the sums and totals will update live. Or, you can use normal fields that are updated only when you run a script. The latter is very useful if many people are frequently viewing the report, and the data never changes. This can reduce calculation time 100 fold or more.

5. Archival reports -- with a little scripting, it's fairly easy to use this system to save archival reports. Just add a Date field to the reporting table, and the recalcuation script that duplicates the records and adds a date. You could probably do this with triggered auto-enter calcs, or with lookups as well.

Cons:

1. It's weird. Many filemaker developers will not understand this method, so be sure to document it.

2. Not data-driven. This only works well if the categories (e.g. Campus, Gender) are known in advance and do not change frequently. If the categories in the report table and the data table get out of sync, bad reports will occur.

3. Adds a new Table, TO, and relationships to your TOG.

[SurferNate]

This is actually a useful technique, I hammered out a messy version of it myself out of desperation once and I plan to refine the technique using repeating calculation fields. I actually use a reports table for this.

[xochi]

I'd avoid repeating fields if you can. They usually make me swear after a few days

I've seen described a further refinement of this technique for doing true cross-tabs (i.e. summarizing a list into a 2-dimensional array of categories/sums. The trick is that you create a relationship with two fields which you set on-the-fly with a script with a two-dimensional loop.

Something like this:

Report table is related to Data table by fields globalX and globalY.

Loop for X = 1 to N

  Loop for Y = 1 to M

     set globalX = X

     set globalY = Y

    commit records

     set  your calculated value  here...

      myTotal = Sum(ReportToData::salary)

   end loop

end loop

[comment]

There are other possibilities too (e.g. Edoshin's Fast Summaries), and it is also possible to combine different methods (e.g. related summary fields).

IMHO, any attempt to establish one ultimate, definitive "über-method" to do ANYTHING in Filemaker (if not in life in general), regardless of what the circumstances might be, is counter-productive. What might be a con in one case, may turn out to be an advantage in another. I try to keep my mind open to all options - including repeating fields.

[newLearner]

xochi, that is what i was trying to see if can be done with FileMaker.

I have large database, ~~300,000 records. I have Find script and while produsing summary report it is taking forewever and on the top of this I have multiple users. While trying to run the Find Script the users cannot use the product.

I will try your solution! thank you,

[xochi]

xochi, that is what i was trying to see if can be done with FileMaker.

I have large database, ~~300,000 records. I have Find script and while produsing summary report it is taking forewever and on the top of this I have multiple users. While trying to run the Find Script the users cannot use the product.

I will try your solution! thank you,

Hi NewLearner,

My database has around 300,000 records too (about 20,000 per month) I have a summary report using the technique described which summarizes about 25,000 records (of the 300,000) into 23 groupings with about 10 calculations per group. I'd estimate it's doing around 5000 calculations per second.

My setup:

FileMaker 7v4 Server

FileMaker 8v1 Advanced client

Mac OS X 10.4.3

Local 100base-T ethernet

FileMaker Server has 225 MB of RAM Cache (very important for performance!)