Cross-tab reports made easy

Here's a simple and powerful method to create cross-tab reports in FileMaker.

Hands-on: get an overview of home expenses

Assume we scrupulously record our home expenses and want to get a big picture from these bits. Here's the the sample data:

and we'd like to have an overview by category and month, like this:

This report shows how many we spend in each of the categories in any of the months (A), view average monthly expenses for this category (B) and total amount spent (C). We can also get month totals (D), average month total (E) and grand total so far (F). We're going to print this report monthly; in January it will have only one column, in February two and so on until we get a complete twelve-column report in December. Such a report is going to be very useful, giving us an overview of the whole year at a glance.

Let's prepare the layout. We need header, subsummary by Category and a grand summary.

We add the labels and the Category field.

Now we define the fields that will make the report. We need only a few of them.

The first field Months is a repeating calculation:

As you see it simply places the amount into the appropriate repetition. This is the key idea, actually. The next field is called Month Totals and it's a summary of Months that summarizes its repetitions separately.

Month Totals makes the biggest A and D parts of the report. We can place the field in both subsummary and grand summary parts, sort the records by Category and preview a nicely looking cross-tab report already.

But our report is not complete yet. To calculate average and total by category we need to use the summary data in calculations, i.e. to somehow extract them from our summary field. To do this for categories we add a calculation field Month Totals by Category:

As you see it's also a repeating field with 12 repetitions. It gives us a snapshot of Month Totals for every category and since it's a plain calculation field we can use it in other formulas. This is the field we'll calculate averages and total from. We use two more fields for this:

These two fields make the B and C parts respectively. We can place them on the layout and see the result:

To make the E and F parts we do exactly the same thing but for grand total: we capture it into a calculation field.

As you see it differs from the Month Totals by Category field by the second parameter, the break field. When we use the summary field here, it means “no break field at all, give me the grand summary”.

Then we calculate the average and grand total:

Note: since the F part is simply a grand total, we could make it with a simple summary:

But I decided to repeat the idea of repeating fields for the sake of consistence.

Here's our final report:

See? It took only eight very simple fields to create a cross-tab report. This is a very manageable and scalable approach.

Scalability test

For example, assume we categorize the expenses further and add subcategories:

And we want to have a subsummary by category and by subcategory.

To do this, we need to add three fields: one field to capture the summary by subcategory:

And two more fields to calculate average and total (H and I parts; the G part uses the same Month Totals.)

Here's the report with subcategories:

Ideas

With certain additional effort you can make dynamic reports, i.e. don't start with January all the time, but display whatever months you have in your found set. Hint: use the repeating fields that capture the summary data in the parts A, G, and D and try to make them to display only the relevant columns.

Remember that you can create more than one field and summarize by more than one criteria in the same report. E.g.

As you go, you'll see that the approach is simple, flexible, and scalable.

---

Don't forget to get the sample file :) Next post (Dec 15, 2006): How bypass the standard IWP authentication page.

Comments

[Heather Davis]

Hi Mikhail,
I've just been working with FMP for a couple of weeks so I don't know the vocabulary, yet. I need to make a cross tab report with 3 levels of sub-summaries. This report is for a production schedule for a fashion designer. The orders are entered into Order_lines, which is the data that needs to be made into a production schedule. The 3 levels are: Style_ID, Ship_Date and Customer. Across the top, I'll have the sizes with the total quantities per size the bottom. I don't need averages, but will total the quantities for all sizes per Customer (I) and total quantities per Ship Date (C)and grand total of the Style for all customers, ship dates and sizes (H).

Style: XYZ Dress (Style_ID)
Ship Date: 7/1/10 (text)
Customer: (text)
Size: drop down value list (XS,S,M,L,XL)
Quantity: (number)

This production schedule can be a separate report for each style (multiple delivery dates, customers, sizes and quantities), or all styles on one report.

Can I run this cross tab report with Style & Ship Date combined as the Category and then sub-summary the Customers?

As soon as I'm confident that this will work for my situation, I'll set it up and work out the kinks.

Thanks for your help!
Heather

[Heather Davis]

Ooops, The grand total is (F) not (H).

[Heather Davis]

Also, your calculation for the Month field is:
=case(Get(CalculationRepetitionNumber)=Month(Extend(Date));Extend(Amount))

What would be my calculation to show the Sizes (XS, S, M, L, XL) instead of months? My field is called Size.

Many thanks,
Heather

[Heather Davis]

Also, your calculation for the Month field is:
=case(Get(CalculationRepetitionNumber)=Month(Extend(Date));Extend(Amount))

What would be my calculation to show the Sizes (XS, S, M, L, XL) instead of months? My field is called Size.

Many thanks,
Heather

[m.edoshin]

