Virtual List Reporting, part 1

Self-plagiarism alert: to avoid repeatedly referring the reader back to earlier virtual list articles, portions of text from those earlier articles are incorporated here.

Welcome to the first installment of a multi-part series on producing reports using the virtual list technique, or more properly, collection of techniques. Demo file: VLR-part-1.zip

Invented and popularized by Bruce Robertson, virtual lists are incredibly flexible, and have made a number of appearances here in the past, including…

• Long Documents in FileMaker 11
• Virtual List Charts, part 1 & part 2
• Summary List + Virtual List
• Conditional Subsummary Report in Browse Mode

The Basic Idea

In case you aren’t already familiar with virtual lists, in a nutshell, you create a special utility table in your solution, and pre-populate it with “more records than you’ll ever need” (there are 10,000 in today’s demo). The records in this table will derive their data “virtually”, by reading it from some sort of array… typically, one or more variables.

This Implementation

There are various ways to implement virtual list reporting, a.k.a. VLR, and today we’re going to concentrate on one particular approach that has worked well for me recently. Specifically, we’re going to use the Multi-Find technique to summarize values from Sales Data and Web Visits, and push that data into $variables, or, more accurately, $variables[withReps], which will be parsed by an unstored repeating calculation in the virtual list table… with table columns corresponding to individual named $vars, and table rows corresponding to $var[repNumbers].

If you’re already familiar with the virtual list technique the preceding paragraphs probably make at least some degree of sense; if you aren’t and/or they don’t, don’t worry, things should become clearer as we explore the examples.

Why Bother?

It’s a fair question, because on the surface VLR may appear to simply be a more convoluted way to accomplish things that you already know how to do via standard FileMaker reporting techniques. And for basic reporting tasks, I would agree.

But as requirements become more challenging, 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.

Once the Virtual List table has been defined, the heavy lifting will be done primarily at the scripting level… and of course at the layout level, but that would be true of traditional FileMaker reporting as well — in fact VLR layouts are typically simpler than corresponding standard FM reporting layouts.

Bottom line: willingness to invest in a bit more complexity up front can help things run more smoothly down the road. Let’s take a look at the structure of the demo, and then we’ll explore the report examples.

Note: Sales values are updated at demo startup, so some of the numbers you see in your copy of the demo will not match the ones shown here.

The Demo

The demo consists of two primary data tables (Sales and Web Visits), the Virtual List table, and a number of supporting tables.

Note that the primary data tables are almost completely pure, i.e., devoid of reporting “helper” fields, apart from a single summary field in Sales…

…and, likewise, a single summary field in Web Visits.

The Virtual List table is a study in minimalism as well.

There is a table view, which can come in handy for troubleshooting…

…and, finally, here’s the Relationships Graph, which is also extremely basic.

Report 1

We’re going to begin with a very simple cross-tab report. Starting from here…

…how do we end up here?

As mentioned above, the bulk of the heavy lifting is performed by the reporting script, which begins with some basic prep, and the initialization of a number of $variables…

…including $listY and $listT, which end up being populated like so (note that the first row of $listT is intentionally empty):

Next the script loops through the two lists and populates $col_x[rep] variables, where x represents columns 1 through 5, and [rep] corresponds to each iteration of the outer loop, i.e., the numbers 1 through 8 (since there are eight values in $listY — assuming you’re running the demo in the year 2016).

At this point the script has loaded up a bunch of $var[reps]…

…which will be parsed by the Virtual List table into an unstored repeating calc field, cell_num_r:

Cell_num_r extracts data from the aforementioned $var[reps] by translating calc reps into $var column numbers, with the ID value of each table row corresponding to a $var repetition number. The column translation works because Get(CalculationRepetitionNumber) enables each rep of the calc to know its own rep number.

If you’re wondering about the “[1]” after the ID in the calc def, it ensures that the calc reps correctly refer to the non-repeating ID field. As an alternative, I could have wrapped ID in an Extend function, but I prefer array notation (i.e., “[n]”).

