Admins / Analytics / Users

What are Cross Filters in Salesforce Reports?

By James Sullivan

Salesforce Cross Filters allow you to create a report that identifies records with or without a related record,where you have no need for the related object’s data (so you don’t need those related object’s fields displayed in the report).

A typical use case for Cross Filters in Salesforce is to identify a record WITHOUT another related record (also known as an exception report). Perhaps you would like 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 these reporting requirements using Cross Filters.

Cross Filters are a great way to level up your awesome admin skills, as Salesforce Cross Filters enable you to find essential information without having to create custom report types or rollup fields (therefore, keeping your org cleaner!)

What is a Salesforce Cross Filter?

Before we get started on how to use Cross Filters, 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 records, but related:

The Opportunity knows which Account it is related to using the Account Name field on the Opportunity. Here is what it looks like “under the hood” in the Salesforce Schema:

In this example, the Account is the parent object and Opportunity is the child object.

A Cross Filter goes across/down that relationship. In the image above, a Cross Filter will go 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 Salesforce

In this example, we’re going to look for Opportunities without Products. We will start with an Opportunity report. We’re going to add a filter based on Products associated with the Opportunity.

Note! The Cross Filter won’t allow you to display any fields from the child object, so if that’s what you want (ie. display Product field data), you need to use an Opportunities with Products report type.

1. 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 disabled (if 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.

2. 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.

3. 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.

Add Sub-filters to Salesforce Reports

Now that we have a list of Opportunities without Products, but you’ve just received further requirements.

We don’t want to pester users 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.

1. Add another Cross Filter for “Activities”:

2. Add an “Activity Filter” to retrieve Opportunities that have a related Activity with the Subject containing “Demo”.

To recap:

  • 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!

Salesforce Cross Filter Limitations

  • 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)

Summary

Salesforce 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.

The Author

James Sullivan

James is a founder and CEO at CloudAnswers.

Comments:

    Helena
    May 04, 2022 6:27 pm
    Can you add columns from the cross filter object to the report? For example, I have EVENT ANSWERS that does not show first/last name. I can cross filter on EVENT ATTENDEES where first/last name is located but I can not see an option to these fields to the report.
    Kasia
    May 08, 2022 8:55 pm
    Hi Helena, you cannot add columns from the cross-filter object. If you need to display cross-filter object fields then you need to start with a report type that includes those fields.
    Ralf
    May 25, 2022 6:07 pm
    Thank you James for this cross filter overview! Indeed, cross filters are a powerful feature, expanding reporting capabilities significantly when used in a thoughtful way. However, one important piece of information on cross filters is missing: You cannot set a filter logic on cross filters. Any conditions will be connected by AND. This applies for "without" and "with" filters. Example for a cross filter: Show me Opportunities without Products, Status = Active, Type = Contract would be interpreted as "Give me all Opportunities without Products that match the condition ( Type = "Contract" AND Status = "Active" )." This would exclude any Opportunities with Products that are of Type "Contract" and Status = "Inactive" or vice versa.
    Matthew Groves
    December 02, 2022 8:24 pm
    Those approaching from a SQL background think Having and Not Having.
    leeanne
    March 23, 2023 2:00 pm
    hi James, thank you this was really helpful, i do have a further question. Can we filter these by date? for example within the industry i work in we have a terminology of Right to Trade, And we would measure how much customers that have the right to trade with our customers that are not currently trading which works fantastic with the cross filter, however we would also measure this by last spend date for example if they have not spent in the last 3 months. The cross filter only shows all accounts that have no invoices related but can i add a filter to state no invoices within the last 3 months too?

Leave a Reply