2 Replies Latest reply on Jun 27, 2010 7:22 PM by PJSpark

    Creating a spreadsheet-like summary with dates

    PJSpark

      Title

      Creating a spreadsheet-like summary with dates

      Post

      I have one table that has several self-referenced occurrences, which relates to records of the same year and another that relates to record in the same year and the same month. So from those, I can obtain totals and other values from the the main table. From this information, what I would like to do is take the dates from the records and summarize them into months and quarters of a year like so:

       

      January              {summary columns of calculated data from other fields in the table with dates for all Januaries}

      February            {all Februaries}

      March                 {all Marches}

      April                    {all Aprils}

      .                                 .

      .                                 .

      .                                 .

      December          {all Decembers}

      Totals                  {total of each column}

       

      Hope this makes sense. I haven't figured out how to summarize in this format without relying on the current record. Of course, it would be easy to do in a spreadsheet, and I have no problem exporting out to Numbers. 

       

      Thanks

        • 1. Re: Creating a spreadsheet-like summary with dates
          philmodjunk

          When you say "all Januaries" do you mean "all januaries of the same year"?

           

          What you are describing is a Summary report and you can set this up with SubSummary parts, remove the body part and add a trailing grand summary part to display the grand totals.

           

          With the right summary fields and sort order, you'll get what you need.

           

          The exact details can differ a bit depending on my first question but here's the basic outline.

           

          Add a calculation field, if you don't have one already defined so that you can sort by month. This field might be either:

           

          Month (Datefield) (Works for all "Januaries" regardless of year)

          Datefield - Day ( DateField ) + 1 (Works if you want all "Januaries" of the same year)

           

          Sort your records by this field to get them grouped and in calendar order. Specify this field as the "sorted by" field in the sub summary part.

          The only thing left to do at that point is define "total of" summary fields for each field in your columns. Place this field in the subsummary part to show the subtotal for each group and place the same field in the Trailing Grand Summary part for your grand totals.

           

          If you are new to summary reports, here's a link to a simple tutorial on setting up summary reports that you may find useful:

          Creating Filemaker Pro summary reports--Tutorial

          • 2. Re: Creating a spreadsheet-like summary with dates
            PJSpark

            Thanks for responding. I've done some poking around, and it appears this type of report is generally called a Crosstab Report, which filemaker doesn't directly support. But I found a descent tutorial on the subject as well as some other information. This may be of some help to others: http://edoshin.skeletonkey.com/2006/12/crosstab_report.html

             

            Thanks