Basic Reporting Part V FAQ: How Do I Get a Report That Only Shows Donors Who Gave Over a Certain Amount Total?

Basic Reporting Part V FAQ: How To Filter By Total Giving in Reports (Example: “How do I write a report that shows everyone who has given over $500 total in calendar year 2015?”)

• Users can sum up the total amount of money donated by each contact using the “AggSum” formula/function.
• Users can filter reports by donation date to limit the donations to a specific period of time.
• Users cannot filter by sum (because of Exago report logic which first applies filters to the report and then applies the summation formula (applies the summation formula after it has already filtered the report)).
• However, you can apply conditional formatting to your report that suppresses any values that are less than your desired total donation amount. That is, if you want to see all donors who donated $500 total in the last year, you will need to filter by donation date (to show only donations given in the last year), apply the AggSum formula to the donation amount to total up each donor’s donations within the specified time period, and then apply conditional formatting to the summed up donation amount field to suppress/hide all totals less than $500.

Part 1: Sum Up Each Contact’s (or Household’s) Total Giving

1. You will need to add a group footer to the report and group on contact (contact ID or contact name or household ID).
2. You can only create a group header/footer for fields that you sorted on (on the Sorts tab).
3. To begin, create a new report or edit an existing report.
4. Make sure you have added the Contacts or Contacts with Contact Types category to your report (on the category tab).
5. Click on the Sort button in the upper left of the report under Filter and Sort toolbar.

6. Select Contact.Contact Name or Contact.ID (you can also use Household ID if you have donors that are in households). Drag the field to the “Sort By” column and then click OK to save.

7. Next, in the Report Layout screen, right click on Detail, select Add Section and then Group Footer in the resulting pop up menus Or click on Add Section at the top and select Group Footer.

OR

8. From the Group Footers pop up box, click the drop down menu and under Sort Fields select the field you wish to show a summation for (Contact Name, Contact ID, or Household ID – whichever field you added to the Sorts tab/selected in step 5.

10. Click OK to save and close the Group Footers pop up box.
11. You will now see a new section/row on your report: Footer: Contact.Contact Name (or Contact ID or Household ID – whichever field you picked in Step 5).

12. In the Group Footer, add the donation amount field by selecting it from the list on the left and dragging it into the group footer where you want the total donation amount per person/household on the report.

13. Click on the Donation Amount field on the report (in the footer) to select/highlight it (the cell will turn blue to show it is highlighted), and then click the formula/function editor button (“fx”) on the Cell Contents tool bar and add the “AggSum” function to the field. Click OK to save and close the Formula Editor pop up box.

or type the following formula in to the cell where you want the donation total to appear:

=AggSum({Donation.Donation Amount})

14. Now, when you run the report, you will see a grand total sum/dollar amount of total donations during the time period per contact/household.

Part II: Suppress Donation Totals Less than the Desired Amount

15. Right click on the Donation Amount field in the group footer (the one with the AggSum formula) and select Format Cells.

16. Click the Conditional tab at the top of the pop up window.

17. Click Add on the bottom left corner.

18. Under the action drop down select Suppress Rows and then click the formula button.

19. Add the following formula:

AggSum({Donation.Donation Amount} < [amount])

Replace [Amount] with the $ amount you desire. For instance, if you want to see only those donors who gave more than $100 total in the specified time period, then the formula would be “AggSum({Donation.Donation Amount}<100”).

20. Click OK to save.
21. Now, when you run the report you will see only those donors who total donation amount for the specified period of time is greater than the amount you specified in the conditional formatting section.