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

[Bob Stuart]

Mikhail, your Cross-tab reports technique is a great example of clear-thinking in an aspect of FileMaker that is very confusing to many users. If you were to write a book called something like: "FileMaker Reports - How to display data effectively" I'd bark my knuckles reaching for the credit card!

If you included techniques to create Flash graphics and view them using the WebViewer, you'd have a real winner!

Thanks for the inspiration over the last few months, Mikhail.

[m.edoshin]

Thank you, Bob! I'll think about your idea of a book while getting myself trained on the blog ;) Stay tuned and, of course, Merry Christmas and Happy New Year!

[Greg Concepcion]

Mikhail,
I've been struggling to view my data in a nice summarized fashion for sometime now and you've finally provided me the relief for many hours of frustration!!!
Thanks so much for the tutorial!

[Scott D Bradley]

Excellent, I get it. How would you make such a report available via IWP, where you don't have Preview mode?

[m.edoshin]

I think I sort of have an idea: you can write a custom function to gather summaries over a found set using the Fast Summaries technique. I need to run some more tests, e.g. check if the limit of 50 records would affect it and so on, but if you need to solve any immediate problem, email me and I'll send you what I have now. If not, wait :)

[Dan Wilson]

Mikhail! Thanks so much for this great post! I've been looking for a simple crosstab capability for FileMaker for YEARS!!! I've already rolled into it into my little FileMaker file that tracks our monthly expenses and it works great!

[John Wright]

A very elegant solution. I wonder is it possible to use this solution to cross over years, say from 2006-2008? or is that too complicated? For one year's worth it is a wonderful way to present expenditure profiles, well done!

[m.edoshin]

Yes, it should be possible, but you need to make the calculation based on some global start value. Such a calculation will be unstored, of course, so it may slow things down.

[Kurt Thomas]

I am trying to get this to work by having a Sub-summary CATEGORY (leading) with the SUB-CATEGORIES as the horizontal headings and then the amount. Basically the same concept as this example - just using the subcategory field instead of the date field. I can not get this to work using the following calculation - Case( Get( CalculationRepetitionNumber ) = Extend( Subcategory ); Extend ( Amount ) )

Any suggestions on what I am doing wrong? I have successfully duplicated this example, I just can not get into work when I try to change the hoizontal headings from date to subcategory.

[Alexandre Villares]

Hi, Thanks Mikhail for the great article!

I'm using FM8 and would like export these cross-tab reports to Excel.
As I tried copy&paste from an exported PDF, one of my problems would be that the reports won't show a 0 (zero) when no amount is present in a certain category on a certain month. Any ideas?

Also, how could I integrate on the same report data from different table/files on the same by month/by category structure?

thanks in advance,
Alexandre

[Manny]

can the same crosstab report be created in FP6? I'm stuck using that version in windows at work. Any help would be appreciated.
Thanks
Manny

[m.edoshin]

Hi Manny,

It's possible to create such a report, but not exactly in this manner. The problem is that FileMaker 6 doesn't allow to summarize individual repetitions of a repeating field. So you'll have to create as many calculated fields as you have columns and make each of them read, say:

January Sales =
Case( Month( Date ) = 1, Sales )

Then you'll need to make a summary field for each of these fields to have column totals. For row totals you can use aggregate functions with a list of the fields, e.g.

Sum( January Sales, February Sales, March Sales, ... )

Hope this helps.

[Peter Mitchell]

Mikhail

Its fantastic but if you have time a quick question. If you already have the month extracted how can you use that instead of extending it from a date field? I have a situation where have a transaction date and a performance date. The latter is entered as a month and year I am trying to migrate from a system I have developed in access and cross tabs have been a problem for me.

Mitch

[SUYONO]

