What you’ll learn in this article:
- Definitions of field filters, cross-filters and sub-filters
- Reporting filters in practice, use cases and examples
- A review and explanation of filtering logic
- A review of common filtering mistakes and how to avoid them
The goal of this documentation is to create confidence with CRM reporting and to become familiar accessing your data in more advanced ways. The topics and examples we cover are intermediate, so ensure that you are comfortable with the reporting basics guide. Let’s dive in!
Reporting filter definitions
Report filters determine which records are included or excluded from a report. The goal of walking though the different filters that are available is to develop an understanding of the tools and to ensure that correct filters are used in practice.
Field filter: Field filters are a way to limit the records that are pulled into a report. Use multiple different field filters to create an AND relationship. There can be up to 20 field filters in a report. For example, a field filter of Gender equals “F” will exclude any Contact record where the Gender is not “F”.
The red arrow identifies a field filter. Field filters can live on their own.
Cross-filter: This view condenses the records that are pulled into a report by either pulling data with or without specified related records. Cross-filters allow you to filter your report from related records that live below the main object of your report. Combine cross-filters to create an AND relationship; the maximum number of cross-filters that can be used in a single report is 3. For example, a cross-filter of “Contacts with Registrations” will exclude any Contact record in your dataset that does not have a related Registration record.
The red boxes identify the cross-filters.
Sub-filter: The name for a field filter that lives under a cross-filter. With the use of a cross-filter, sub-filters allow you to filter records from related records that live below the main object of your report. You can only filter on the fields directly on the object specified in the cross-filter. Each cross-filter can have up to 5 sub-filters. For example, if you were using a Contact level report type and wanted to filter with the Registration field “Event Year”, first you would add the cross-filter for “Contacts with Registrations” and then you would add a sub-filter for “Event Year”.
The small blue arrows identify a sub-filter. Sub-filters always live under cross-filters.
Report filters in practice
Field filters
A field filter is the simplest way to exclude records in a report. A field filter functions on any field that is included in your base report type. You can see all of the available fields from which to create a field filter in the left panel of the report builder. These fields can be from the main object in the report. For example, in a Registration report type, the main object would be Registrations or any object above the main object, such as the Registration’s Contact, or even the Registration’s Contact’s Household.
In this way, you could add a field filter to find only Registrations that are from Contacts who are part of a Household with more than 3 members (the field filter would be “Total Family Members” is greater than 3).
A field filter cannot function on related objects below the main object in the report. For example, if you are starting with a Contact level report type, you cannot filter on a field from a related Registration record, as Registrations are under Contacts. This is where cross-filters come into play, which we’ll cover below.
How to use multiple field filters
When multiple field filters are used AND relationships are created. Add up to 20 field filters to exclude records and pull the data for analysis. Because each field filter creates an AND relationship, you never want to use multiple field filters for the same field in a single report. If however, you want to change the logic that is used to different combinations of ANDs and ORs, you can use filter logic to do so, which we’ll cover below.
How to use commas within field filters
In some cases, you will want to call out multiple values in a single field filter, such as pulling information where Event Year equals “2018, 2019, 2020, 2021”. However, note that the use of commas in field filters will always create an OR relationship. This expression will pull data where the Event Year is equal to 2018 OR 2019 OR 2020 OR 2021. Adding commas within a field filter, or adding filter logic of OR will expand the records pulled into a report. Additionally, you’ll want to be aware that in some cases there will be commas within values that you're searching. For example, if the Event Name that you want to filter contains a comma, such as "Forest City Walk, Run and Roll", you must include quotations around the entire value so it will be evaluated as 1 value instead of 2 values.
Note: The use of commas in field filters will always create an OR relationship.
Filter logic explained
Filter logic can be used on field filters to expand the report results. Filter logic does not apply to cross-filters or sub-filters, only field filters. Filter logic allows you to change the AND logic to OR logic (or a combination of ANDs and ORs).
- AND - finds records that match both values (1 AND 2)
- OR - finds records that match either value (1 OR 2)
Note: Filter logic only applies to field filters and not cross-filters or sub-filters.
To add filter logic into your report, click on the arrow drop down to the right of “Filters”, then select “Add Filter Logic”. To create your logic, separate each field filter by a filter logic operator.
For example, “(1 AND 2) OR 3” will find records that match both Filter 1 and Filter 2, or Filter 3.
Let’s say you’re interested in pulling all contacts in your database from California. For example, instead of:
1. “State/Province” equals CA, California
AND
2. “Postal Code” starts with 9
You can implement:
1. “State/Province” equals CA, California
OR
2. “Postal Code” starts with 9
The second example that reads 1 OR 2 will expand the report results in the chance that the State or the Postal Code is missing or incorrect.
A field filter cannot function on related objects below the main object in the report. For example, if you are starting with a Contact level report type, you cannot filter on a field from a related Registration record, as Registrations are under Contacts. To pull records that exist under the main object on a report, we use cross-filters.
Cross-filters
Cross-filters can be used to include or exclude records in your report based on related objects. This means that in a Contact report type, you can filter based on the existence or absence of a Contact’s child objects, like their Registrations (Contacts with/without Registrations). When adding a cross-filter to a report you will select if you’re looking for data “with” or “without” the related objects to create either an inclusion or exclusion cross-filter.
Cross-filters are used to filter related records that exist under the main object on the report; a solution to what we outlined is not possible with a simple field filter. You can only apply a maximum of 3 cross-filters in a report, and cross-filters can only be used to limit a dataset (they can only be used together with AND; there are no OR options with multiple cross-filters). For example, you CANNOT achieve:
Contacts with Registrations, Event Year equals 2020
OR
Contacts without Registrations, Event Year does not equal 2020
How to use multiple cross-filters
Each cross-filter that is added to a report will read as an AND. Depending on the information you want to pull into your report use up to 3 cross-filters. Each cross-filter can be either an inclusion (with) or an exclusion (without) cross-filter. We will cover more examples of common mistakes with cross-filters and how to properly utilize them below.
Sub-filters
Use sub-filters to include or exclude records in your report based on related objects’ fields. A sub-filter is applied within a cross-filter. For example, if you created an inclusion cross-filter of Contacts with Registrations, you’ve limited your report results to only Contacts who have at least 1 related Registration record. Now, if you add an additional sub-filter to the cross-filter that says Event Year equals 2020, you’ve further limited the results of your report to only Contacts who have a Registration from an event in 2020.
If, however, your cross-filter was an exclusion cross-filter of Contacts without Registrations, and then you added a sub-filter of Event Year equals 2020, you’ve actually expanded your report results, since you’ve just limited the scope of the exclusion cross-filter.
How to use multiple sub-filters
Using multiple sub-filters is going to be different depending on if you’re expanding or narrowing the records of your report through a with or without cross filter.
Inclusion cross-filters
When adding multiple sub-filters to an inclusion cross-filter you are narrowing the results of your report. For example, if you’re using a Contacts with Registrations cross filter this will pull all Contacts who have Registrations. If you add a sub-filter for “Event Year” is equal to 2020, this will further narrow the results of your report to show all Contacts with Registrations for only the year 2020. Further, if you add another sub-filter for “Registration: Sub Event” is equal to Half Marathon the records pulled in your report will be further narrowed to only show all Contacts with Registrations for the Half Marathon sub event in 2020. When a comma is utilized in an inclusion cross-filter an OR relationship is created. For example, adding a sub-filter for “Event Year” is equal to “2019, 2020”, will pull records who have registrations in either 2019 OR 2020.
Exclusion cross-filters
When adding multiple sub-filters to an exclusion cross-filter you’re expanding the results of your report. Let’s review a few scenarios with the below data set:
- Contact A has registrations for Forest City 2019 and Kids Run 2019
- Contact B has a registration for Kids Run 2020 only
- Contact C has a registration for Forest City 2018 only
- Contact D has no registrations
For the above example, if you’re using a Contacts without Registrations cross-filter this will pull all contacts who do not have a registration, which will be a very limited number of records. Looking at the above data set, Contact D will be the only record pulled.
If you’re using a Contacts without Registrations exclusion cross-filter and a sub-filter for “Event Year” equals 2020 is added this will expand the results of your report to show all Contacts without Registrations in 2020. With the above data set, Contacts A, C and D will be pulled.
If you’re using a Contacts without Registrations exclusion cross-filter and a sub-filter for “Event Year” equals “2019, 2020” this will be treated as an AND expression; the report will pull contacts who are WITHOUT registrations in 2019 AND 2020. The above data set would pull Contacts C and D.
If you’re using a Contacts without Registrations exclusion cross-filter and a sub-filter for “Event Name” contains Forest City is added, this will expand the results to show all Contacts without Registrations for the Forest City Event. With the above data set, Contacts B and D will be pulled.
There are also times when double negatives arise with exclusion cross-filters. For example, if you’re using a Contacts without Registrations exclusion cross-filter and a sub-filter for Event does not contain “Forest City” this is a double negative. With the above data set, Contacts C and D will show up in the report, because they both do not have any registration where the event does not contain "Forest City".
Common mistakes with reports and how to avoid them
Why you cannot use multiple sub-filters for one field in the same inclusion cross-filter
Using multiple sub-filters under a single cross-filter for the same field is not possible because each sub-filter that’s used creates AND logic. For example, if you’re looking to see who is retained for both Forest City and Kids Races events from 2019 to 2020, the below would return 0 records. The use of two sub-filters for “Event” (one for Forest City and the other for Kids Races) creates an AND relationship. This report is trying to pull records where the event field contains Forest City AND Kids Races, which doesn’t exist.
Instead of using multiple sub-filters for the same field, the use of a comma is needed to create an OR within the inclusion cross-filter’s sub-filter.
Incorrect: (Contacts with Registrations where [(Event Year=2019)AND(Is Active=True)AND(Event contains “forest city”)AND(Event contains “kids races”)]) AND (Contacts without Registrations where [(Event Year=2020)AND(Event contains “forest city”)]) AND (Contacts without Registrations where [(Event Year=2020)AND(Event contains “kids races”)])
Correct: (Contacts with Registrations where [(Event Year=2019)AND(Is Active=True)AND(Event contains “forest city” OR “kids races”)]) AND (Contacts without Registrations where [(Event Year=2020)AND(Event contains “forest city”)]) AND (Contacts without Registrations where [(Event Year=2020)AND(Event contains “kids races”)])
Why you cannot use commas to list multiple values in an exclusion cross-filter
Within an exclusion cross-filter using commas will create an OR expression. If you were to list multiple event names separated by commas in the “Contacts without Registrations” exclusion cross-filter’s Event sub-filter, this creates an OR situation. This would allow a contact to show up on the report if they don’t have one of the two events. Instead of pulling retention data for participants who have not yet participated in Forest City AND Kids Races 2020 events, this would potentially pull data for contacts who have not participated in Forest City OR Kids Races 2020 events (in addition to those who haven’t participated in either event) which is not the goal of the report.
Instead of using a comma in a sub-filter within an exclusion cross-filter, add an additional exclusion cross-filter to create an AND.
Incorrect: (Contacts with Registrations where [(Event Year=2019)AND(Is Active=True)AND(Event contains “forest city” OR “kids races”)]) AND (Contacts without Registrations where [(Event Year=2020)AND(Event contains “forest city” OR “kids races”)])
Contact A has a registration for Forest City in 2019 and Kids Races in 2019, but also Forest City in 2020. Because a comma was used in an exclusion filter this created an “OR” situation and has segmented the data incorrectly and will pull Contact A into the report.
Correct: (Contacts with Registrations where [(Event Year=2019)AND(Is Active=True)AND(Event contains “forest city” OR “kids races”)]) AND (Contacts without Registrations where [(Event Year=2020)AND(Event contains “forest city”)]) AND (Contacts without Registrations where [(Event Year=2020)AND(Event contains “kids races”)])
Why you cannot use a list of years to learn who ‘skipped’ a year of racing
To learn what contacts ‘skipped’ a year of race participation you cannot list the Event Years that you’re interested in with a comma in the Contacts with Registrations, Event Year sub-filter. Using a comma creates an OR and pulls records where Registrations could exist for a single year listed or either year listed, but not both.
Instead you will need to use cross-filters to create AND relationships for the Event Years of data that you’re interested in.
Incorrect: (Contacts with Registrations where [(Event Year=”2018” OR “2019”)AND(Is Active=True)]) AND (Contacts without Registrations where [(Event Year=2020)])
Correct: (Contacts with Registrations where [(Event Year=2018)AND(Is Active=True)]) AND (Contacts with Registrations where [(Event Year=2019)AND(Is Active= True)]) AND (Contacts without Registrations where [(Event Year=2020)])
Practice makes perfect, even with reporting
Make sure that you’re clear on the goal of your report and the data that you intend to pull, then check that you have created the correct relationships through AND’s and OR’s. As with anything, the key to becoming a reporting expert is practice.
We recommend that you attempt to build your reports and if you would like another set of eyes to review, don’t hesitate to contact your Customer Success Manager.
Comments
0 comments