BASIC REPORTING PART IV: Adding Filters (REQUIRED)

BASIC REPORTING PART IV: Adding Filters (REQUIRED)

NOTE: Due to a recent update to the reporting module, you may notice some differences between your system and the screenshots in this tutorial. We are working to update the screenshots, but in the meantime, the steps in the tutorial below are accurate/have not changed.

The video below is a recording of the "Intermediate Reporting: Filter Logic" webinar.

1. Once you have decided on the type of report you want (basic data/facts & figures, comparative report, or a report that answers why the data shows what it shows (why something increased or decreased) by correlating two or more data points (our Basic Reporting Part I tutorial) and the layout that will best suit that purpose (our Basic Reporting Part II tutorial), it's time to begin creating your report (our Basic Reporting Part III tutorials).
2. While you are creating your custom report, you will want to add Filters to the report. Filters are the rules you give the report for what data should be included on the report. The fields that you add to the report output (the data that shows on the report) do not in any way affect the data that is pulled into the report. For example, just adding the Donation Date or Donation Amount field to the report layout does not limit the report to just showing people who have made a donation. Everyone in your database will still show on the report until you add some filters to the report, telling the report to only show you contacts who meet a certain criteria.
3. Filters are the rules that tell the report which data to let into the report. The layout (covered in our Part V tutorial) details what data SHOWS on the report. These are not the same thing. Not all data allowed into the report has to show on the report. For example, we might use filters to limit the data into the report to just people who became a member between January 1, 2019 and December 31, 2019. However, we might not show the membership information anywhere on the report. We might not even care about their membership data. It might be we want to see which events these "2019 members" attended. So we might use their membership start date as a filter to limit the report to just people who became members in 2019 but not to affect the data that is shown on the report at all. The filters and the report output are two separate, independent steps.
4. Please Note: sometimes, you might want to do data analysis with a report - that is, you might want to bring a large chunk of data into a report and then "slice and dice" your data in various ways (for example, you might want to bring in your 2019 donation data into the report and then filter and sort the data in a variety of ways to look at the data by campaign, solicitation method, with or without certain programs or types of donations included etc.). This kind of analysis involves filtering a cohort of data "on the fly," which can be done in our Smart Dashboards and using the Interactive HTML Viewer in Advanced Reports. That sort of "on the fly" analysis is different than the report filters we are talking about here - in this tutorial/step of report creation, we're talking about the rules/criteria for pulling in the cohort of data to be looked at, regardless of whether that is a narrow cohort to be used in a specific report or a larger cohort that will be "sliced and diced" later. We will cover "slicing and dicing" in Basic Reporting Part VIII: Using Your Completed Report.
5. The Basic Reporting Part III tutorials cover where in each of the various report writing interfaces you add the filters to the report (for example, in the Express View interface, it's the funnel icon on the right hand side of the screen. In the Advanced Reports interface, it's under the gear/Settings icon). Below is the basic gist of how to add filters to the various reporting interfaces, but consult the interface specific tutorials for more details. In general, you will first select the category, then the table within the category, then the specific field you want to filter by, and then enter your operator and match criteria.

   Basic Dashboard

   Click the funnel in the upper right of any chart and then select from the drop down options.

   

   

   

   Smart Dashboard

   Add filters by selecting from the fields and criteria on the left.

   

   Advanced Search

   

   Select the Category (equivalent to selecting a table in reports interface)

   

   Select the criteria (equivalent to selecting the field in reports interface)

   Select the Operator

   

   Enter or select your criteria

   

   Express Reports

   Filters are found by clicking the funnel on the right hand side of the screen.
   

   

   

   Double click on the category on the left to see a list of tables in that category

   

   

   Double click on a table name to see the list of fields in that table

   

   Drag the field to the filter window on the right and then select your operator and enter your criteria

   

   Select the operator and then add your criteria

   

   

   Advanced Reports

   Filters are accessed on the Setting Menu (gear icon in the upper left)

   

   In the filters window, start by selecting a table from the drop down on the upper left (you can only select from tables you added to the report on the categories tab)

   

   

   Drag the field you want to filter on to the right hand panel

   

   Select your operator and add your criteria in the lower half of the screen

   

6. Computers and reports are strictly literal, so all data will come into your report unless you exclude it using filters. For example, setting a filter for "donation date is between 01/01/2019 and 12/31/2019" will pull in ALL donations made between those dates included voided/cancelled donations and in-kind as well as cash donations. If you want to exclude voided donations, you would need to add a filter for that. If you want only cash donations, you need to add a filter for that, too.
7. In general, when adding filters to a report, there are a few basic principles to keep in mind:
   • It is better to structure a filter as a positive statement than as a negative statement. For example, "Donation Date is After 01/01/2019" is better than "Donation Date Is Not Before 01/01/2019". Even though those two statements should return the same results, it is not always the case that they are equivalent (see "Not In" below for more details of why this is the case).
   • AND - using "and" to connect two or more filters means that ALL of the connected conditions must be true. For example: "gender = female" AND "mailing address city = "Boston" means  a contact's gender must be female and her mailing address's city must be Boston for the contact to be pulled into the report. Since Organization's don't have gender, they would automatically be excluded. Any contacts who have their gender marked as female but whose mailing address is some other city OR BLANK would be excluded from the report. Similarly, anyone whose mailing address is Boston but whose gender is blank or anything other than female would also be excluded. You can connect as many criteria together as you want using AND, just keep in mind that ALL of the criteria must be true for a record to be included in the report.
   • OR (in Express Reports and Advanced Search the term used is ANY; ANY means the same as OR) - using "or" to connect two or more filters means than ANY/just one of the conditions must be true for the contact to be pulled into a report. For example: "gender = female" OR "mailing address city = "Boston" means  a contact's gender must be female OR the mailing address's city must be Boston for the contact to be pulled into the report. In this example, all women, regardless of where they live are pulled into the report and all contacts, even organizations, whose mailing address is in Boston (regardless of gender) are pulled into the report. You can connect as many criteria together as you want using OR, just keep in mind that ANY/just one of the criteria must be true for a record to be included in the report.
   • RULE GROUPS - rule groups are when you want two or more rules to work together/be applied per RECORD, not per contact. For example, if you set a simple "AND" filter as "Donation Date is between 05/01/2019 and 05/31/2019" AND "Donation Fund is Annual Appeal", you will get anyone who made a donation during May 2019 (regardless of the fund) AND who also made a donation to the Annual Appeal Fund (regardless of date) because those two things are true PER PERSON. If you want those two things to be true per record (in this case, per DONATION), then you would need to create a rule group. A rule group says "these X number of criteria must be true together/per record." You can create rule groups in an Advanced Search (see the section on Rule Groups within the Other Criteria section of the Searching for Contacts tutorial) and in Advanced Reports. Rule Groups cannot be used/created in Express Reports.
   • Some criteria/filters CANNOT be created in reports. For example, you cannot filter by a sum (e.g. you cannot say "show me everyone who donated more than $500 total last year) (because you can't filter by a formula/sum, you can't stop all donors who donated last year from getting onto the report, but you can hide data from the output using conditional formatting, which will be covered in our Basic Reporting Part V tutorial). Additionally, in reporting you cannot filter by household (e.g. "pull everyone in the household into the report if anyone in the household meets this criteria"). HOWEVER, these types of filters CAN be created in Advanced Search; you can then drop the results of an advanced search into a report (so that you can customize the output/control the report layout/output). See this tutorial for more information on using an Advanced Search in a report.
   • The filter "Not In" or "the absence of a condition" (e.g. show me everyone who doesn't have any donations in 2019) is especially problemmatic/difficult to use. Because the report will evaluate each individual record, rather than the group/the person, it's simply going to look and see if each individual record meets the criteria. If Jane has two donations, one in 2018 and one in 2019, the 2018 record is "not in 2019" so Jane gets pulled into the report, even though she has a donation in 2019. See our tutorial on why structuring these queries/filter criteria is so difficult and what to do about them.
   • Because the report looks at each record individually, and not as a group/not per person, when you want to consider only the first or last record in a group, you will need to use the Min and/or Max function. For example, you cannot simply say "donation date is before 12/31/2018" to find people who have not donated in 2019, because if they made a donation prior to 1//1/2019 IN ADDITION TO A DONATION IN 2019 then they will still appear in the report. Instead, you would have to use the Max function to say "the Max (last/most recent) donation date for each person is not after 12/31/2018." Then the system will look at only one record per person - their most recent based on donation date - and evaluate if the person meets the criteria. See our tutorial on using the Min/Max function for more information on how to use this (can be used in Advanced Search, Cross-Tab Reports, and Advanced Reports).
   • Please see our Basic Reporting Part IV: Donation Reports - Tips and Basic Reporting Part IV: Membership Reports - Tips tutorials on additional filtering tips, specific to donations and membership reports.