Basic Reporting Part IV FAQ: Understanding "Not In"/"Not Equal To"

Basic Reporting Part IV FAQ: Understanding "Not In"/"Not Equal To"

For Individuals/Organizations:

1. When attempting to exclude contacts from a report if any of their records/transactions meet a certain criteria presents a unique challenge because the report logic evaluates each record individually against the stated criteria.
2. For example, excluding donors from a report if they ever donated to a particular campaign.
3. The report logic is looking at every single individual donation record individually and deciding if it meets the criteria. If donation A was to the campaign, it gets excluded. But if donation B was not to the campaign, it gets included. If those donations were made by the same person, then the person gets pulled into the report even if they have an excluded donation. The system is not looking and cannot look at a donor's donation history as a collective/whole and is not evaluating the DONOR, it's evaluating each donation (the same rules/logic applies to memberships, event registrations, etc. - this is why membership reporting becomes very difficult if a member has two membership chains/records; the system is going to evaluate each membership record individually, not collectively, so that if one record matches the criteria but another doesn't, then the member still gets pulled in to the report).
4. This reporting logic exists in all relationship databases/regardless of system and is not specific to CRM/Exago.
5. CRM's advanced search function is specifically designed to search a contact's history as a whole/collectively, rather than evaluating individual records. Using advanced search for the example given above (exclude donors who ever gave to a particular campaign), if any of the contact's records meet the criteria, then the contact is excluded from the results.
6. When attempting to write these types of reports, you should create the criteria in Advanced Search and then use the saved search in the reports module to customize the report output (see our tutorial on using a saved search in a report).

For Households:

1. If you select "apply to household" in advanced search when creating this type of search, then everyone in the household will be excluded from the search results if anyone meets the criteria. For example: Terri Smith and Jim Smith are in a household together, and Terri has a donation on her record that is linked to the Annual Appeal 2018 campaign. If we write an advanced search with the criteria "donation campaign not equal to Annual Appeal 2018" and check the apply to household box, both Terri and Jim will be excluded from the search results/will not be pulled into the search results (even though Jim didn't donate to the campaign, he is still excluded because Teresa did donate).
2. Sometimes it is impossible to write criteria to exclude contacts, especially when trying to make two or more "mutually exclusive lists" (e.g. "show me everyone who donated last year" and "show me everyone who is a lapsed donor" and contacts should be on one list or the other, but not both) when households are involved where one contact meets the criteria and one doesn't.
3. In these cases, your best bet is to apply a temporary contact tag to all the records you want on one of the lists. For example, if we want one list of everyone who DID donate to the 2018 campaign and one list of everyone who did NOT donate to the 2018 campaign, and we want Terri and Jim on the "did donate" list because Terri donated (but Jim did not) and want to be sure they do not also end up on the did not list (because Jim didn't donate), then we can pull up the list of everyone who did donate to the campaign (which will pull in Terri and Jim), tag their records with something like "2018 campaign donor." Then, we can write the DID NOT contribute to the 2018 campaign search criteria to include a filter for "contact type not equal to 2018 campaign donor." That will make sure that Jim and Terri are not also pulled into the did not list, when they belong on the "did" list.