As you begin to use metrics more, you may come across the need to summarize some of the data that gets collected in metrics. Fortunately, there is a little hidden attribute on each metric called Aggregate Type. While aggregate type isn’t really used by the Arena UI, it can be useful in various reporting scenarios.
Say you want to create a monthly summary report that displays metric data summarized for the last 12 months. If you have a metric for weekend attendance, and another metric for baptisms, you’d probably want to summarize those numbers differently when reporting monthly numbers. For example, for attendance, you’d want to report the maximum number (weekend with the greatest attendance), but for baptisms, you’d probably want the total number (sum) during the month. That’s where aggregate type comes in.
By default, you will not see an Aggregate field when editing metrics. You’ll need to turn on the “Show Aggregate Field” module setting on the Metric Chart module. Once you’ve turned this setting on, you’ll be able to set aggregate types for each metric. Possible values are Average, Total, Maximum, and Minimum.
Once you’re using aggregate types, you can then take advantage of a couple SQL functions when writing custom reports…
mtrc_get_aggregate_value
When you pass this function a metric id and start and end date, it will return a value for that metric based on the aggregate type. For example, if the aggregate type is Maximum, then the largest metric value between the start and end dates is returned. If the aggregate type is Average, then the average of all metric values between the dates is returned. etc. etc.
mtrc_get_monthly_values
When you pass this function a parent metric id and a start year, and month, it will return a table of all the child metrics and their respective aggregate values for the following 12 months. This function uses the previous function to determine the aggregate values for each metric and month.
We use both of these functions to generate the type of report that Jon blogged about here.
