Aggregates Vs Summary Fields In Custom Reports

Custom Reports allow you to organize and showcase your data in a meaningful way. You can perform calculations like adding together numbers or even finding an average amount. There are a couple tools within custom reports that we often mix up because they appear to do some things similarly: Aggregates and Summary Fields. However, they are actually quite different! Let’s talk about each of them separately first.

Aggregates
You can apply an aggregate to a field if you want to count the number of results, calculate the sum of a group of numbers, or see separate columns for totals within certain time frames. You can find a full list of all the aggregate options and what they will calculate in our help guide. In short, aggregates focus on the accounts or journal entries your query returns to provide results.

As an example of what aggregates allow you to do, let’s build a report that shows a 5 year breakdown of money received based on Fund. I want to have a separate column for each year next to each Fund.

First, every custom report needs a query. Since we plan on using aggregates, we need a query that contains all of the transactions we received between 2012-2016 which is our 5 year time frame. This is a simple date range query that should return Journal Entries. Once we have a query, we’re ready to start building the custom report. Under Reports, choose your favorite category and create a new report. I would suggest naming it "Five Year Giving by Fund."

Next, let’s add report columns. These will be the columns of information displayed the end result. Follow the steps below.

• Under the Commonly Used Fields drop down menu and click Fund and Received 5 times
• Arrange the columns into order by clicking on the middle of the field column and dragging up or down. I would suggest putting Fund at the top of the list
• Click on the dotted menu icon to the left of the first Received column and set the Aggregate drop down to One Year Ago
• Change the column header to something like “One Year Ago” or “2016” so that it makes sense in the report. Highlight the column name and just start typing! Repeat for the other Received columns
• Under Group Results, select Fund
• In the drop down menu that will appear immediately after, select Collapse Groups

Now, we're ready to run the report! Click Save and Run under the Task menu. Choose the query you created for all donations from 2012-2016 at the top of the page and then click Submit.

There you have it! This same report can be used each year to see ongoing trends with your Funds. To change the date range, you just need to adjust your query to include the appropriate dates. You can also do a similar setup to group the results by Campaign, Approach, or even the Account Name of your constituents instead of Funds.

Other helpful reports that use aggregates:
How do I report on number of transactions per donor?
How to create a report for the number of donors in each state

Summary Fields
If you’ve ever wanted a quick run-down of giving for a list of constituents but weren’t sure how to get it, allow me introduce you to summary fields! These are pre-defined fields that show a wide range of detail and summarized information about an account. There are a total of 320 unique fields to choose from, including Household Fields which show summarized information for the entire household. The most important thing to remember about these fields is that they look at the account/household as a whole to determine the output in the report.

Let’s work through an example that will showcase the capabilities of summary fields. I want to see a 5 year giving history for all of my constituents. I’d like to have a column for each year, a lifetime total, and the date and amount of their last gift. I want my results to sort based on the total lifetime giving in descending order so that I can see top lifetime donors first.

We can use the standard Base: All Constituents query here because summary fields look at the entire account’s giving history. (This is where aggregates and summary fields differ the most.) Under Reports, choose a category and create a new report. A good name for this might be "Five Year Giving History."

Next, let’s add report columns. These will be the columns of information displayed in your end result. Follow the steps below.

• Select Sets from the drop down menu under Browse Fields and click on Giving History Information (This set of fields has almost all of the information I need!)
• Select Summary Fields from the drop down menu under Browse Fields and click Last Received Amount and Last Received Date
• Click on the dotted menu icon to the left of the Lifetime Received Total column, and click on the Z to sort by largest to smallest and choose to sort on it 1st. If you have the Sort Name sorting 1st and change that setting, it will change the Sort Name to 2nd.

Now, we're ready to run the report! Click Save and Run under the Task menu. Choose the category Base and the query All Constituents at the top of the launch page and then click Submit. This same report could be used to get details about any group of constituents. For example, if you only wanted to see this information about your Board Members (tracked under Defined Fields) you could use a query that contains those individuals instead of All Constituents.

To recap, aggregates are used when you want to perform a calculation on a field based on the results in a query. The key to aggregates is the query - if it doesn't provide the raw data the aggregate cannot calculate. Summary fields are used when you want to see summarized journal information about an account as a whole. They don't look at specific journal entries in the query because they pull data about the entire account.

What's your favorite way to use aggregates and/or summary fields? I'd love to hear how you utilize these awesome reporting tools!