Virtual List Reporting, part 2

Welcome back for the second and concluding installment in our exploration of Virtual List Reporting (a.k.a. VLR). Demo file: VLR-part-2.zip

2016-06-21_045615

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.

2016-06-23_210841

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):

2016-06-21_13-23-37

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.

2016-06-21_044637

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…

2016-06-22_030612

…to see what’s going on behind the scenes during report generation.

2016-06-21_044809

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.

2016-06-22_031741

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.

2016-06-21_223105

The leading and trailing sub-summaries both use virtual_num_1 as the break field…

2016-06-22_032941

…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…

2016-06-22_034150

…and 2 to type entries, and in both cases pushes $sortCode into $col_101[$counter].

2016-06-22_034810

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]. The work around is to use a non-repeating “virtual” field… an unstored calculation with a text, or in this case, number, result.

2016-06-22_035654

[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:

2016-06-22_040515

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.

2016-06-22_132214

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.

2016-06-21_051100

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…

2016-06-22_133425

…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.

2016-06-25_11-26-44

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).

virtual-parent-marked-up

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).

2016-06-25_11-47-14

It contains two fields…

2016-06-25_11-48-43

…and is related to virtual_list like so:

2016-06-25_11-46-08

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).

2016-06-25_17-58-52

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.

2016-06-27_133722

And here’s how this monstrosity looks on the virtual_list table view layout.

2016-06-25_19-39-35.png

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…

2016-06-26_113441

…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…

2016-06-26_120202

…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.

12 thoughts on “Virtual List Reporting, part 2

  1. Ruben van der Laan

    Brilliant, I am going to use this asap and see how it goes! Thanks for the great effort to put this all in writing.

    Reply
  2. Muhammad Mushtaq

    You are great ; i am looking cross tab report. This is great example for me and other who is new in filemaker.

    Reply
  3. Goutam

    I used a similar approach to create a virtual list, I executed an sql query and got the number of results stored in a global variable and split the query result into a column in rowNum and rowData. The following link can be used to access it https://postimg.org/image/4ekezznfb/

    But the problem is, how do I create the number of rows and populate the rowNum value in the table dynamically?

    Reply
    1. Kevin Frank Post author

      As mentioned in part 1, you pre-populate the virtual list table before hand with “more records than you’ll ever need” and rowNum (as you call it — it’s called id in my demos) is an auto-entered serial number starting with 1 in the first record.

      Reply
  4. Steve

    I really enjoyed both parts of the series. I look forward to digging in, but I’ll have to wait until I upgrade from 12 as the sample files can only be opened with 13+

    Reply
    1. Kevin Frank Post author

      Thanks for taking the time to comment. Unless I’m mistaken the only reason the demo files required 13 was because they used a popover to facilitate running the report scripts… here are the same demos with the FM 13 restriction removed…

      Reports can now be activated directly via the scripts menu.

      Activate reports directly from Scripts menu

      Reply
  5. Steve

    Thanks Kevin for adjusting those 2 demo’s. I did have a question that’s probably obvious to everyone but me.
    It seems that you can only print the report from Preview Mode. If you exit preview mode, or print the report, the report disappears.
    Also if you adjust the script to avoid Preview Mode, you can not (well I couldn’t) then print the script from Browse Mode. Is this expected behavior with VL, or as a newb am I just missing something?
    Thanks again for posting the 2 demo’s for FM12
    Steve

    Reply
    1. Kevin Frank Post author

      As was explained in part one, this is a consequence of my decision to use $vars instead of $$vars.

      Reply
      1. Kevin Frank Post author

        Actually wasn’t in part 1, it’s here in part 2… in the Report 7 section…

        “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.”

        Reply
    2. Kevin Frank Post author

      OK I just reread your message more carefully… You must be doing something wrong because I can assure you this method works fine for printing without previewing. Since $vars are scoped to the current script, make sure your print step is in the reporting script, and not in a subscript. Or modify the technique to use $$vars instead, but then you will need to make sure those $$vars are cleared after running each report, or chaos may ensue (if you are using FM Advanced, you may want to check out ClearRepeatingVariable at http://www.briandunning.com/cf/1368).

      Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s