A cross filter keeps or removes parent records based on whether related child records exist. “Accounts without Opportunities” is the canonical example and it is the one you cannot build any other way in a standard report. Behind it sits a subquery, and the WITHOUT form is an anti-join, which is the most expensive shape of query the platform runs. Three cross filters per report, five subfilters each.
What a cross filter does
Open a report on Accounts, add a cross filter, and you choose between “with” and “without” a related object. The report then returns only accounts that do, or do not, have at least one matching child record.
Subfilters narrow what counts as a match. “Accounts without Opportunities” where the opportunity subfilter says “Stage equals Closed Won” returns accounts that have never won anything, including accounts with fifty open deals. That distinction matters and is easy to get backwards: the subfilter defines the child records being looked for, and the with or without setting then asks whether any exist.
The subquery it becomes
A cross filter compiles to a semi-join or an anti-join. The WITH form is roughly this:
SELECT Id, Name FROM Account
WHERE Id IN (
SELECT AccountId FROM Opportunity
WHERE StageName = 'Closed Won'
)And the WITHOUT form flips the operator:
SELECT Id, Name FROM Account
WHERE Id NOT IN (
SELECT AccountId FROM Opportunity
WHERE StageName = 'Closed Won'
)Seeing it written out explains the behaviour. The child object is queried in full, subject to its own subfilters, and the parent set is then tested against that result. There is no index on absence. To know that an account has no matching opportunity, the database has to establish that none of the opportunities match, and that work scales with the opportunity table rather than the account table.
Performance, concretely
Three practical rules.
Make the parent set small first. The cross filter is applied alongside your other filters, and a tight date range or an indexed filter on the parent reduces the set the subquery has to be evaluated against. Adding a cross filter to an unbounded report is the reliable way to produce a timeout.
Make the subfilters selective. A subfilter on an indexed child field restricts the inner query. A subfilter on a formula field does the opposite, because the formula has to be computed for every candidate child row.
Prefer WITH over WITHOUT where the question allows it. Sometimes it does. “Accounts with no activity in 90 days” can occasionally be rewritten as a filter on a last-activity field that already exists on the parent, which turns an anti-join into an ordinary indexed filter. When a field like that exists, use it.
Where none of that is enough, the answer is a real field. A checkbox on Account maintained by automation, “Has Won Opportunity”, is computed once when things change rather than recomputed on every report run. It costs a field and some automation, and it converts the most expensive filter in reporting into the cheapest.
What cross filters cannot do
The restrictions are as important as the capability:
- No columns from the child object. A cross filter filters. It does not join. You cannot display the opportunity name in an account report because a cross filter mentions opportunities. If you need child fields on screen, you need a report type that includes the child.
- Not part of filter logic. Cross filters sit outside the numbered filters and are always combined with AND. You cannot express “accounts with opportunities OR accounts in this region”.
- Three per report. Combined with the AND-only rule, that is a tight budget.
- The child must be related to the report type’s primary object. Cross filters follow existing relationships. They do not create one.
- Not available in every report format. Joined reports in particular do not offer them inside blocks.
Frequently asked
Can I use two cross filters on the same child object? Yes, and it is a reasonable way to express “with an open opportunity but without a won one”. Each is evaluated independently, so you are paying for two subqueries.
Why does my cross filter return records I expected it to exclude? Almost always the subfilter. Check whether the subfilter describes the child records you want to find, rather than the ones you want to avoid. The with or without switch handles the negation. The subfilter should be written positively.
Do cross filters respect sharing on the child object? The running user‘s access applies throughout, so a child record the user cannot see does not count as existing. This produces different results for different users on the same report, correctly.
