This appears to be a report that is often needed, but without a simple solution. The same would be true of Accounts without Opportunities report, or Accounts without Cases report.
As it turns out, if you have Enterprise, Unlimited or Developer Edition there are a couple straightforward solutions!
One way is to create a Custom Report Type.
Here are the steps:
- From the Setup menu, open the Create section and select Report Types
- Click New Custom Report Type, select the primary object (in this case Account), fill in name, label and description fields, and select the category you want the report type to appear.
- Select “Deployed” so other users will have access to this report type and click Next.
- Click in the box that says “Click to relate another object,” select your second object and click “A records may or may not have B records.”
One thing to be aware of when looking for report types in order to create a report is custom report types are listed after standard report types. This means that you’ll need to scroll to the very bottom of the report type list (and possibly have to click Show more…) to see these options.
The other option is even easier – Utilize a Cross-Filter
Here are the steps to create an Accounts without Contacts report:
- Create a new Account report
- Click the little down arrow on the filter “Add” button to open the dialog box and choose “Cross Filter”
- Complete the filter criteria by selecting Contacts as your second object and click OK
On the slightly more technical note, the query that would produce the data set for type of report is called a Left Outer Join. In SOQL it would look something like SELECT Id, Name, (SELECT Id, Name FROM Contact) FROM Account.
- Knowledge Article Number: 000003804
- A Deeper look at SOQL and Relationship Queries on Force.com
- Success Community Discussion