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.
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.
Variables and fields are entirely different things. You are referring to fields; not variables.
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.
summary field.jpg 141.1 K