Build a monthly summary report in FileMaker

1. Ask one reporting question

Our question is: “How many recorded hours fall in each month?” It is not “How many hours were invoiced?” or “How much revenue did we earn?” Those need different source data. This exercise uses a separate TimeEntries practice table, not an alteration to the Filesoft Time Ledger product.

Download five fictional time entries. The CSV separates year, month and day to avoid ambiguous date text. For the simplest exercise, enter the five dates manually in FileMaker using your normal date format. Create EntryID (Text), WorkDate (Date) and Hours (Number).

2. Calculate the expected answer first

The expected totals are January 2026: 3.5 hours; February 2026: 4 hours; January 2027: 4 hours. The grand total is 11.5 hours. Including the second January catches a common mistake: grouping by the month number alone merges different years.

Use decimal hours consistently. In this example, 1.5 means one and a half hours, not one hour and fifty minutes. If your source records store durations differently, convert them with an explicit rule before comparing totals.

3. Define the month key and summary

Create a calculation field named MonthStart with a Date result. Its expression returns the first day of the record’s month. Use your actual table occurrence name in place of TimeEntries.

/*
	Purpose: Return the first day of the work-date month for report grouping.
	Author: Plato
	Created Date: 2026-10-10
*/

Date ( Month ( TimeEntries::WorkDate ) ; 1 ; Year ( TimeEntries::WorkDate ) )

This minimal teaching expression assumes every WorkDate is valid and non-empty. Exclude or resolve missing dates before reporting. Keep the year in the key; format MonthStart for a readable month and year rather than replacing it with a month-name text field.

Create a Summary field named TotalHours, using Total of Hours. A summary depends on the found set and its layout context; it is not a permanently stored monthly total. Claris explains summary fields.

4. Build the grouped report

1. Create a report layout based on TimeEntries. Put EntryID, WorkDate and Hours in the body so you can audit the detail rows.
2. Add a sub-summary part for records sorted by MonthStart. Place MonthStart and TotalHours in that part.
3. Add a grand summary part containing TotalHours for the full report total.
4. Show all five practice records, sort by MonthStart and then WorkDate, and inspect the report in Preview.

You should see three month groups. If the sub-summary does not appear, check that the sort includes the exact field selected as the break field. Sorting only by WorkDate is not the same report setup.

5. Change the selection and reconcile again

Find only the four entries from 2026, then apply the report sort again. You should now see two groups and a grand total of 7.5 hours. Return to all five records and the grand total should return to 11.5.

This is why a report needs a visible scope, such as “Recorded hours — selected year.” A perfectly calculated total can still answer the wrong question if someone assumes it includes all records when a found set excludes some.

If you see 7.5 hours for January, you probably combined January 2026 and January 2027. If you see 11.5 beside every detail row, check the layout part in which the summary is placed.

6. Keep the detail until you trust the summary

Leave the body rows visible while developing the report. Compare each group with a manual sum, then decide whether the finished report should show detail or only subtotals. Test empty selections and unexpected blank Hours values rather than treating a missing value as a confirmed zero.

Finished result: three distinct monthly groups totaling 11.5 hours, plus a second check of 7.5 hours for 2026. Export a PDF only after those figures agree. A chart can follow later; it should use the same reviewed scope and totals.

References

• Summary fields

Use a practice copy and verify the result in your FileMaker version. Menu wording and platform behavior may differ.