By Aaren Ballinger, Sr. Consultant
The “Select filter and output fields” tab of query gets all the glory. It really is the “meat and potatoes” of building your query, as it is where you select those all-important filters and output fields.
However, the second tab of the query tool in Blackbaud CRM™, named “Set sort and group options”, is where the magic really happens. It can turn some unwieldy results into easier to digest information through the powerful features built into it. This tab has two important sections: “Sort records by” and “Include records where”. Here’s a breakdown of what these features can do and how you can use them effectively.
Sort records by:
The first thing that you will notice when looking at the Set sort and group options tab is that all of your query output fields appear under “Select results fields for sorting or group filters”. You can now select any of these fields and move them to the “Sort records by” window by either dragging and dropping or using the arrow buttons. This allows you to sort your query results by one (or multiple!) of these fields, which can make your query results easier to understand and sift through. Utilize the buttons at the top of the Sort records by window to switch between ascending and descending order.
Here’s some examples of how you could sort your results using different fields:
Alphabetically by Last Name (“Last/Organization/Group/Household name”)
Chronologically by gift date (“Revenue\Date”)
Numerically by gift amount (“Revenue\Amount”)
Include records where:
The “Include records where” window is a super versatile and useful feature, but I think the average query user isn’t aware of how it works. Essentially, you can use this feature to further filter the results of your query.
The first thing to note about this feature is that you can only use it if you have applied a summarization operator to one of your output fields. You’ll remember that this is blue “Sigma” button found in the output window on the first tab of a query. Learn more about the summarize query output feature through this past blog post.
To make this feature a bit easier to understand, I think it’s helpful to walk through an example exercise. Let’s say that you want to do some analysis of the giving of your Board of Directors. You build a quick constituent query that selects those with a constituency of “Board Member” who have made a gift during calendar year 2020. Under “Results fields to display” you add the “Revenue\Application Details\Amount” field. You then add the SUM operator on top of that output field in order to see a total dollar amount that each of your board members have given during 2020.
Now say that you are looking at your results, and you decide that you’d like to filter them even more to see which board members have given $5,000 or more to your organization this year. This is where the “Include records where” window comes into play!
When you drag the “SUM(Revenue\Application Details\Amount)” output field into the “Include records where” window, a pop up appears asking you to apply your criteria. In this case, we can change our operator to “Greater than or equal to”, and then input “5000” for our amount. When we re-run our query, the results will not show only those board members who have given a total of $5,000 or more this calendar year.
The possibilities are endless when it comes to using this feature to further narrow down query results. It can be a powerful tool to better understand large amounts of data and save time fiddling with your results after they have been exported.
I hope this quick tutorial has helped you better understand the often-overlooked second tab of the Blackbaud CRM™ query tool!