Creating A Popular FileMaker Crosstab Report

From Dwayne Wright PMP
Certified FileMaker Developer

WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

Please Note: If you are viewing this page in a news feeder, the images may get munged up a bit. For the best experience, please visit the journal directly by clicking (here). 

I do want to stress this isn’t the only way to create such a report but a very popular one and one that is so basic a developer of any level can implement.

One of the most popular crosstab reports is the one that shows accounts receivable broken down into ranges between the current date and the original date due. So we want the report to list each client that has one of more invoices with payment needed and we want to see it categorized by 0-30 days, 30 + days, 60 + days and 90 + days. So you can see the dollar amount for a client across the board.

For this example, I created calculation fields that determine if a value is needed for the 0-30 days, 30 + days, 60 + days and 90 + day options. After that, I create similar summary fields for each. So we have 8 new fields when this is done.

So on the main layout, you can see just the basic invoice fields I need to show of the aging report. You can experiment in changing the invoice date and see the different aging calculations update.

Here you can see the basic layout.

Now we have our report that we run. Any of you that have seen my reports are familiar with the style. This is because I usually copy the script steps from an existing report (a FileMaker Advanced feature), paste it for my new script and tweak it as necessary. So this script ...

- opens up in new window
- finds invoices with a balance due
- runs an error sub routine if no records are found
- goes to the report layout and sorts the records
- adjusts the window to fit the needs of the report

An example file can be downloaded by clicking (here) and there is a related movie on this topic! CLICK HERE!

=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2008 - Dwayne Wright - dwaynewright.com

The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.