Need some help in creating a report which will provide all oppty which has product1 and product2 in their opportynity line item. Product 1 and Produc 2 will be an AND condition so that it only shows opportunity which has the two products only.

If any oppty has only product1 , it shouldnot show in the report and similarly if any oppty has product2 , it should not show in the report. The oppty which has both the products should be only returned in the report.

I used the report type as Opportunity with products

I tried to create two filters with product name contains 'Product1' and Product name contains 'Product2' but once I ran the report it returned nothing. This filter is not checking both the rows it checks against one row line item record.

Any idea how to proced in this scenario.

1 Answer 1


This query is not possible using Reports

The workaround is to have use Roll up Summary (RSF) fields (or you can use DLRS) on Opportunity that counts the presence of child OpportunityLineItem of specific Products. Then you can filter on Opportunity fields

Hack-y solution that works in some use cases:

DLRS allows for fields to be concatenated into a string so as long as the product names are short and there aren't too many products per Opportunity, you could, in many cases, simply DLRS roll up the productnames into a concatenated field and then query where ConcatenatedField__c contains 'P1' and contains 'P2' and Product Count = 2 (another RSF)

  • Thanks ! I will look at the DLRS , I haven't worked on that earlier. I will check that out
    – Quddus
    Jun 24, 2019 at 21:29

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .