How do I calculate quarterly totals

[Carol4kids]

I have a database with utility bills for our school district. There are multiple meters for each school campus, and 22 schools. I have bills for each meter for each school for each month for the past 21 months. I'd like to calculate quarterly totals for the schools sites (including all their meters), and then compare the quarters year-to-year to see if each school is increasing usage/charges (i.e. first quarter last year compared to first quarter this year). All the data is there - I just can't figure out how to get the quarterly totals across multple years, or how to do the comparisons. This is my first FMP database, so "beginner" may be an understatement!

[Ender]

There are a couple methods for summarizing data. You could use a columnar layout with sub-summary parts and summary fields, or use relationships and sum() calcs. Both methods will require a Quarter field and a Year field in the Bills table. These are pretty easy to set up:

Quarter (calculation, number result) = div(month(BillDate)-1;3) + 1

Year (calculation, number result) = year(BillDate)

Alternatively, the relationship method could be done by setting up a multi-criteria relationship from a couple global date fields, but I'll skip this for now.

A. Sub-Summary Report

Add a summary field to the Bill table for each of the things you want to total. Ssomething like:

Total of Usage (summary) = Total of Bill Usage

Total of Charges (summary) = Total of Bill Amount

Add a columnar report layout to the Bills table. Add a Sub-Summary by Year part and below that a Sub-Summary by Quarter part (the basics of how this is done can be found in the FileMaker PDF manual.) Insert the Year field on the Sub-Summary by Year part and those summary fields if you wish to see a summary by year, and insert the Quarter field on the Sub-Summary by Quarter part, along with those summary fields again. The Body part can be left in if you wish to see a line for each Bill on the report (add any necessary Bill fields to the part,) or it can be removed if you only wish to see the totals by Year and Quarter.

Now the thing with sub-summary reports is they only show up when you sort by the part's chosen fields and then Preview or Print the layout. So you would need to sort by Year and Quarter, and then Preview or Print. This can all be scripted, of course.

B. Relationships

We should first pick a good table to use as the location where the totals will be viewed from. Since you want to be able to compare totals from different quarters, I'd recommend we use the Quarter table, where each record is about one quarter in one year. If you don't have a Quarter table, you can add it for the purposes of this report.

So the Quarter table will have these fields:

Quarter (number)

Year (number)

SchoolID (number)

Now we add a relationship between Quarter and Bill:

Quarter <=> Bill

Quarter::Year = Bill::Year

Quarter::Quarter = Bill::Quarter

Quarter::SchoolID = Bill::SchoolID

With this relationship in place, we can now add a couple sum() calcs to the Quarter table to total the related Bill values:

Total Usage (calculation, number result) = Sum(Bill::Bill Usage)

Total Charges (calculation, number result) = Sum(Bill::Bill Amount)

Totals for a particular Year-Quarter-School can be seen by adding those Total fields on a layout based on the Quarter table (and populate this table with all the Year-Quarter-School triples.) But for comparisons, you might use one more layer of abstraction, showing those totals in a Quarter portal from a School layout (using a relationship between School and Quarter.)

So basically, there's more than one way to do this, but the choice of which to use depends on how you want to see the data.

[FMP_Trainee]

Ender,

that's a much appreciated extensive answer...

I was searching for FileMaker Reporting features and implementations on the web.

Yours is quite cool actuallly, simple, precise. The kind of thing I was looking for.

Any clue where it's possible to find exactly that kind of reports explained...? Except a training from you I guess, Minneapolis being a little far from my place...

Anyway, many thanks, I will try it right away.

Rgds,

Julien

Edited September 28, 2005 by Guest

[Ender]

Julien, there are many demo files in this and other forum web sites that show some handy ways to summarize data. The trick is finding them. There may be relevant example files in the Solution Library, or maybe in a forum that covers Layouts and Reports (I haven't looked.)

[Carol4kids]

Thank you so much for your generous answer. I'm going to sit down with it now and work through your instructions. I am truly grateful you have taken the time to give me this level of detail, to help me understand. You are my hero today!

Carol