You’d Like a Report? I Have 5 Questions for You

This article is not about how to create reports in FileMaker. Creating good reports in FileMaker involves an understanding of layouts, relationships, scripts and possibly other concepts, depending upon the report. It would make for an awfully long article. That being said, creating reports is pretty early in the learning curve for learning how to develop in FileMaker, so if you know how to develop solutions in FileMaker, you probably already have a pretty good idea of how to generate a printed report.

On the other hand, even if you’re an experienced FileMaker developer, you’ve probably noticed that it’s sometimes difficult to capture and communicate the requirements of a report. We’re asked for printed reports all the time, and without the 5 questions outlined in this article, getting a report right would end up with a lot of trial and error, or a lot of back-and-forth emails and phone calls to be sure we’re getting the report right.

This article is about how to describe a report in a way that a developer can easily understand, without having to be a developer yourself. Even if you are doing your own development for your own reports, I think you’ll find this a useful way to think through and consider all your reporting options and make sure you don’t miss anything.

First, a few words about reports

A lot of times, when we’re gathering requirements for a new project, our clients, quite naturally, start out talking about printed reports. Before they have a FileMaker database from us, reports seem like the biggest challenge, and the importance of getting the right data on their printed reports looms large in their minds. Many times, generating reports by hand (with spreadsheets or documents) was how they kept themselves from going crazy while their data tracking needs began to get out of hand.

In reality, though, creating reports from a well-organized database is fairly simple for us, as developers, to do. So long as we’ve understood clearly what it is you’re trying to keep track of (and we’re especially good at understanding that), creating a report is just a matter of arranging your data in a particular way on the page.

This is actually a key advantage of the way we gather requirements and build solutions. Many developers, especially those teaching themselves, naturally start from the requirements of a desired report, and try to build a database that will generate that report. The result is a database that makes one report really well, so long as nothing changes. We, on the other hand, start by creating a model of your data and how the different pieces fit together in the real world. The result is a database that can be used to produce any report that’s based on that data.

Sometimes, in fact, potential clients are shocked when we don’t want to talk about their reports early on. When we present a proposal for a new project, our section about reports usually just says something like “we assume 6-10 reports, to be determined later in the project”. This is because in our experience, that all-important list of reports that we were talking about at the beginning of a project is almost always completely different by the end of the project. Two things happen: First, as the solution develops, a lot of the reports that seemed super-critical turn out to be totally unnecessary once a user can easily work with their data on the screen. Second, having a good database model opens up possibilities for new reports that the client couldn’t even imagine were possible before the database solution took shape.

The reason we can safely estimate a project without knowing every last detail of every last printed report is that with a well-organized database, creating a report is pretty straightforward. We do it all the time, both for new solutions and as enhancements to existing solutions for our existing clients.

That being said, it’s sometimes challenging for a client to communicate exactly what should be on a report. For that, we have the “5 questions”. These five questions, if clearly answered, will allow us to create just about any report you need, without a lot of back-and-forth emails and extended phone calls.

There are five key questions to answer:

What is this a list of?
What records should show?
What values should show?
How should the records be sorted?
How should the records be grouped or summarized?

Question 1: What is this a list of?

All reports are essentially lists. This first question asks the most basic: what is this a list of? (In database terms, what “table” are we reporting on?)

Although it’s a basic question, and may seem simple, do pause to give this question some thought, because the answer may not be as obvious as it seems. For example, recently a client asked me for a report of loans, showing how much had been accrued in fees during a particular date range. Sounds like a list of loans, right? However, it turned out that for each fee accrued, she wanted to see the date the fee accrued, the type of fee, and the amount. So, in fact, this report is a list of fees, not a list of loans.

That example gives you one way to consider this question, when the answer isn’t quite that obvious. Say this to yourself: “I want to see one row on the page for each ____”

A relational database has a lot of what we call “one to many” relationships. One loan, in the example above, could have many fees. Other examples are where one property has many rentals, or one customer has many invoices, or one invoice has many line items. As users, we tend to focus on the “main” item, like the loan, property, customer or invoice. Our reports, though, are often lists of subordinate items like fees, rentals or line items.

Question 2: Which records should show?

