Spring Cleaning your Database II - Queries and Reports

Throughout the life of a database, many queries and reports are created. The main purpose of this cleanup is to dust off all of the cobwebs on the outdated queries and reports and make a clean sweep through your categories.

First let’s tackle those Queries!

To determine which queries should stay and which should go, you need to first get a good understanding of the purpose of queries and how they work.

A Query is a way to create a list of accounts or journal entries that meet set criteria that you define. Queries are often thought of as the “who” and they can be used in conjunction with Reports, Communications, Mass Update, Advanced Find, and Security Groups.

The options within the query will help you drill down the accounts or journal entries that you are looking for. To validate if your query is correct, you will first need to define exactly what you are looking for.

Do you want to create a mailing list that only shows Account information or are you looking to see all donations within a specific time period? If you are looking to see Account information such as Name, Address, Email and account related user defined fields, you will select Accounts as your Data Return Type .

If you are looking to see journal entry information in your report such as Date, Received, Fund, Contact Subject, or Journal related user defined fields, you will select Journal Entries as the Data Return Type.

Another option to pay close attention to with the query setup is the Criteria Options.

For the Criteria Matching, you will want to select “Match each criteria” when the results need to match every one of your criteria. You will want to select “Match at least one criteria” when the results need to match any of your criteria.

You also want to pay attention to the “Which persona should be checked for matches” when you are asking the query to only look at a specific Persona type on an account.

As you work through each category in this cleanup, consider the name of the category. You may want to think of the category as the name of the file folder that the queries are stored in. It is essentially the topic of the queries.  

A popular question that I hear often, in regards to Query categories is “What is Base?” Base is the name of the category that holds the Starting point queries, such as All Constituents, All Accounts, All Users, etc. You can continue adding queries to this category that may not have a definite fit in any other category.

Once the categories are organized, you can then go through each query to see if you still need that information or if you would like to delete it. I would advise checking with other users within your organization before deleting queries.

If you see queries that would be better suited in a different category, you have the option to move a query to another category.

If you are looking to create additional queries that are common, here are some of the more popular queries: How to find lapsed donors, How to query on donations within a specific time frame , and How to query on new donors

Moving onto Reports, which are often thought of as the “what”. 

Reports provide the detailed information on what exactly you are looking for. Report categories serve the same purpose as Query categories. When reviewing over your reports to determine which reports stay and which no longer have any relevance, keep in mind that you can use the same report for multiple queries.

Queries and Reports are a major part of your database, and you always want to make sure you are getting the most from them. Following these Spring Cleaning tips will allow you to feel good about the data that you are reporting on.

Until next time, keep dusting those fixtures, sweeping out the nooks and crannies and emptying out unnecessary clutter within your database. I encourage you to leave your questions, comments, and helpful tips below!