HI MIKHAIL,
THANKS FOR YOUR GREAT TIPS ABOUT CROSS TAB REPORTING, BUT WHEN I TRY WITH
RELATIONSHIP TABLE, IT VERY SLOW. I ALSO DOWNLOAD SAMPLE FILE THAT YOU CREATED (BLAZINGLY FAST SUMMARIES IN FILEMAKER)FROM
http://www.onegasoft.com/ AND IMPLEMENTED TIPS FROM YOU. WOW IT'S MORE INCREDIBLE FAST. BUT GET SUMMARY () FUNCTION CAN'T GET DATA FROM RELATIONSHIP TABLE.
WOULD YOU TELL ME HOW ABOUT GET/STORE DATA FROM RELATIONSHIP TABLE (CHILD TABLE FROM PARENT TABLE) WITH ONE TO MANY RELATIONSHIP TO SAVE LOCALLY AND SUMMARY LATER. THANKS FOR YOUR TIPS MIKHAIL, GOD BLESS YOU

[m.edoshin]

Hi Suyono,

Sorry for the delay. Yes, the GetSummary() function is know for that: it doesn't work with related fields. The common workaround is to make a local calculated field that fetches the related data and use it for all sorting and subsummary parts. Then you'll be able to use the GetSummary() to read this field's value.

Another note: some FileMaker summary operations have a setting that breaks them down by some field and that field can come from a related table. I haven't worked with these options much, so I cannot say if they're usable here.

[myusaul]

Hello Mikhail

On filemaker, cross tab reporting that get data from Parent Table(unstored) is very slow,
i had tried make local calculated field to get data from Parent Table, example : date field through Autoenter Calculation or Lookup, But it was failed because when i try to change Date Field on Parent Table, Local Calculation was i made in child table not changed.

I Had tried Replace script to replace Date field on Child table to replaced with Date field in Parent Table, And it worked. Usually i included Replace Command in Add New Record.

Mikhail, Would you tell me anyway to make a local calculated field that fetches the related data into Child Table.

Many Thanks

[m.edoshin]

Hi myusaul,

There's no such a way, I'm afraid. That is you either copy data with some script and have them stored and therefore fast, or you use a calculated field that does not need to be copied, but is slower.

However, FileMaker normally should not be too slow even with unstored fields, unless the data set is really large or the calculation is cumbersome and therefore slow. I.e. if it just points to related “Parent::Date” it should be slower than if it were in this table, but still usable. Do your calculation look as if they can be optimized somehow?

[myusaul]

thanks mikhail,
Yes i actually copy field i choosed from parent table to child table used script per one id,it's fast.

usually i included copy data through replace command script with add/new record.

this script look like this :
Allow user abort (off)
Set error capture (on)
set variable [$get_id; value:parenttable::pkid
New Window[]
Go to layout ["diagosis"(diagnosis)]--> child table
Perform Find [Restore]--> Find $get_id
If[Get(FoundCount)=0]
Go to layout--> next child table
Perform Find [Restore]--> Find $get_id
Else If [Get(FoundCount)>0]
Replace Field COntents[No Dialog; child table::date; parent table::date
and others
End If
Else If [Get(FoundCount)>0]
Replace Field COntents [No Dialog; diagnosis::date; parent table::date
...
...
close window

That's was my script mikhail.

i had record over 150.000,and we need cross tab report faster, reason why i choosed your way in onegasoft was we can view report in browse mode before print into previev mode, so why we need to export field in parent table into child table.

previous database system that we used before that create that records.

please your suggest mikhail.

oh ya we worked on goverment's healthcare environment in developing country. we choosed filemaker because we can develop our system byself

many thanks.
myusaul

[m.edoshin]

Hi myusaul,

As far as I understand, you use not the technique described on this page, but my older cross-tab reports based on so-called “Fast summaries”, right? And the reason is that you need to view the reports on screen without switching to preview mode. Is this correct?

If yes, then I have questions:

- Can you use FileMaker 10? It has a new feature that allows you to view summary parts on screen. If yes, can you try to use this new technique instead of the older one. The old one is just too complex to work comfortably with.
- If not, what version do you use? Can you describe the data model in more details? It's Ok to send me a sample, if possible; my address is m.edoshin at mac.com.