You may have come across cross filters when learning about Salesforce reports but having no immediate need for them, forget about them. It is only sometime later when faced with a reporting challenge that you may delve back and find that they are very useful and may even solve a problem that you thought you could not do with Salesforce reports.

Lets review what reports with cross filters can do:

• Define a filter to only show parent records that have a related child record
• Define a filter to only show parent records that do not have a related child record.

For example, you may have a request to create a report of Accounts that have had no activity in the last 12 months. Initially you may think that this is difficult to do with Salesforce, to report on what doesn’t exist. Usually you are reporting on all records or records that meet a certain filter criteria. With cross filters, creating a report of accounts with no activity in the last 12 months is extremely easy.

1. Create a new report based on the Accounts report type
2. Add a cross filter for Accounts with Activities
3. Add an activity filter using Created Date greater or equal LAST 12 MONTHS

Here is what the filters will look like:

Salesforce Accounts with no activities in the last 12 months

Cross filters work on related custom objects as well. In the following example, a custom object ‘Projects’ has been created and is related to Accounts. A cross filter can be used to report on Accounts that have incomplete projects.

Salesforce Accounts with incomplete projects

It could also be used to find accounts that didn’t have any projects created in the last 12 months.

Salesforce Accounts without Projects within the last 12 months

It is also possible to combine multiple cross filters. In the example below, the report will list any accounts without Activities, Cases and Opportunities.

Salesforce Accounts with No Activities Cases Opportunities

Some things to be aware of :

• You can define up to 3 cross filters in a report
• Each cross filter can have up to have subfilters
• Be aware of the report type you are using for the report. For example if you would like a report of Accounts with no activities, and you use the report type ‘Accounts with Activities’ you will not get any results.

So here are some examples of how you could use cross filters:

• Accounts with no Activities in the last 12 months
• Accounts with no Opportunities created in the last 12 months
• Accounts that haven’t ordered a certain product in the last 12 months (if you bring order history into Salesforce)
• Opportunities with no Activities, Competitors, Products, Contact Roles (e.g. as a quality check on the data)
• High Priority Open Cases with no Activities in the last week
• Cases with no case team members or case comments