(NB: As per comments, this is specifically a technical question around Salesforce reporting capabilities.)

Reports using the standard report type "Opportunities with Products" will include Opportunities that have no related Products. But the report can be filtered on Products fields and then only the opportunities with products matching that filter are included in the result. You can even filter a Products field equals "" and the report will list all the Opportunities with no Products.

This means (in a relational database sense) that Opportunities are being left-joined with Products and then filters are being applied.

I want to do something similar with a custom Report Type and so far have been unable.

If I create a new Report Type with primary object Opportunities object and related object Products I have two options:

  1. "Each Opportunities record must have at least one related Products record" => resulting reports will not show Opportunities if there's no related Product at all (unlike the standard report). In a relational database sense, this is an inner join not the desired left join.

  2. "Opportunities records may or may not have related Products records" => in resulting reports if I put a filter on Products fields then every Opportunities record is still included in the report (unlike the standard report). In a relational database sense, Opportunities are being left-joined with the pre-filtered Products result.

How do I make a custom Report Type that will show me all Opportunities records but let me naturally apply a simple Products field post-filter to the joined result, as per the standard report?

  • What are you trying to achieve in terms of business outcome, what would a consumer of this information be looking for?
    – identigral
    Aug 21, 2022 at 22:11
  • @identigral thank you for considering looking at solving our business issue a different way. My business users would be best served by having an available report type that looks a lot like the standard report but ropes in various related fields by lookup that are not currently available, which includes Opportunities both with and without Products, where the result set can be post-filtered by fields of the Products.
    – Joe
    Aug 22, 2022 at 0:38
  • 1
    You're describing a solution to an unstated problem - see XY problem. Here's a solution - 2 reports, each with locked filters that deals with pre-conditions such as having or not having products.
    – identigral
    Aug 22, 2022 at 1:17
  • I'm asking for technical help to achieve the most parsimonious solution. Salesforce can do it for standard reports, I would like to replicate it for custom reports... if possible. (Or a clear statement that it's known not to be currently doable.)
    – Joe
    Aug 22, 2022 at 9:51
  • Welcome to SFSE. Please take a moment to read How to Ask and take the tour. Have you tried adding a Has Products filter to your left-joined custom report type? Aug 22, 2022 at 16:31


You must log in to answer this question.

Browse other questions tagged .