What you have described is a "cross tab" or "pivot table" report. These are possible in FileMaker, but take a bit of work to set up as there isn't a built in tool for generating the report (or at least the underlying query) found in some other database systems.
From what I see here, it appears that you have this relationship:
Personnel----<Coaching ( ---< means "one to many")
Personnel::Serial = Coaching::ReferencedEmployee
There is more than one way to set this up. The simplest to describe in a forum like this is probably to use filtered one row portals and summary fields.
1) Define a summary field in coaching, sRecordCount as the "count of" ReferencedEmployee (any field that is never empty works)
Define cFiscalPeriod as a calculation field that returns the correct matching text for fiscal period from your date field. (I don't know your fiscal year and am not sure what "09" means in your example, the 9th month of the fiscal year or ?? ) It needs to return exactly the same text as that shown in your example.
2) Define a global field, gFIscalPeriod, in one of your tables. (Since it will have global storage specified, it can be defined in any table in your file and it will still work. I'll put it in Personnel for this example.)
3) Set up a list view layout based on Personnel. In the body layout part, put CompleteName on the layout as your first column of data. Add a one row portal to Coaching as your 2nd column. Specify a 1 row portal and give it this portal filter expression:
Coaching::EntryType = "One-on-One" And Personnel::gFiscalPeriod = Coaching::cFiscalPeriod
Put sRecordCount into the portal row.
4) Once you have your first portal correctly sized and set up to look like you want and you see the correct total in it, return to layout mode and make copies of it. (option or ctrl drag or copy/paste). Open Portal set up and change the portal filter expression to refer to a different entry type value.. Repeat this until you have the additional columns that you need.
5) Add a new relationship to Manage | Database using a new table occurrence of Coaching:
Personnel::anyfield X Coaching|All::anyField (Double click the relationship line in order to change = to X.)
In this relationship, you can literally use any field in the table as a match field on both sides of this relationship. Add one more set of single row portals to a trailing grand summary or the footer that show related records from Coaching|All and that use same summary field, but from coaching|all. Keep the same portal filters as before. These will give you your column totals.
Note that it's also possible to produce this report using a series of calculation fields, each with a different SQL query as a parameter of the ExecuteSQL function. (Requires FMP 12 or later.)
Thank you for the direction, Phil! At first glance, I'm not sure this will work, as I am already using a portal on form view layout. The actual layout I'm working with is here:
What I have done is create a table called ProfileAndSettings. This table houses all of the information for the user of the database, as well as their preferences. All tables in the solution have a global field called magicKey, with a value of 1. All tables are related using this field.
My layouts are based on ProfileAndSettings, and use portals to pull related information in from various other tables, including Personnel and Logs. The purpose of this is to enable the menubar seen on the left, and to enable a consistent one-window experience. My intention is to have any secondary windows open strictly for creation of new records.
I can use the method you describe above to accomplish generating the report I'm looking for, however it would require a new window be opened (unless my thinking is off?). Do you have any other ideas, or is my best bet to suck it up and make this report display in a separate window?
You would need to use a layout that does not use a portal like that. Set up a list view layout and put the one row portals for your columns of data inside the body layout part.