Log in
Hot & Trending on FMT

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.

Kevin Frank

FMT Staff Note: IF you would like any of Kevin's tip or techniques incorprated into a solution or project contact him at his website below.

Hello and welcome. I’m Kevin Frank, and I’ve been using FileMaker Pro since the late ’80s… professionally since 1995. The expression “hack” has both positive and negative connotations. Here it is defined as “a tip, trick or technique that helps you solve a problem,” and the best hacks are the ones that can be re-used and modified to meet a variety of challenges. Or, put another way, as I read recently in Street Fighting Mathematics, “A tool is a trick I use twice.”

Kevin Frank and Associates provides custom FileMaker database solutions for business, government, education and non-profit clients. With over 20 years experience, we are certified FileMaker Developers with a solid reputation for high quality results. We invite you to call us today for a no-charge, no-obligation phone consultation.

To find many more in depth articles from Kevin and some must have tools and services visit his site at the link below:

Website: www.filemakerhacks.com