Trying to be less stupid about why report runs slowly ...
... or have Filemaker(v10) be less stupid
I have a report which runs, but darn slowly, for reasons that strike me as unnecessary. Let me explain:
Driven off two tables
Sales Invoices (invoice # is primary key), also contains "due date"
Line Items (invoice # is the most significant element of the primary key), which also has what I call "reportable fields"
Based on the comparison of due date to run date, reportable fields of the tables go into different buckets, and show up in different columns in a cross-tab report, with various totals and subtotals displayed.
MS Access or Crystal Reports would be completely efficient
If I were doing this in MS Access or Crystal, I would write an SQL query that joined the two tables, filtered, and triaged the data in to the buckets. The output from the ODBC engine would be piped directly into the report generator, the totals and subtotals would flow naturally, and the whole thing would be quite fast.
My Filemaker implementation
"Fast" is not happening in my Filemaker implementation.
My report based on Line items, which I filter down from around 50,000 to areound 800. I use an unstored calculation to compute a bucket number, and triage the reportable fields into four repeating fields (each repeating field has 7 slots, indexed by the bucket number). I have repeating summary fields which total up the repeating fields. None of these calculations are stored (this would be senseless, as the bucket number will change every day).
For clarity, I am using repeating fields, not using portals for the reasons articulated in the postscript to this (once again, this is FMP V10).
What I am seeing
FMP is taking a pass through the entire (unfiltered) table of Line Items for each summary field. These passes are collectively taking upwards of 20 minutes. Once those passes are done, report generation takes about 5 seconds.
Obviously, I would like to get rid of the 20 minutes. I can speculate that since the summary fields require data from a related table, that the engine thinks it needs to grind the whole thing completely.
My questions are why in fact is this happening or any suggestions as to how to stop it from happening.
PS - I used repeating fields, not portals for this cross-tab report. The database schema is completely out of control, so adding five obscure self-joins was not an appealing prospect. Also not too keen on trying to create an unlimited height portal.