How to exclude accounts or contacts from a report if they haven't purchased a specific product? - Answers - Salesforce Trailblazer Community
Trailblazer Community
Ask Search:
Morgan MarcheseMorgan Marchese 

How to exclude accounts or contacts from a report if they haven't purchased a specific product?

Hi everyone,

Scenario...

Customer 1 has purchased: Headphones, Speakers, Microphone
Customer 2 has purchased: Headphones, Speakers

I am trying to create a report that will give me a list of Accounts with Products for people who have purchased headphones, but have not purchased a microphone. Problem is that I can't use Product Name does not contain "Microphone" in my report filters because that just returns all products for each account excluding any that contain the word microphone, which of course still returns both customers and just doesn't show Customer 1's Microphone product in the report.

Ultimately, I can't find another way to do this, other than this post I found last night regarding rollup filters:
http://salesforce.stackexchange.com/questions/14354/how-to-get-a-report-of-accounts-without-a-certain-product

Is that really the only solution? We have 280+ products right now and a marketing department that enjoys getting very, very granular in their target audience. I could have times where they want to know if a customer HAS purchased A, B, C, and/or D, but has never purchased X, Y, or Z... or whom haven't purchased X, Y, or Z in the last 12 months. Is there truly no better solution other than me considering creating 280+ individual rollup fields? To me, this seems like pretty common need for good product marketing, how do other businesses handle this hurdle?

Thanks for your time!
Geoffrey FlynnGeoffrey Flynn

Hi Morgan,

Are Accounts directly related to Products?  Meaning you don't have Opportunities between them?

If so you can use an Accounts report type with a Cross-Filter
https://help.salesforce.com/HTViewHelpDoc?id=reports_cross_filters.htm&language=en_US (https://help.salesforce.com/HTViewHelpDoc?id=reports_cross_filters.htm&language=en_US)

I don't have that exact relationship in my org but here's what it would look like with Accounts and Contacts
User-added image

William TranWilliam Tran
Morgan, there are two sides to each coin :-)

If you can't find a solution one side look on the opposite side.

So to accomplish what you want you need to do this:

Needs:
Get list of Accounts for people who have purchased headphones, but have not purchased a microphone.

Solution:

Use Filter Logic (click the down arrow next to "Add"):

Put this in logic: 
NOT (1 OR 2)

Criteria 1: Product not equal to headphones
Criteria 2: Product equals microphone

Save and run.

You can change equals to contains if you want. Equals performance is better than contains so use equals if you can.

Thx
William TranWilliam Tran
Morgan, 

Have you had a chance to try out the formula?

Thx
Lauren SpiteLauren Spite
HI William, What do you mean by "NOT (1 OR 2)"?