I'm trying to create one report (to be as a mailing list) that combines Opportunities with Accounts and Contacts but I can't find a way. I need all the Contacts for Accounts which have Opportunities on them where the Opp stage is Trial.

Any ideas.

Thanks

Attribution to: Dedo

Possible Suggestion/Solution #1

You can do it with SOQL, not so easy with reports. Reports aren't great when you want to go into 2 different "branches" (from Account to Contacts + from Account to Opportunities).

Joined report might be OK but will show you a lot of "false positives" (only the opportunity-related side will respect your filter by Stage). This might change in later releases, after all in the beginning joined reports didn't support charts or exporting to excel...

Can you consider fixing your data so for example once Opp is in Trial state all contacts from Account are linked to it via OpportunityContactRole? This will probably require some triggers in order to maintain...

If that's also not a valid solution - you might be able to pull it off with some kind of analytic snapshots?

SOQL could be as easy as

SELECT FirstName, LastName, Email, Account.Name
FROM Contact
WHERE AccountId IN (SELECT AccountId FROM Opportunity WHERE StageName = 'Trial')

Attribution to: eyescream

Possible Suggestion/Solution #2

You could do this with Custom Report Types (CRT). Create a CRT based on Contacts that have at least one Opportunity. When you build a report on that type you can than filter on the Opportunity Stage and the Contact's Account.

Attribution to: Mike Chale

Possible Suggestion/Solution #3

Create a Roll-Up Summary field on Account that gives you the COUNT of Opportunities where Stage = 'Trial'.

Then your report will be on Accounts and Contacts, where Account.Count_of_Trial_Opps__c > 0.

Attribution to: Jeremy Nottingham

Possible Suggestion/Solution #4

You can create a custom report type with a primary object Contacts with Opportunities, and filter by Opp stage.

Attribution to: Tom C
This content is remixed from stackoverflow or stackexchange. Please visit https://salesforce.stackexchange.com/questions/5079