So, to give a specific example, rep 2 of cell_num_r in row 8 above will first assemble a $var[rep] named “$col_2[8]” and then will Evaluate that var, returning 1072493.34.

The remainder of the script is devoted to displaying the report.

Here is the report in layout mode, with reps 1-5 of cell_num_r individually placed from left to right, and reps 2-5 formatted for readability to display zero decimal places.

And, as we saw at the outset, here is the end result.

Note that the reporting script ends with a Pause step to facilitate $var exploration via the Data Viewer. You will need to unpause before moving on to the next example.

Okay, that was a fair amount of explanation, but it also took care of a lot of foundation-level material. Moving forward, we will focus on specific points of interest in reports 2 – 6.

Report 2

Here we have a year/month cross-tab Sales report…

…and here it is in layout mode. We are going to use the same layout for report #3 as well, so there are a couple of merge variables in place of hard-coded text labels. In the body we have reps 1 through 14 of cell_num_r…

…and the totals in the trailing grand summary come from a corresponding 150-rep summary field, defined like so (we don’t need to specify the number of reps for the summary field; they are inherited automatically from cell_num_r):

The script for Report 1 was intentionally written to be as transparent as possible, e.g., it employs no custom functions and uses only static variable declarations. In the remainder of today’s reporting scripts we are going to make things more efficient by utilizing a pair of custom functions.

First, CustomList allows us to replace this…

…with this:

CustomList also facilitates building the month number list.

(These are the most simplistic examples of what this amazing CF can do… even if you normally avoid CFs on general principle, I recommend making an exception for this one.)

Second, SetVarByName facilitates the creation and population of $col_3 through $col_14, i.e., months 1 – 12. Here the CF merely eliminates a bit of tedium; in reports 4, 5 and 6 it will play a more substantial role.

Finally, note the use of the custom paper size trick to gain an extra horizontal inch for the report layout.

Report 3

As mentioned, this report uses the same layout as report #2, although this time we’re summarizing Web Visits instead of Sales.

This illustrates one of the advantages of VLR, because, having already built report #2, I was able to bring report #3 online in (literally) five minutes by duplicating the script, modifying the lines highlighted in yellow, and adding the lines highlighted in green.

Report 4

This is an expanded version of report #1, with sales order counts and percentages.

SetVarByName gets more of a workout here.

Report 5

Here we’re taking report #4 one step further, by adding a trailing grand summary with three-year averages looking backward from today’s date.

Once again, thanks to VLR, an additional block of script code accomplishes this, and we don’t need to touch the Relationships Graph or table schema.

Note that in line 63 above we set $cNum to 22. One of the tricks VLR keeps up its sleeve is to work some of the magic in higher-numbered column $vars. The report layout displays reps 1-12 of cell_num_r in the body, and then reps 22-32 of the same field (not a summary field) in the trailing grand summary.

If you display the virtual list table before generating the report and scroll it righward like so, you will see numbers like these appear in row 8 during report generation.

As mentioned earlier, what you see in your copy of the VLR demo won’t match what you see here because sales data is auto-updated on demo startup; also if you’re running the demo in the year 2016 the row will be 8 — otherwise it will be higher. (If it’s lower, please clue me in on your time travel secret.)

Report 6

Our final report today is a further refinement on reports 4 & 5, with two new columns at the far right showing the number of web visits and percent of those visits that resulted in a purchase.

Unlike a standard FileMaker report, which would typically be based on a data table such as Sales, the virtual list table doesn’t care where the data comes from, and you are free to combine data from unrelated tables. Have your script populate the $vars, and the virtual list table will do the rest.

Conclusion

At the risk of stating the obvious, nothing here is carved in stone. If you’ve made it this far, you are probably thinking of ways you can adapt or improve on some of these ideas for use in your solutions. Maybe you’d rather use $$vars than $vars, or would prefer to generate a single 2 dimensional delimited $array (or $$array), and parse the data from that. Those approaches have been explored in some of my earlier virtual list articles, and there are pros and cons associated with each.

At any rate, today we’ve made the tiniest scratch on the surface of what can be accomplished with Virtual List Reporting. In part 2 we’ll dig a little deeper.