Ok, I'll try to assemble a sample and post. Stay tuned.

[Kelly]

Your instructions on Crosstab reports has saved me! I am a new user of FM 9 but used to do my report in Access. I had no clue how to begin putting it together in FM. I am stumped, though. While I have all of my subcategories working well, I cannot figure out how to get my "categories" listed. My categories are also calculations, not just a label. I can send you a pared down sample of what I am trying to do.

Any help you can provide is GREATLY appreciated!

[m.edoshin]

There must be little difference, actually; please send me your sample to m.edoshin -you-know-what- mac.com

[Dchigg01]

Hi Mikhail,

I was directed to your article when I added a post in the FM Forum about creating something that resembles your cross-tab report. First, I want to thank you for the detail you included in your blog. The effort is much appreciated!

Unfortunately, it seems that my data is a little different. The table I'm pulling from, Net Worth Statement, contains calculation fields that total certain categories (assets and liabilities). Right now there are only 24 records in the table...one for mid-month and one for month-end for each month. The months of Sep/Oct/Nov/Dec only contain the date at the moment, but the other months are populated.

What I'm trying to do is create a Balance Sheet Summary report for the current year where just the month-end records are displayed on the report for each month. I have tried a single row portal where I select the specific record for just one row and it won't display.

Anyway, my post is here:

http://fmforums.com/forum/showtopic.php?fid/28/tid/216540/pid/364912/post/364912/#364912

Should you find yourself with some time to review the details, I would appreciate any input you can provide.

[Dchigg01]

Hi again Mikhail...I just wanted you to know that I fixed my problem so there's no need to review my FM Forum post.

Keep up the great work!

[Mikhail Edoshin]

Hi Dchigg01 :) Yep, I was somewhat slow to answer. Glad to hear your report is OK :)

[Dchigg01]

It's no problem, Mikhail. I'm sure you're quite busy.

I am looking deeper into your cross-tabs report now, though, because I have a need to summarize data for a year as you do in your sample data. I'm trying to understand everything, but I'm a novice, really, and I have trouble correlating your sample data to my actual data.

My financial document is quite complicated. It's a budget vs. actual report with three columns per month (budget, actuals, over/under). Then I also have a YTD (year-to-date) total for all three (budget, actuals, over/under) for each row, and the annual totals at the far right. That way I have a picture of what the budget is YTD compared to the actuals YTD, along with the resulting over/under figure. The annual totals just are what they are and really only give you a good picture when the year is complete, which is why the YTD columns exist.

As I look at your sample data and I think about what I have done with my database thus far, I feel pains of my inexperience and lack of knowledge. I know I'm creating a monster that could be done far more eloquently if I just knew what I was doing. All in good time, I guess.

Anyway, I'm usually pretty good at reverse engineering things, but this stuff in FileMaker tends to tax my brain. For instance...where you have "category" as a field, I have hard-coded that information on the report. Only the numbers change each month, but there are nuances about it that stump me, like when a category goes away in a given month. Once hard coded, you can't just take it away or it goes away from every month.

There are times when I wonder if FileMaker was the best answer for this project, but I love the program so much, I want it to work. Right now everything is in Excel and there's just so much room for error, and that's what I'm trying to correct by putting everything in a FileMaker database.

Well, enough of my tangent. My question has to do with getting the YTD numbers calculated. If you were to expand on your sample data, how would you do this? I mean, instead of Avg, I need a YTD calculation.

If you can find the time to respond, that would be great. I'll check back every so often to see if you have posted a response.

Thank you so much!

Debbie

[Mikhail Edoshin]

Hi Debbie,

I need to re-read this and think a bit :) It's possible to make such reports in FileMaker but they are indeed monstrous. BTW, I'd suggest you to send me an email to m.edoshin - (at) - mac.com, so I can reply via email too; this will be more convenient.

Mikhail

[Edward Souza]

Dear Mikhail,

It is needless to say your posts are true examples of elegant and sophisticated coding based on simplicity.
Simplicity can only be reached through deep knowledge and lots of practice; however, when you get all that and give it back to people, such attitude makes you unquestionably above average.

Thanks for giving and sharing; may you receive a thousand times that in return.

[Dchigg01]

Amen, Edward! I wholly agree with your sentiments. My thanks as well, Mikhail!

[Mikhail Edoshin]

You've made me feeling guilty of not writing more often :)

[George Sova]

Mikhail,
I am very much interested in your Cross Tabs reports technique. But I could not find any formulas or layouts on this screen. Is it something you publish somewhere else? Is it for sale? I am very much interested to learn about your technique.

[Mikhail Edoshin]

Hi George.

There was a problem with links; I fixed it, so the example file is now available (the first link in the post).

Kind regards,
Mikhail