I discovered today with custom reports that have 'with' relationships that records 'without' related records are being returned.
Custom Report Type:
Accounts with Memberships
When I run this report, if I only pull in fields from the Account object, Accounts without Memberships are showing in the report. These accounts do get listed as unique rows.
As soon as I add a field from the Membership object, then only Account With Memberships show up.
I have not found this to be the case with standard report types.
E.g. Standard report type of Opportunities with Products does not show this behavior. If I run this report type and only add Opportunity Name, then the opportunity name will show multiple times(once per Opp Product) but opportunities without products will not show.
Have others experienced this? Is this known behavior? I have always understood report types to be literal and this can be deceiving if when utilizing a 'With' report if it will actually show 'without' records.
Steps to recreate:
- Create a Custom report type of Object A with Object B
- Create a new report using the newly created report type Only
- add a field from Object A to your report preview. You will find records returned that do not have related Object B records.