Print this page

Report filter not bringing in the correct results on a WITH or WITHOUT Custom Report Type

Knowledge Article Number 000004402
Description If a report includes a filter on a child object field, the filter will only work for the records of the parent that have a record on the child object. 
For example: If you navigate to Setup | Create | Report Types and have a Custom Report Type using a relationship such as the following:

Contacts object.
with or without related records from Opportunities (B).

Filtering on the Opportunity Object is not allowed for this report.

If you use the Filter in report: Opportunity Stage equals to Closed Won, the results in the above report type will display the records based on the filter and other based on without opportunity records per the report type.

This is expected behavior. 

Good to know: You can physically add a filter on one of the Child Objects, however the report will return incorrect results. Meaning your expected results will not populate even if you are attempting to filter out those records completely.

Try a Workaround

In order to get only the records of the Parent Object with at least one Child Object's record, you need to change the Custom Report Type definition and use the "with at least one record" logic. You can also:
  • Add a Cross Filter using the Filter Logic.
  • Use Multiple reports based on different report types in preparation for Export to Excel to use VLOOKUP functions.


promote demote