Shifting Columns in Cross Tab Reports

One of the 2009 Devcon presentations that I enjoyed the most was a session where John Sindelar presented the concept of using unstored calculated repeating fields to simplify code. The basic concept is that the calculation engine fires once for each repetition in a repeating calculated field, and through use of the Get(CalculationRepetitionNumber) function, you can have each repetition do something unique when it evaluated.

A very handy use of this is for cross tab reports, where each repetition can pull in data from a different table occurrence. The end result is that a cross tab report can be generated with a fraction of the fields that it would take to do this with separate calc fields for each cell.

Say, for example, that you have a cross tab report that shows categories running across the top of the report, and states running down the left, like this:

In this example, the rows of categories are actually a repeating field that is displaying all its repetitions on the layout in a horizontal format. The calculation defined within the repeating field is simple:

Case(Get(CalculationRepetitionNumber) = 1; CategoryTO_01::CategoryField:
Get(CalculationRepetitionNumber) = 2; CategoryTO_02::CategoryField:
Get(CalculationRepetitionNumber) = 3; CategoryTO_03::CategoryField:
Get(CalculationRepetitionNumber) = 4; CategoryTO_04::CategoryField:)

Every time the calc fires, it checks to see what repetition is it displaying, and pulls in data from the correct table occurrence. In this way, a single field with N repetitions can be used to create a cross-tab display with N columns, rather than needing to have one field per column.

In the course of using this technique, I’ve found additional ways in which one can accomplish things more easily than using the “one field per cell” method. An example of this would be shifting columns in a cross tab report to omit columns that are empty. The example code above includes all categories (all columns) regardless of whether there is data or not to display. In order to omit empty columns, just a few extra steps are required.

First, you’ll need a list of all possible columns, in the order that they appear. This list needs to exist in the global space, so a global field or global variable is required. Your list of all columns looks something like this:

allpaper
Bath Accesories
Bath Fixtures
Bath Linen

You can hard code these values, or extract them with a script. At this point you have a repeating field that displays all of the columns, regardless of whether or not they contain data, and you have a list of all columns, in the same order as the repetitions. Let’s call the repeating field “fullRepeatingField” and the list of column names “longList.”

Next, you need a list of columns that you wish to include in your final output. In other words, you need a list of columns that contain data. One way to do this is to do a “Count()” through each of the TOs that pull in data for the repeating field. Let’s call the list that holds the columns that we wish to include “shortList.” A calculation, in the form of Set Variable or Set Field, in a looping script is a good way to extract these values, and the calc would look something like this:

If(Count(CategoryTO_01::PrimaryKey);
shortList&GetValue(longList;1)&"¶";shortList)

This calculation says: “If there are any categories for the first column, add that column name to the list of columns that have data, otherwise, leave the list of columns that have data alone.” The order of these values determines the order that the columns display (topmost category is the leftmost column). In fact, you can use this method to sort columns as well as remove empty columns.

The final piece that you need is to create another unstored repeating calculation field to display the categories that have data. The calculation for this repeating field is the heart of the technique:

Let([vThisCategory = GetValue(shortList;GetCalculationRepetitionNumber);
// #1vPositionInFullDataSet = fnGetValuePosition(longList;vThisCategory)//
#2];fullRepeatingField[vPositionInFullDataSet] //#3)

Code Comments:

#1: The variable vThisCategory pulls in the name of the category that we want to show in this repetition. The first value goes into the first repetition, the second value goes into the second repetition, etc. In our example, if FileMaker is displaying the first repetition, this variable holds the text “Wallpaper”

#2: We need to know which repetition in the full dataset to find the value we need, that position is equivalent to the position of the category name in longList (because we set it up that way when we created longList.) Note that there is a call to a custom function “fnGetValuePosition;” you can find a similar custom function here. If you don’t have FileMaker Advanced, you can copy the code of the custom function into the calculation dialog. It’s essentially just a text parsing exercise.

#3: The last part is to pull the correct value out of the repeating field that hold all of the values. The repeating field notation makes this very easy, once we know which repetition that we want. This article shows how to reduce the number of fields needed to create a cross-tab report, then how to manipulate “columns” in repeating fields by comparing two lists. I hope you find it useful!