I wasn't able to download your file since my company's policy prohibits downloading (potentially) executable files.
I have an Average Errors calculation field which is just "Average(Errors)".
I have a Year and Month calculation field with is simply Year(Date) & Right ("0" & Month(date); 2)
In my report, I have a sub-summary part that I group the report by Year and Month.
The report correctly shows the detail records, grouped by year and month.
Where/how would I add the Summary field you were suggesting?
If I add a Summary (average (errors) field) to the database, for example, FMP 12 then tells me that I am not allowed to use a Summary when I select it via the "Part Setup" screen.
Therefore, I'm still not sure how to summarize each YEAR+MONTH's data to just get an average of the detail's "Errors".
Thanks in advance for any update.
Go to File - Manage - Database, Go to the second tab "Fields" and create a new field called s_Average (so you know it's a summary field) and set the type to "Summary"
Then choose "Average of" Reported errors. Like so:
Then go to your layout and put this average value in your sub summary part. Sort according to the correct field and this should work.
You could also make a summary field that summarizes the "Total of" reported errors. And add that field to the sub summary part.
"I have an Average Errors calculation field which is just "Average(Errors)"."
This will indeed give you the value for the last record (in a trailing sub-summary part), but what value it gives depends on what the field 'Errors' is defined as.
Got it working, thanks! (forgot to make the Errors field numeric.)
One final question -- I'm having difficulty charting the averages on a graph.
If I select YEAR/MONTH for the x-axis and s_summary for the y-axis, the graph doesn't show the summarized data.
Is there an additional trick needed here?
The chart setup looks different depending on whether you use FileMaker 12 or an earlier version, but both have an option to chart "groups of data when sorted". You need to specify that option and then make sure your records are correctly sorted--it's a bit like getting sub summary parts to show data in a summary report.
Turned out to be a sorting issue. I corrected the sort and assigned a script trigger to the chart layout and it looks great now.
Thanks very much.