Welcome back for the second installment in our exploration of Virtual List Reporting (a.k.a. VLR). Demo file: VLR-part-2.zip
To avoid repetition, this article will assume the reader is familiar with concepts and techniques introduced in part 1 (some of what follows may appear to be gibberish if the reader is not)… but to briefly recap, here are some benefits of VLR:
- Flexible framework accommodates complex reporting challenges
- Fast performance (we use the Multifind technique in this demo)
- No need to tamper with schema in your data tables or on the relationships graph
- Unlike traditional FM reports, you can easily combine data from unrelated tables (we saw this in report 6 in part 1)
- Under certain circumstances, VLRs can be much faster to develop than traditional FM reports (as per discussion of report 3 in part 1)
This approach to reporting can be ideal in situations where the “shape” of your data is known in advance. I don’t mean to imply that VLR cannot be used with more open-ended reporting challenges, but the sales data we will be reporting on today has well-defined boundaries, e.g., there are three Types and five Zones.
Each report is generated via its own script, which is self-contained and attempts to strike a reasonable balance between readability and conciseness.
In part one we looked at the first six reports, so today we’re going to start off with report 7 — which happens to be an expansion of report 1 (shown here):
Report 7
The difficult I’ll do right now
The impossible will take a little while
— from “Crazy He Calls Me” by Sigman & Russell, 1949
One benefit of VLR is that it provides a handy framework for accommodating “impossible” reporting requests. For example, did you notice that all the reports in part 1 focused on various combinations of type, year and month, but completely ignored the zone?
Of course there’s nothing stopping you from cloning the report 1 script and layout, and creating a simple cross-tab report for zones along the lines of the type report shown above… but what if the client wants to see the zone report and the type report on the same page? If your initial reaction is “FileMaker reporting doesn’t work that way”, bite your tongue, because with VLR, this sort of thing is a piece of cake.
Script strategy for report 7:
- Start with a copy of the report 1 script and layout
- Duplicate the main portion of the script so you have two identical parts to work with
- Part A will produce the zone segment
- Part B will produce the type segment
- Tweak and fine tune as necessary
Reminder: before generating the report, you can click this link…
…to see what’s going on behind the scenes during report generation.
Another reminder: the reporting script ends with a Pause step to facilitate $var exploration via the Data Viewer and/or the above window. The minor inconvenience of having to click Continue is a consequence of my decision to use $vars rather than $$vars.
Here’s the report in layout mode. Note the use of reps 100 and 103-107 to accomodate labels, and Hide Object to suppress columns 6 and 7 for the Type section.
The leading and trailing sub-summaries both use virtual_num_1 as the break field…
…and at this point a bit of explanation may be in order. To get the report to summarize into separate groups for zone and type, the reporting script assigns a “sort code” of 1 to zone entries…
…and 2 to type entries, and in both cases pushes $sortCode into $col_101[$counter].
On the report, $col_101[$counter] is rendered via cell_num_r[101], and it would be convenient if we could sort on rep 101 of that field, but unfortunately FileMaker doesn’t allow us to sort on any field rep other than [1].
27 Jan 2018: actually it is possible… see Sorting on A Field Rep > 1 for a simple trick to allow you to sort on any repetition of a repeating field.
The work around is to use a non-repeating “virtual” field… an unstored calculation with a text, or in this case, number, result.
[Planning for future needs, six virtual fields have been defined, but the only one used in today’s demo is virtual_num_1.]
And, finally, the reporting script populates $vNum1, and then sorts on the corresponding virtual field, like so:
Question: couldn’t we skip the sort code business entirely, and instead assign $vText1 to $col_100[$counter], and then do a descending sort on virtual_text_1 (since the client wants to see zone before type)? Answer: yes, that would work as well, but a sort code is more extensible, and VLR is never just about solving the current problem.
Report 8
Continuing our theme of basing the current report on an earlier one, report 8 is an expanded version of report 2, which as you may recall was a cross tab report summarizing sales by year and month, and looked like this.
Now, in report 8, we’re going to drill down and sub-summarize by zone, and then follow that with a total for all zones (equivalent to report 2)… so since we have five zones plus a total segment, and can acommodate two per page, the report will run to three pages and looks like this.
The report is produced by a three-level deep set of nested loops, like so:
- Outer loop: zone (all zones on the final iteration)
- Middle loop: year
- Inner loop: month
Note that as per report 2, this report uses the custom paper size trick to gain an extra horizontal inch for the report layout (so if you want to print it, generate a PDF first, and then print the PDF… or if you happen to have any 8.5″ x 12″ paper handy, load it into your printer and print directly from FileMaker).
Here it is in layout mode…
…sub-summarized using the methodology we saw in report 7.
Report 9
Here the challenge is to analyze the data by zone, year and type, and then summarize for all zones combined… on a single page in this format.
And if you think I took the lazy way out and resorted to displaying the data in portals… you’re absolutely right. Here it is in layout mode (this is a marked up printout showing the rep #s for the cell_num_r fields).
The report layout is based on a new table, virtual_parent (once again, containing “more records than you’ll ever need” — I arbitrarily gave it 100).
It contains two fields…
…and is related to virtual_list like so:
Bear in mind that ID is the only indexed field in the virtual_list table, so we use that on the child side of the relationship. On the parent side, id_virtual is an unstored calc with a text result, and its contents will be a multiline key (a list of foreign keys) based on a variable — $$vMLK[rep] — populated by the reporting script.
This approach may seem like overkill for a single-page report, but will make more sense in report 10, and also when you consider that this architecture is intended to support not only current reporting needs, but as-yet-unidentified future needs as well.
Script strategy for report 9:
- Outer loop iterates through years
(outer loop corresponds to virtual_list records, i.e., rows) - Inner loop iterates through zones + types
(inner loop corresponds to virtual_list columns) - Populate yearly total columns by summing existing $var[reps]
- Populate $vMLK[1] so id_virtual contains a valid multiline key
- Go to the report 9 layout (based on virtual_parent), isolate the 1st record, preview
Report 10
Now, inevitably, you’ve been asked to dig deeper and show the numbers summarized by year, zone and month, with each year on its own page (each year will correspond to a record in virtual_parent).
The totals beneath each column are related summary field [reps] which correspond to the columns they sum. E.g., the North zone Walk-In column (virtual_list::cell_num_r[3]) is summed by virtual_list::zz_s_cell_num_r[3], and so on.
And here’s how this monstrosity looks on the virtual_list table view layout.
In the interest of recycling as much of the report 9 script and layout as possible, I decided to leave the year in rep 1, and arbitrarily chose rep 51 for the three-letter month name display — in the above screen shot, for readability, I’ve temporarily placed it between reps 1 and 2.
Script strategy for report 10:
- Outer loop iterates through each year + month
(outer loop corresponds to virtual_list records, i.e., rows) - Inner loop iterates through zones + types
(inner loop corresponds to virtual_list columns) - Populate yearly total columns by summing existing $var[reps]
- Populate $vMLK[rep] so id_virtual contains a valid multiline key for each report page
- Go to the report 10 layout (based on virtual_parent), isolate the appropriate number of records, preview
To elaborate on the $vMLK business, each year will be reflected by a record in virtual_parent, which means, assuming you’re reading this in the year 2016, that there will be eight records (i.e., report pages), corresponding to the years 2009-2016.
For each parent record, the highlighted step below…
…constructs a 12-item list of IDs, corresponding to related children in virtual_list, i.e., the month records for that year. Here’s how they appear in the data viewer…
…but note that the spaces you see between each value are actually hard returns, as indicated by the tooltip on $vMLK[8]. The data viewer helpfully (and for once I’m not using the term sarcastically) displays the data horizontally.
Conclusion
I said this in part 1, but it bears repeating here:
VLR enables you to generate reports (reports that would normally require the creation of multiple helper fields and/or utility relationships) without touching table schema in your main solution or making any modifications to the Relationships Graph.
A related point, which I did not explicitly state then, is that if you are tasked with implementing complex reports in a live, multi-user system, this is a very safe way to go about it (because, once the initial tables and fields are in place, you will never lock table schema).
Whether the preceding paragraph applies to you or not, with VLR, after initial setup, all the heavy lifting is done at the scripting and layout levels. Reporting takes place in the presentation layer, and if you’re willing to invest a little time getting comfortable with the paradigm, this will likely become your “go to” system for all but the simplest reporting tasks.