Filemaker Pro 12 Monthly Reports

Hi Guys

I have a purchase ordering DB which captures flights, accommodation and vehicle hire details and costs. I would like to generate monthly reports by month and department. Please assist.

Who is Participating?
Will LovingConnect With a Mentor PresidentCommented:
Note that the field your sort by doesn't actually have to BE in the Sub-Summary part, though in this case you do want that, you just have to have the records sorted by the field associated with the Sub-summary part in order for the breaks to occur in the correct place. And if you are sorting my multiple fields, then the Sort Order of the fields makes a difference. If I were you I would probably sort by Date first and then MonthName second.
Will LovingPresidentCommented:
In general, the report would be created in the table that has the individual line items, assuming they are all in one table. If you have them in different tables then you'll either need to make separate reports, a more complex report operation or layout, or restructure your database.

For this report you'll need to create both a new Layout and a script that will find and sort the records so you don't have to do it manually each time.

For the report, create a new Layout based on the table containing the individual line items. In order to summarize by Department and Month, you'll need two "Sub-summary Parts", one each for Department and Month. When you add a sub-summary part, FileMaker will ask you what field to summarize by. Which you put first is up to you in terms of whether you want it sorted by Department first and then month within Dept, or Month first and then department within in each month.

Unless you want to print the full list all transactions, you can delete the "Body" part that is included by default on the layout. Keep it if you want to see the full list of transactions below each sub-summary part.

You'll need to create some extra fields. To display the Month, you'll need a calculation field that uses MonthName( someDateField ). You'll also need to create Summary Type fields for each field that you want to have summarized - usually using the Total or Count functions in the Summary field dialog. Note, this is different from using the GetSummary() calculation function - avoid that for now until you understand how regular Summary fields work. The summary fields get put into the Sub-Summary parts and will display Totals or Counts for each Month or Department summarized. They can also be put into a Grand Summary to summarize the entire report.

In order for the summaries to work, you must find the group of records you want, presumably by date range, and then sort them. In order to sort by Month,  you'll need to create a Value List of Months with the name of each month in order and then assign that value list in the Sort Dialog for the MonthName field you created above. If you want the Departments in a particular order other than alphabetical, you'll have to use a Custom Value List for that sort as well.

You can do the find and sort manually, but if you want to automate it you'll need to create a script to run. Usually the script will look something like this:

New Window
Go to Layout [ "My Summary Report" ]
Find Records [ <some save Find criteria> ]
Sort Records [ "Month" ; "Department" ]
Page Setup [ Restore ]
Enter Preview [ ]
Pause [ ]
Print [ Restore ]
Close Window [  ]
murphyz1Author Commented:
Hi there

I seem to be getting there slowly but surely but for some reason I have two separate month's entries under one month. see Attached. Could you please explain in more detail if it's not too much trouble.

Thanks so much
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Will LovingPresidentCommented:
My guess is that it's not sorted right or the sort order is not specified correctly.

In order for it to sort correctly, it must be sorted by the same field that is specified in the Sub-Summary part. Assuming all records are within the same year and you don't have values from multiple years, you need to sort by the MonthName field that you created, making sure to specify a the Custom Value list of Month names so they sort in the correct order. You probably also want to sort, secondarily by Date so that the individual listings are in date order.
murphyz1Author Commented:
Will give it a bash and let you know
murphyz1Author Commented:
Thanks so much. All working now!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.