Ranking Entries in a Summary Report

Demo file: 2010-11-21-count-unique.zip (requires FM 10 or later)

Yesterday we looked at a simple method to flag unique entries in a found set. This time, we’re going to look at an additional use for this technique, using the same data set and demo file as last time.

As you may recall, we have a simple table of sales data, and previously we produced a summary report sorted by salesperson, but reordered by total sales, so that the top performing salespeople appeared at the top of the report.

Now it turns out that ranking the sales people isn’t quite as easy as you might expect. You might be tempted to try this:

But it doesn’t work, because @@ indicates the actual record number, and since we’re summarizing multiple records, we end up with:

…which is no good at all. So, we’re going to have to remove the kid gloves, roll up our sleeves, and show FileMaker who’s boss. And believe it or not, to make this happen, we’re going to use the flag_unique field that we discussed last time. Let’s take a look at our raw sales data. Here are just a few records, sorted by salesperson.

If only we could produce a running total of flag_unique… hmm… well, why not, let’s give it a try. What happens if we define a summary field, s_running_count_of_flag unique, like this?

Now what does our data look like? You know what? That’s going to work.

And here’s what our report looks like.

Okay, that’s all well and good, but what if we want to group first by zone, and then rank the salespeople within each zone? We can design a new report, summarized first by zone, and then by salesperson…

… and we can sort it first by zone and then by salesperson, but will our salesperson ranking do what we want?

Not yet. Susan should be #1, not #7; we need the ranking to restart for each zone. Fortunately, there is a way, and it doesn’t require defining a new field… we can just tweak the one we already have. Let’s revisit the definition of s_running_count_of_flag unique, and this time we’re going to check the “restart summary for each sorted group” box.

When that box has been checked, we then need to specify zone as the sort field (this is also sometimes referred to as the “break” field).

And while we’re mucking about, there’s something else about this new report that could use some tweaking. Take a look at the Zone column… we don’t really need to see the zone name repeated over and over again, do we? Let’s go into layout mode and apply some conditional formatting to the zone field.

What we’re saying is: only show the zone name if we’re on the first salesperson record of a given group; otherwise make the text color white (i.e., invisible). And here’s our final report, sorted by zone and by salesperson, with rankings correctly restarting for each zone.

Darn that looks nice, if I do say so myself. Believe it or not, there are some other cool tricks we can do with flag_unique, but we’re going to save those for another day.