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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Will LovingPresident, Dedication Technologies, Inc.Commented:
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
Will LovingPresident, Dedication Technologies, Inc.Commented:
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.
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

murphyz1Author Commented:
Will give it a bash and let you know
Will LovingPresident, Dedication Technologies, Inc.Commented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
murphyz1Author Commented:
Thanks so much. All working now!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FileMaker Pro

From novice to tech pro — start learning today.