More Useful Salesforce Reports with Cross Filters

August 26, 2016 Jonathon J. Leon Guerrero


It’s the beginning of a new quarter.

And that means it’s time to reorganize our Accounts. We need to select new targets and cut down the number of Accounts each rep owns.

My sales manager always has the same request. He needs me to identify which of our non-customer Accounts have an existing open Opportunity and which ones do not. That helps him make good strategic decisions as we define our new priorities going forward over the next three months.

Now how do I get that list? One way would be to run two separate reports — an Account report and an Opportunities report. Then I could compare them and cross off the non-customer Accounts that don’t have an open Opportunities. It would be a process of elimination . . . and a lot of inefficient work that might involve an Excel spreadsheet or pen and paper.

Or I could use a cross filter.

Salesforce defines cross filters as the ability to include or exclude records in your report based on related objects and their fields. It essentially allows you to choose a “with or without” function in your report. Cross filters work great when I need to run a report on, say, customer accounts without a renewal/upsell opportunity.

And it’s an simple way for me to provide the exact data my manager needs on those new-quarter account reports. So here’s how I use a cross filter to do that.


Go to the Report Builder of the specific Account report that you would like to add a cross filter. In the Filter section, click on Add to add a standard report filter. For this example we’re going to select Type in the first drop-down list, not equal to in the second drop-down list, and select Customer, Partner / Customer in the third drop-down list. This will limit the report to only show non-customer Accounts. Now, click on OK to add the filter.

New - Step One


In the Filter section of the Report Builder, click on the drop-down arrow next to Add and select Cross Filter.

The first drop-down list is where you will select a parent object. This determines which related objects you can see in the child object list. For this use case, we’ll select Accounts. The second drop-down list is where you can choose With or Without as your filter operator. In this example, we’re going to select With. In the third drop-down list, you’ll be able to select child objects. This list will only show child objects that are related to the parent object you just selected. So we’ll select Opportunities.

New - Step Two B

Because we want to only look at Accounts with Open Opportunities, we’re going to add a sub-filter to the cross filter.


Before clicking on OK, we’re going to click on Add Opportunities Filter. In the first drop-down list, we’ll select Stage. In the second drop-down list, we’ll select not equal to as our filter operator. In the third drop-down list, we’ll select Closed Won, Closed Lost, Trial — Active, Trial — Completed. Now click OK.

New - Step Two C


Now, you can easily see the non-customer Accounts that have Open Opportunities. And that gives my manager good information about what Accounts we should be prioritizing and maybe which ones should be dropped in the new quarter.

New - Result

Now that might sound pretty straightforward. But why over-complicate the process when it can be done simply? Besides, getting tasks like this accomplished quickly allows me to focus my efforts on other projects.



About the Author

Jonathon J. Leon Guerrero is a pre-sales solutions consultant and head of Sales Operations at LeanData.

Previous Article
Doug Landis at Box Preaches the Importance of "Engaged Selling Time"
Doug Landis at Box Preaches the Importance of "Engaged Selling Time"

Doug Landis was listing all the things that can occupy the typical day of a sales rep. Administrative tasks...

Next Article
When There's Too Much in the Technology Toolbox
When There's Too Much in the Technology Toolbox

It’s never a good feeling when you’re working on a job and you don’t have the right tool. But these days in...