Tips for Using Aggregates in Blackbaud eTapestry® Reports

Geoff Arbuckle

Last week, I published two blogs on basic tips I always want to emphasize to eTapestry users when it comes to Queries and Reports. Earlier this week, I gave some more tips on Compound Queries. Today, I go back to Reports to speak on the power of Aggregates.Last week, I posted a blog post with tips regarding the use of Summary Fields in Reports. I talked highly about the advantages of adding these columns to show summarized donation totals for a pre-determined date range (i.e., Year to Date, One Year Ago, Two Year Ago, etc.) even if your query is set to only display account information. While helpful, these Summary Fields can only pull full amounts from the relevant accounts, regardless of what your Query set for criteria or limitations over very specific transactions.



Wouldn't it be nice for you to pull information about, say, Funds and show what donors to that Fund gave year over year?



Have I got great news for you, readers ... you can!



Let's say you have a query that pulls all the transactions ever given to a specific Fund or Approach or Campaign. Whoever you are creating that report for might decide they would like to see that over the course of a few years. Maybe they are asking for a readout of how much money each donor gave to that specific Fund year to date, and then list the last few years too. Through the use of Aggregates, you can do just that.



How do you activate these Aggregates? That's easy. When building a Report, as you select the columns to appear in the Report, you will likely notice that each column added has a trio of tiny dots next to them. When you click on this, you will see there are various options available such as making the field visible or invisible, selecting the sorting order (ascending or descending), and the Aggregate options. When you open that drop down menu, you will find several options available to you. These include showing years like the year to date, one year ago, or two years ago, etc.



To make these fields work well for you, here's what I recommend you do: When building a new report by going to the Reports tab, then clicking one of the categories listed, and clicking New Report, make sure you are setting the Group Report By to Account, and then clicking Collapse Groups. Then, as you select your fields, click to add the Commonly Used Field "Received" multiple times. Click it at least as many times as you need to cover the years you wish to aggregate and include. When you click the options, select a different Aggregate for each use of Received. You will also notice that selected columns all have names in a gray box. That means you can rename the columns as well. So, if you choose the Aggregate for Year to Date, change the name of the column from "Received" to "YTD Received" or something similar. Repeat that process for each column you chose for Received. As you run the report, and then match your journal entries return type query to that report on the Launch page, it will calculate those columns as desired.



You might also notice other ways to leverage this option within the Aggregate menu. You can do Average or Max or Min to find the highest gift given from that query or the lowest. You can also use these options on a Date column as well so you can show the Date and Received Amount that was the highest or lowest. You can also use First or Last to show the first gift and date and the last gift and date.



My recommendation for you would be to build a report to play around with these Aggregate options. I think you'll find aggregates "a-great-great" way to get more out of your reporting capabilities! Until next time when I have more tips for you, keep on eTapping on!