Building a Cross-Tab Report

FileMaker™ is, and always has been a remarkable reporting tool but there are times when one needs to go outside of its native functionality or really think outside the box. In this post, I’m going to describe one such situation and how I solved it.

Now, I should add that there are multiple ways to solve any FileMaker™ problem; I could have used ExecuteSQL (Click here to read a post on this subject) or Bruce Robertsons’ amazing Virtual List but I didn’t. I thought of them, considered them carefully and then decided that there was a simpler way of doing what I needed.

But before we go into detail, let’s just explain a simple but fundamental principle that affects most reporting. This is that in any report using records from a simple parent-child relationship, i.e., Invoices and Invoice Items, the report is generated from the child table, using the relationship to the parent to display related information. (While it would be possible to generate such a report from the parent table, you would have to display the child records in a portal and, since you won’t know ahead of time, how many rows (child records) you will have, the report is not only difficult to design properly but it relies on guesswork (and you never want to do that).

In the project we are working on, the client (a media company) puts together bids (quotes) for their clients advertising projects. A project is for a specific brand, in this example, we are using a fake company (at least we think it is) and will have a varying number of spots (for TV generally) of different lengths.

Each bid will have x number of products/services included but let’s start with the Bid details themselves.

This bid has three spots and a number of line items.

which requires only standard printing techniques and, in printed mode, will have a cover page and then one or more detail pages.

And now we get to the problem.

A bid may have multiple revisions; a revision being a variation on the original bid with some items that are consistent and some that are not. The Spots themselves may change and the bid items may well vary also with some items being removed and others added.

Click here to read a detailed explanation of how we create sequentially numbered revisions.

What we wanted to be able to do is display the bid and its revisions in a columnar report so that anybody can see the big picture in order to make a decision as to which to approve.

The main issue here is that we need to be able to line up the items and revisions on a row by row basis, sorting all records by the Category and then by the order that the client wants to see those items but, since each revision can have some, or all, different items, this is where we have to start

thinking-outside-the-box

To start the process, we are going to create a new table, BidRevisions that will be used to generate the report. (Each time we run a report, we delete all of the records that were previously there and start over; thus making it a dynamic report that always reflects the current state of the bid in question).

In that table, we create a field called ProductID and connect it to the Products table

We now need to create a calculated field (in the Bids table) to get the _pkid (primary key) of the original bid plus the _pkIDs of the revisions.

Before we can do that however, we need to do a couple of things. Each revision has the _pkID of the original bid entered into a field called BasedOn_ID and we have a relationship from that field to the original bid.

The relationship also excludes any revisions that have been rejected.

With that relationship in place, we can now create the calculated field.

In this solution, we are connecting to each table via a global field (G_BidID) and setting that field with the Bid we are working on.

We now need a new relationship to connect the calculated field to all of the Bid items for the original bid and its revisions.

ALL of the potential products/services are loaded into each new bid (based on different criteria) but only appear in the bid itself once a quantity is entered, That’s why we have the second criteria in the above relationship to only show items that have a quantity.

We are now ready to start building the report (via a script). The first thing we do is check that the bid has revisions; if it doesn’t, we stop the script right there as this report is completely unnecessary.

Now we go to the related products and loop through them building a return delimited list of all the product ids

Then we use a custom function to remove any duplicates.

This custom function is incredibly useful and you should have it in all of your solutions.

Brian Dunnings website is, almost certainly, the best place to search for a specific custom function. As always, there is no point in reinventing the wheel.

Then we go to the BidRevisions table (which has already been emptied of all records, using the Truncate Table script step) , and we create a new record for each of the different products that are being used in the Bid and its revisions.

The methodology we are using here is fast and efficient (a big thanks to Andy Persons who came up with it). It starts with a list of values, does something with the first value, then resets the variable removing the value that was just used. When there are no more values, the loop ends. (I use it constantly).

At the beginning of the script, we’ve also set a variable with the value of the calculated field and we’ve added 6 global fields to store the different ids.

As part of the script we then set those global fields with the relevant values.

If you’re wondering why we are limiting the report to the bid + 5 revisions, the reasoning is simple. Firstly, we didn’t know if this report would be useful and the only way to find out was to build it and show it to them; they loved it! Secondly, we could only fit 6 vertical columns and the product description on a single page. (The functionality can easily be expanded to show more revisions if, and when, that becomes necessary).

The first global connects to the bid itself and looks up the client, the job, brand, etc. Each of the other globals connects to a revision and displays the data from that revision. Some products have information in all columns and others do not, so the end report is a matrix.

In total we have 6 relationships using a combination of the Product Id and the global field (for the specific revision)

We are also excluding any records where the quantity is empty.

Below you will see the relationships for the 1st two revisions

All we have to do now is add a few calculations to add up the values and display them in the report. Because we are also grouping the records by Category, we also need to lookup the CategoryID which we link to the Categories table

And then we need relationships (one for each revision) to include the Category so we can sort and group properly as well as get the totals.

Lastly we need to add the calculations and summary fields.

And our report is done.

The script itself is quite simple and very short.

We only have a small number of fields in the table

and the relationship graph is also very simple.

Because the report is built from scratch each time, there is virtually no overhead or performance impact. At worst, there would only be as many records as there are products (less than 100).

Is this the only way to achieve it? Absolutely not but it is a simple method which only took a couple of hours from start to finish. I hope you find it useful.

Leave a Reply

Subscribe To Our FileMaker Tips & Tricks

Join our mailing list to receive the latest FileMaker tips, tricks and video how tos from Michael Rocharde

You have Successfully Subscribed!

Subscribe To Our
Video Posts

Join our mailing list to receive info on new videos as we post them

You have Successfully Subscribed!

Website Apps