1 Reply Latest reply on May 7, 2009 11:02 PM by comment_1

    Report Summary by Year

    Birdys

      Title

      Report Summary by Year

      Post

      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! 

        • 1. Re: Report Summary by Year
          comment_1
            

          Filemaker doesn't do cross-tab reports, at least not natively. In general, Filemaker is very rigid in the horizontal direction (it requires a fixed number of columns), and flexible in the vertical (you can have any number of rows).

           

          There are a few fairly advanced techniques to simulate a cross-tab report. One would be to define a series of relationships (one for each year in your example) to filter the data for each column. Another one is described here:
          http://edoshin.skeletonkey.com/2006/12/crosstab_report.html

          Note that neither one is perfect in the sense that the number of columns needs to be fixed in advance, with no automatic adjustment for the actual number of categories in the data.

           

           

          The "expected" form of your report in Filemaker would be either:

           

          Item A

          • 2006: 100

          • 2007: 200

          • 2008: 300

          Total A: 600

           

          Item B

          • 2007: 150

          • 2008: 250

          Total B: 400

          -----------------

          Grand Total: 1,000

           

           

          or:

           

           

          2006

          • Item A: 100

          Total 2006: 100

           

          2007

          • Item A: 200

          • Item B: 150

          Total 2007: 350

           

          2008

          • Item A: 300

          • Item B: 250

          Total 2008: 550

          -----------------

          Grand Total: 1,000