4 Replies Latest reply on Jan 7, 2014 6:55 PM by BruceHerbach

    Create subtotal reports

    phanh

      Hello, everyone

       

      We are looking to create summary reports that allow us to organize volunteering data into meaningful groups with aggregate calculations. In addition, “grand total” type value computed and located at the end of the report.

       

      We have a volunteer database where volunteers can check in and checkout at different locations. Each school would like a report that list the total volunteering hours per volunteer and then the total at the end for each school. The data is stored in two different tables: vol_InOut (checkin, checkout, location, hour) and vol_data (total_hour). The hour field is used to calculate a particular volunteering hour by day and the total_hour field is the total volunteering hours together.

       

      Screen Shot 2014-01-06 at 8.16.14 AM.png

       

      Screen Shot 2014-01-06 at 8.16.33 AM.png

       

      For this particular volunteer, she volunteered at more than one schools so our calculation is inaccurate. We are looking at this tutorial (http://forums.filemaker.com/posts/be76a023b9) but only want to display particular volunteering hour associated with a school. The tutorial seems to display all data that group by a specific field.

       

      We are wondering if there is other way to accomplish the solution. Any advice is appreciate.

       

       


        • 1. Re: Create subtotal reports
          BruceHerbach

          Hi,

           

          Base the report layout on the table with date and location fields.  Have a script open the report and do a find for the location you want in the report.  The sort by name so that it shows the volunteer's name in the sub summary part of the layout.

           

          You can also have multiple sub-summary sections and sort by two criteria so it show's both sections.  For example this could be volunteer name and District.  The District Subsummary section would show the name of the district and the line items would show date and hours.

           

          You can set this up and do a manual find and sorts to see how the report will work then develop the script.

           

          HTH

          • 2. Re: Create subtotal reports
            phanh

            Thank you, Bruce for the advice. We try to understand your advice above but wondering if our solution just missing one element.

             

            We have two tables: one keep track of the volunteer personal info which includes total volunteering hours and the other table keeps track of the checkin, checkout and volunteering hours per day. The "hour" variable calculates the total volunteering hours per day while the vol_report_TotalHours variable is the sum of all "hours".

             

            We are wondering if we need to create an additional variable to calculate the total hours but filter out by Location variable? We duplicated the vol_report_TotalHours and add in the condition to filter out by location but because the variable is calculation type with sum(), we couldn't add that in. We not familiar enough with all the available functions in Filemaker. Any advice is appreciated.

             

            Screen Shot 2014-01-07 at 2.02.07 PM.png

            Screen Shot 2014-01-07 at 2.02.44 PM.png

            Screen Shot 2014-01-07 at 2.12.54 PM.png

            • 3. Re: Create subtotal reports
              BruceRobertson

              Variables and fields are entirely different things. You are referring to fields; not variables.

              • 4. Re: Create subtotal reports
                BruceHerbach

                Hi,

                 

                When setting up a summary field you just have to tell it what field you want to sum.  You can set it up as a running total and tell it when to restart the total by sorting on a field.  Take a look at the example summary field attached.

                 

                You field is trying to determine what to include,  this won't work.

                 

                Once you have the field,  put it in the Sub Summary part of the layout and put the field it sum in the body section.  Fidn the records you want, and sort.  You should have both the line item value in the body and the sum in sub summary section.  In short keep it simple and let FileMaker do the work.

                 

                Bruce