Report Summary by Year
I am new to FMP and having difficulty finding instructions on how to create a layout that summarizes amounts by item (vertically) and by year (horizontally). I have tried creating different tables for each year, and have been able to retrieve the year summaries for each item but I cannot get the Grand Total function to work.
For example, my database has Song Titles and revenue earned during each year over various transactions. To test my layouts, I've uploaded revenue from 2009 into one table and revenue from 2003 into another table. The 2009 table was the first one created in my database and therefore is my default for all layouts. Within the 2009 table, I created a calculated field called "Total 2003" that pulls revenue amounts from my 2003 table. I've included this, (along with my Title field) in the subsummary part. This works fine. However, I cannot create a Grand Total for the year 2003. I tried to mimic other templates and have created a summary field called "Summary of 2003" which is a summary field that is equal to the total of "Total of 2003". The field "Summary of 2003" is included in the Total part of the layout. The result is incorrect. The grand total for the year 2009 is correct.
What do I need to change in order to report an accurate subtotal from all "year" tables?
Is there a different way to accomplish this that does not require setting up tables for each year?
I've already looked at the other postings in this forum that refer to monthly reporting and none of those appear to address the issues I'm having. I'm currently using FileMaker Pro 10, so I don't know if the previous postings were specific to earlier versions.
Thanks in advance for any assistance!