When filtering reports, sometimes you want to retrieve records with or without other related records, but have no need for the related objects fields or data. A typical use case is to identify a record WITHOUT another related record, sometimes described as an exception report. Maybe you want to know which Opportunities don’t have any Activities this week or you are trying to find all Accounts that became customers last quarter. You can achieve this using Cross Filters.
Cross Filters are a great way to level up your awesome admin reporting skills, enabling you to declaratively find essential information without creating custom report types or rollup fields.
What is a Cross Filter?
Before we get started on how to use Cross Filters and examine some examples, let’s first define the “Cross” in “Cross Filter”.
In Salesforce and other relational databases, you have records and related records. You often have multiple objects related to one. Some examples are:
- Accounts related to Contacts
- Tasks related to Contacts
- Opportunities related to Accounts
- Leads related to Campaigns
In Salesforce, you see these relationships on every page with “Related” or “Related Lists”.
This means that we have two different types of records. In the example below, one is an Opportunity, and one is an Account; they are separate but related.
The Opportunity knows which Account it is related to using the Account Name field on the Opportunity.
In this example, the Account is the parent object and Opportunity is the child object.
When a Cross Filter is used, it is going across that relationship. More specifically, going down.
In the example image above, it is going down from Account to Opportunity. When you add a cross filter to a report, you are looking at the data in a Related List and filtering based on those child records.
How to Create a Cross Filter
In this example, we will start with an Opportunity report. We’re going to add a filter based on Products associated with the Opportunity. Keep in mind, the Cross Filter doesn’t require (or allow) you to display any fields from the child object, so if that’s what you want, you can do an Opportunities with Products report. In this example we’re going to look for Opportunities without Products.
First, create an Opportunity report and you should see this:
The majority of report types allow Cross Filters, but occasionally, the options are greyed out. Usually, this means that your organization has the option turned off because you are using Group or Professional Edition. Make sure you can see the “Add Cross Filter” action by going to the Filters panel and clicking the dropdown next to Filters. If you are following along with our example, click on it.
Now you will see a new section of filters pop up (you can have up to three!). The edit modal will prompt you to set the Parent and the Child.
As an example, Opportunities have Opportunity Line Items, Contact Roles, Quotes, etc. As another example, if the report were “Accounts With Cases”, then you would have the option of setting the parent as Account OR Case.
This is where the power comes in – you can select “With” or “Without”. This means you can find any record missing individual child records using the “Without” option.
This is exactly what we want to do in our example, so let’s continue making our Opportunities without Products report.
First, we need to select the relationship:
Now that we have the relationship set, we have the most basic filter: Show Opportunities that have no related Products.
The updated report of our Opportunities will now only display Opportunities without Products.
Now that we have a list of Opportunities without Products, we are pretty close to a really useful report, but we don’t want to pester people to create Products right away until they understand the client requirements and have completed a demo. So, we want our report to display only Opportunities without Products, but with ‘Demo’ Activities. Having completed a demo, our sales reps should be in a position to add Products.
First, we add another Cross Filter for “Activities”:
Then we add an “Activity Filter” to retrieve Opportunities that have a related Activity with the Subject containing “Demo”.
- The report type is “Opportunities”. We only have data and fields from Opportunity records in the report
- We added a Cross Filter to exclude Opportunities without Products
- We added another Cross Filter to only include Opportunities with a ‘Demo’ Activity: This way we know that our sales person should definitely know which products the prospect is interested in
So now, if an Opportunity doesn’t have Products but does have a Demo, they’ll show up in our report. In other words, this shows all opportunities that need a hand progressing through the sales process!
Limitations of Cross Filters
- There is a maximum of three cross filters per report
- There is a maximum of five sub-filters per cross filter
- Cross filters might affect performance and slow down your report
Last but not least, a few important details:
- You can have regular filters as well (like only showing “Open” Opportunities)
- This report type is great when you are looking for relationships that are missing (for example if there is no “Primary Contact” for Account)
- This Filter Type is not available for Group or Professional license types
- Make sure you use the correct fields (if you have two lookups from Opportunity to Account, then you need to find the right one through Object Manager in setup)
Cross filters are a powerful tool once you know how to use them. They unlock the ability to create reports that you used to have to do in another system. As with any report changes, make sure to check the report results and validate that it’s working before sharing any report or saving it in a shared folder.
Want to send these reports? Check out Report Sender to easily schedule these reports to any email address you want. Make sure stakeholders stay in the loop by sending them updates directly from Salesforce. In case you need help with any kind of report or want to talk Salesforce, contact us.