Use Aggregates in Reports

When you create a custom report, you can aggregate the entries in a field, so that the field shows a calculated value. For example, you can calculate the average received amount per account instead of listing all received values per account.

Aggregates are similar to summary fields in that they both show calculated data for each account, are available for custom reports, and many have the same calculations. However, aggregates only calculate values based on the data available in your query, whereas summary fields are calculated based on all information available on the account. For example, consider using an aggregate on the Received field to calculate an account's year-to-date giving to a particular fund. Conversely, use the summary field Year to Date Received Total to calculate an account's year-to-date giving to all funds.

To include the original entries and the calculated value, add the field to the report twice. Use an aggregate on the second version of the field.

To use an aggregate on a field, add the field to the report definition. Click the toggle button next to the field name. The row expands and additional options appear. Under Aggregate, select a calculation such as "Average." Next, we recommend you rename the column to reflect the calculation. To do so, click the column name, delete the field name, and enter the calculation name, such as "Average Received."

Types of Aggregates:

• Math - Use these on fields that contain number or currency data.

  • Sum - This calculates the total of the values in a field for each account, based on the entries in the query.

    Note: If you group your report by account, and select to show group totals or collapse, the report automatically shows the sum of any currency or number fields, even if you do not set up the sum aggregate.

  • Avg - This calculates the average of the values in a field for each account, based on the entries in the query.

  • Min - This shows the lowest value in a field for each account, based on the entries in the query.

  • Max - This shows the highest value in a field for each account, based on the entries in the query.

• Count - This shows the number of entries in each group, based on the entries in the query. The number one appears in the count column for every entry that has data. If there is no data, the number zero appears. The ones are added together and total lines appear to show the total count for each group.

  For example, if you use the count aggregate on the Approach field, but some transactions don't have an approach, then those transactions would not count towards the count total. If you include transaction and non-transaction entries in the same report, but only want to count transactions, consider using the count aggregate on the Fund field since transactions are associated with funds and non-transactions aren't.

• Date Range - Use these on journal fields that contain number or currency data. The aggregate calculates the total of the values in the query withing the specified data range. The date range depends on the date when the report is run. The data range options include: one year ago, one fiscal year ago, two years ago, two fiscal years ago, three years ago, three fiscal years ago, four years ago, four fiscal years ago, five years ago, five fiscal years ago. Consider setting up a report that shows yearly totals. Then run the report at the start or end of each year.

• Ordinal Number - Use these on journal fields to identify values that have a designated position based on the journal entry dates within the query. The ordinal number aggregate options include: first, second, third, fourth, fifth, and last. For example, consider using this aggregate to show the last received amount for each account or the first fund to which a constituent gave money.

Typically, reports are grouped by account and collapsed, so that the account's data is aggregated and shown on a single line.

When you group by something else, the data is aggregated per group per account. For example, if you create a report with the data grouped by fund and use the average aggregate on the Received field, then if a constituent gave once to the General/Operating fund and twice to the Unrestricted fund, the account's transactions would appear in both groups. The report would show the average of the account's gifts for each fund.

If you group by fund, the report shows the average of each constituent's gifts and gifts from all accounts per fund.

If you group by fund, and collapse the groups, the report only shows the average of gifts from all accounts per fund.