Now that we’ve determined what type of thing we’re making a list of, we need to determine which of those items (“records”) to show. Sometimes, the answer is “all of them”, but more often than not, you only want to see particular items on the report. You can think of this as the report doing a Find for you, and then including only the found set of records.

There are lots of ways to isolate the records you want to include. Sometimes it’s a particular value you’re looking for, like “show me all the Activities with a status of Open”. Other times, you’ll want to incorporate the input of the user, like “Ask the user for a start and end date, and show all the Activities in that date range.” It could also be that you need a combination of user input and standard values. In any case, you’ll want to specify how it is that we will identify the right records for your report.

A quick word about those date ranges. I’m often asked for a report of “last month” or “last year” or “a selected month”. It’s every bit as easy, and sometimes easier, to let the user enter any date range they want. We can make the default date range your most commonly used dates (like “last month”), but we can give the user the chance to change those dates. Sure, you’re only thinking of “last month” now, but if it’s just as easy, why not allow your report to have the flexibility to report on any date range?

Question 3: What values should show?

Now that we’ve identified the right records in the right table, we need to know what you want to show (“fields”) about each record. Name? Date? Account Number? Invoice Number?

Just as question 1 seemed simple but turned out to be (potentially) tricky, this question may sound tricky, but it’s usually the easiest question to answer. Think of it this way: if your report were in a spreadsheet, what would the columns be called?

Of course, even though it’s an easy question, that doesn’t mean that it’s not often forgotten, or that you can skip it.

Question 4: How should the records be sorted?

As you may already know, FileMaker stores records in the order they are entered, but you can sort them pretty much any way you like.

It’s normal to think that your data has some kind of “natural” sort order, and to forget to answer this question. It’s usually not too complicated to answer, but often forgotten.

When you’re thinking about this question, don’t forget that you may want to sort by more than one thing. For example, if you’re asking for a report of Invoices, sorted by Customer, consider how you’d like to sort invoices for the same customer. By invoice date? By due date?

It’s easy enough to sort data by multiple attributes. We can sort ascending, descending or a custom order. For each thing you want to sort by, though, consider whether, if you have more than one row with the same value, the order of those similar items also matters.

Question 5: How should the records be grouped or summarized?

So now we have the right records, from the right table, showing the information we need in the order we want.

Up to this point, we can still think of this report as just a list. We have rows (the records we found), columns (the fields we’re showing) and a sort order.

To make this list into something that we’d think of as a “report”, we’ll want to group the records, and find out something about those groups.

Grouping is where we put a header, and show all the records for that header. Summarizing is where we show totals, averages, counts, and so forth. For example, you might have a report of invoices. You want them sorted by Customer, then by Due date, with a count of the invoices and the total due for each customer. That might look something like this (this isn’t pretty, but just to make the structure clear):

Customer A

1/15/2013

Invoice 1 $100

Invoice 4 $50

1/30/2013

Invoice 7 $20

Invoice 12 $18

Total Customer A ( 4 Invoices): $188

Customer B

1/13/2013

Invoice 15 $150

1/18/2013

Invoice 54 $30

Invoice 68 $25

1/30/2013

Invoice 77 $200

Invoice 102 $108

Total Customer B ( 5 Invoices): $513

Grand Total: $701

The example above is “grouped by” customer and then date, and “summarized by” customer, with a count and a total due.

Also notice that there’s a “grand total” below the last customer. If you want this, don’t forget to mention it! I’d usually include a grand total as a matter of course, but sometimes it’s not obvious whether a grand total is relevant for a given report.

Conclusion

If you answer the five questions above about your desired report, chances are a developer can create that report for you without coming back to you with more questions. If you’re a FileMaker developer creating a report yourself, I’d still recommend pausing for a moment to answer these questions for yourself.

If you use these questions to think about what it is that you’re trying to get onto your report, you’ll make sure you don’t forget anything, or miss erroneous assumptions. Also, you may find that variations on the report are also useful. A good developer can give you those variations, often by asking the users, when they run the report, which variation they’d like. For example, you may want to run that report above by customer then date, but it also may be useful to see it by date then customer. You may want to find those invoices by date range, but it also may be useful to run essentially the same report, but only for invoices in a particular status. While you may start off looking at the report only one way, thinking through these questions may expose opportunities for slicing and dicing your data in ways that you hadn’t thought of, to give you even more useful information.