(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:
"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.
"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?