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