This is amazingly similar in structure to the first cross tab report that I ever created in FileMaker (Did it in version 3, no less).
In my case, it was a 5 year monthly comparison grid where each cell reported both the total and daily average figures for a type of material purchased from the general public by a scrap metal dealer. You selected a material (or material category) from one value list and specified the most recent year in another field to then see the data update into the cross tab grid.
Start by defining a table with one record for each month of the year. This table stores no data except the month and year match fields and only has 12 records, with one global field, gYear5 for the product and one for the most recent year. 4 unstored calculation fields then compute their values from from this global field, cYear4 was defined as gYear5 -1, cYear3 subtracts 2 an so forth...
A number field, month, stored the month numbers 1...12. A cacluation field computes a date as Date ( month, 1 , gYear5 ). Put this field in the body of a list view report and format it to only show the month name and you get the first column of your data.
You'd also have 3 global fields for product, metric and Tier. Set up a calculation field named cTierKey that returns the value of gTier unless the selected value in the field is "All", then it uses the list function to return a list of all Tiers.
Set up this relationship:
CrossTabTable::gProduct = ORGs::ProductName AND
CrossTabTable::cTierKey = ORGs::PriceTier
In ORGs, add a Summary field to compute the total of AnnualContractValue$
Now set up your list view layout with a narrow body layout part with your calculated/formatted date field for column 1. For Column two add a one row portal based on ORGs. Define the following portal filter:
CrossTab::cYear1 = Case ( gMetric = "Free Trial" ; Year ( Orgs::Date_FreeTrial );
gMetric = "New Orders" ; Year ( Date_NewOrder ) ;
gMetric = "Cancelations" ; Year ( Date_Canceled )
) // case
CrossTab::Month = Case ( gMetric = "Free Trial" ; Month ( Orgs::Date_FreeTrial );
gMetric = "New Orders" ; Month ( Date_NewOrder ) ;
gMetric = "Cancelations" ; Month ( Date_Canceled )
) // case
Place your summary field inside the row of this single row portal. Make 4 more copies of this portal, but change the first field reference from cYear1, to cYear2....gYear5 for each additional portal.
This does not provide all that you've asked for, but it's a start.
First - I'm impressed you can remember that far back and in such detail!
I'm going to study it tomorrow morning over a cup of coffee.
Thank you very, very much for contributing your time and expertise to this forum. You really are a tremendously generous resource for the FileMaker community.
The methods that I shared here wouldn't have worked back then. I had to set up 5 different table occurrences instead of using a filtered portal and I may have originally used calculation fields instead of portals--though unfiltered portals could have been used back then.
This was a layout that I was still responsible for updating and maintaining up to last Fall when I was hired away from my old company. I'm still responsible for it as an independent consultant for that matter...
I studied your instructions and sent PM with questions to you this morning.
Please post them here. I make it a policy to not answer tech questions via forum private message or email.
Those systems would be overloaded if I allowed that and I prefer to keep the discussions public so that others can contribute to and learn from any such discussion.
The attached summarizes my understanding of your instructions the other day about creating a cross tab report (similar to your scrap metal application). I think I understand everything except the areas highlighted in the attached summary.
Off Topic ... Is there a wayu to attach pdf files to posts in this forum?
Thanks for any and all help.
You are correct. You assign a value to this global field and then the other years counting backwards calculate the years for the other portals/columns of data.