4 Replies Latest reply on Dec 4, 2014 11:46 AM by themactech.

Report Calculation on Summary Field

Title

Report Calculation on Summary Field

Post

ok. I stink at at Reports.. LOL.

Not entirely true but it is an area where I need more experience. In the attached screen shot I'm creating an efficiency report based on the number of tasks completed per day divided by a specific rate.

In the report, I have a script trigger searching for all completed tasks and then I have a summary field that Counts the number of found records (s_TasksCompleted). In the report you see I'm sorting by Name, Date, Assembly and the Sequence (the task).. it breaks it down by date and sequence and calculates the number of tasks complete for each sequence.

Now I want to take each totaled tasks complete for that sequence and divide by the rate shown to get the efficiency %

but right now it's taking the overall total of records found (24) and running the efficiency calculation.

how can I get it to just calculate the number of records on each line.. ?

• 1. Re: Report Calculation on Summary Field

Use the GetSummary function to get the sub total shown in each sub summary layout part rather than the grand total based on your found set. Note one key limitation that GetSummary has that your sub summary layout part does not have: GetSummary requires a break field defined in the same table as specified for your layout and your summary field where a sub summary layout part can specify a break field in a related record. (The "break" field is the field specified as your "when sorted by field" in the part set up for your sub summary layout part.)

Caulkins Consulting, Home of Adventures In FileMaking

• 2. Re: Report Calculation on Summary Field

I got that work. Thanks.

Like you said, the one drawback for the GetSummary function is that the break field must be in the same table. I created a new calc field that in the table that mirrors the data in the related field then used that as the break field. Now the efficiency % are calculating accurately. So thanks for that.

My new challenge is to show the overall average of that efficiency percentage per staff person. So i created a new summary field that averages the total found efficiency % fields. My break field is "Last Name" But I'm running into an issue when I try to use the same technique. It's working to some degree but instead of averaging the grouped efficiency % (by sequence) it's averaging ALL of the found efficiency % for that staff person.

so in the example attached... i have the efficiency % calculated by total completed.. so the first 3 just have one.. but the down further I have one where I show 4 have been completed. The efficiency % shown for that line is correct but when it does the total average at the top.. it's actually counting that 8.95% 4 times..

So if can figure out the break field I think I can nail this down.

• 3. Re: Report Calculation on Summary Field

You'd have to divide each value to be averaged by the sub total counts to calc the correct answer.

• 4. Re: Report Calculation on Summary Field

interesting. I'm trying it every which way but loose and still can't get the calc correct.

Do I even need to use the GetSummary function in this case?