Reporting in FileMaker by month and year with a single field
Reporting by month year in FileMaker – how to make it easy
FileMaker month year reporting in the same sub-summary part is a simple request, except how to make it happen in FileMaker is unclear. Well I am here to share this simple FileMaker trick that delivers this basic FileMaker reporting functionality to both your on-screen and printed reports.
How I discovered that FileMaker month year reporting needs a little ingenuity
Years ago I had a tough boss (I had a few and they are awesome teachers!). This particular tough boss received one of my monthly reports for marketing spend over the past 12 months. In April, the report looked like this:
2017 | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | 2018 | Jan | Feb | Mar
The data crossed years making it confusing to know clearly which year was being looked at. He asked, can’t you get the report to show month and year like this:
Apr 17 | May 17 | Jun 17 | Jul 17 | Aug 17 | Sep 17 | Oct 17 | Nov 17 | Dec 17 | Jan 18 | Feb 18 | Mar 18
A simple and reasonable enough request, except I had two sub-summary parts: one for year and the other for month. I had no idea how to make them a single field so that I could have one sub-summary part that showed both month and year.
A lightbulb moment
I noodled and noodled through the problem, spending a lot of time in the calculation box. I was trying to figure out which combination of things would bring me that magical single field for month and year based on the purchase order date. Finally it dawned on me.
What if I used the Date function to assign all of the purchase order dates to the first of the month like this:
Date ( Month (PO Date) ; 1 ; Year (PO Date) )
Awesome, that worked!
I could now write the report on a single field using only my new Month Year field, placing it on a single sub-summary part when sorted by that same Month Year field.
I ran the report and it looked like this:
4/1/17 | 5/1/17 | 6/1/17 | 7/1/17 | 8/1/17 | 9/1/17 | 10/1/17 | 11/1/17 | 12/1/17 | 1/1/18 | 2/1/18 | 3/1/18
Making it look right
Better, but not looking like a normal report showing the month name and year.
I moved on to more fiddling around with the date formatting options for the field in layout mode. Finally I settled on the answer – a custom format to show the month name fully and the 4-digit year.
This is how I formatted the date field to display as Month and Year (ex. April 2018) and you can to:
- In the Data section of the Inspector (top right) go to the Data formatting section
- Choose Custom from the Format options
- In the first drop-down select the full name for the month, ex. December,
- Click the separator drop-down to the right and choose Space
- In the next drop-down select the 4 digit year option
- For the remaining drop-downs choose None
The month and year report was looking just like my tough boss, and now I, expected it to:
April 2017 | May 2017 | June 2017 | July 2017 | August 2017 | September 2017 | October 2017 | November 2017 | December 2017 | January 2018 | February 2018 | March 2018
Sometimes even a simple thing takes a little effort
FileMaker is SO easy for so many things.
- It is the #1 Rapid Application Development platform (source: G2 Crowd)
- You can create functioning applications with no programming skills
- It can take 30% less time to create applications in FileMaker than in MySQL (source: Reddit)
Even with all this power and ease, every once in a while you get a gotcha. With a little determination and experimentation, I’ve found in every case that those gotcha’s were overcome — as in this example of reporting the month year using a single field and sub-summary part.
Just add pluck
Being a beginner FileMaker developer can have its frustrating moments. As an experienced developer of many years I’m hear to remind you — one of the reasons we all love working with FileMaker is that with no coding experience and a little pluck and ingenuity we can get just the results we want out of our databases.
Too busy to build a month and year report yourself?
If writing a month year report in your FileMaker database sounds great and you just don’t have the time, no problem. Take the first step. Request a free, no obligation call with me. I’ll advise you on the best way to add this report to your own database.
ALLISON, Partner & Software Development Director
I am here to make your work clear and predictable. When your most complex processes are elegantly handled by the push of a button your mind is free to do more of what you excel at. When your custom software application is well constructed, it handles the heavy lifting in the background so the goal remains clear and in focus. Your own unique work style is now the norm. Your custom software is an extension of you, a faithful, smart and powerful application that's easy and open to evolve if/when the time comes.