Salesforce Report with Filter on Child object

In objects with parent-child relationship, we can run report with provided record type or create Custom Report Type. But when we would like to get all parent with and without child records, we need to create Custom Report Type and select "with or without related records from ...".

Since the fields from child objects are available, we can use it as Filter as well. But, issue arise when we would like to filter based on field in child object.

Use Case: We would like to show all Opportunities with Invoice Date from 1 Jan 2014 onwards for North region. Opportunity is the parent object and Invoice is a custom object with lookup to Opportunity, which is child object for this scenario.

Before add filter, let us see the whole data:

Notice we have 2 opportunities here without Invoices and 1 opportunity with 2 invoices. Now, let's add filter Invoice Date greater or equal "1/1/2014". This is the result

We do not expect the last 4 opportunities in the report because they are no longer fit the criteria, we just would like Opportunity with invoice date 1 Jan 2014 and onwards.

Let's analyse, since we are using report type, "with or without related records":

• Safire Z and Maxima 2A do not have invoice, but since out report type is with or without related records, both opportunities here is fall into WITHOUT related records, the filter Invoice Date greater or equal "1/1/2014" is not effect.
• Edge Emergency Generator and Edge SLA 2 have invoice, but the date do not fit criteria set, so the invoice is filter out, but the opportunity itself not, so it treat the same as above Opportunity without invoice.

Let's add cross-filter said just show Opportunities with Invoices.

Using cross-filter, we able to filter out Safire Z and Maxima 2A because they do not have invoice. But, Edge Emergency Generator and Edge SLA 2 will stay because have invoice, although it do not fit criteria of Invoice Date filter.

Solution

Instead of using custom report type "with or without related records", you need to use report type where Opportunities "with at least one related" Invoices.

Reference: Why do records appear when I filtered them out on a with or without report type?