Filter Opportunities with Contacts Report by Fields on Contact Lookup field

Question

I'm looking to create a report of Opportunities with some native Opportunity filters. These Opportunities also have a custom Lookup field pointing to a Contact: Project Contact.

In the Opportunities with Contacts report, I want to further filter by a custom field (a checkbox) in the Contact Object.

In the report I can access related Contacts for the Opportunities, but I can't access the fields of the specific Contact in the Project Contactfield.

Apologies if it's covered elsewhere - I have had a look at docs and elsewhere on SE, but none of it is ringing any bells thus far.

Any ideas?

Answer 1

It sounds to me like this calls for a Custom Report Type.

You'd have your objects as Opportunity and Contact, but you'd also use the Add Fields Related Via Lookup tool to bring in the set of fields that are specifically on that parent Contact, rather than the Opportunity's Contacts related via Opportunity Contact Roles. You can add those fields to a specific section in your Report Type.

You'd then be able to filter by Project Contact fields in any Report based on that Report Type.