Fiscal Year Invoice Totals In A FileMaker Crosstab Report

From Dwayne Wright - Certified FileMaker Developer
TWITTER: dwaynewright

I have a large number of reports that I want to add to my InBizness CRM framework product. That seems to work out well from a productivity standpoint with the blog that I have called FileMaker Reporting Explored. I am purposely trying to create crossfire efforts as I populate content in both areas.

So with that in mind, back on August 26, 2008, I created a blog posting for a cross tab example report for customer aging. A crosstab report is a report that summarizes values in both rows and columns. A week later, I did a report called Invoice Sales By Fiscal Year Quarter. So now it is time to take the next obvious step and blend the two to get a Cross Tab Fiscal Year Report.

Now I used a custom function to get my fiscal report quarter information data. Here is a copy of that calculation FYQ ( invoice_m::g_startmonthFiscalYear ; INVOICE::date_ordered ). The g_startmonthFiscalYear indicates what month of the year the fiscal year starts. In my InBizness solution, the g_startmonthFiscalYear field can be found in a preferences tab area from the main screen.

So when the calculation kicks in, you get data strings such as 4Q08, 3Q08 and 1Q07. The breakdown is the first two characters are the calculated quarter for the even and the last two characters are the year. In a crosstab report, I will need to calculate each quarter separately. So I will need to read just the first two characters of that result to get the quarter information.

I will need 4 calculation fields and 4 summary fields for those calculations. That gives me one summary total for each possible quarter. The calculation for the first quarter is Case(Left(FiscalYearQuarterSold; 2) = "1Q"; c_grand_total; ""). The c_grand_total field is the total of the invoice including line items, shipping and tax charges.

Here you can see some of the new fields I added to create this report.

Again, there are a number of ways to create a crosstab report and the method I'm using is the least intense from a programming aspect but does create more code overall (particularly more fields in the implementation).

After that, it is just a matter of implementing the steps I’ve outlined before ...

- find a layout that closely resembles my needs
- duplicate that layout and rewire it for my report
- move the new report to the area in belongs in the overall layout list
- find a script or set of script steps that closely resembles my needs
- duplicate that coding and rewire it for my report
- add the additional report to my array of custom menus
- test the new report for quality assurance needs
- document the new report feature

Here you can see the original report that show fiscal year data in the traditional way.

Here you can see the new crosstab report that shows the same information but in a more concise way.
More info about the author and FileMaker in general, contact me at

© 2008 - Dwayne Wright -

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