Pages

Friday, February 28, 2014

How to count unique Account in an Opportunity report in Salesforce ?

Requirements background:
1. User would like to know how many accounts have closed won opportunity last quarter?
2. To show all opportunity related in point (1) above in the same report.

Analysis:
1. Create Opportunities report type with Summary report.
This approach maybe work, but if you have a lot of accounts, it is not a good solution to count number of account manually


2. Create Account report with Cross Filter with Opportunity
Using this approach, we can get number of Account, but:
- No information on Opportunity in the report
- We cannot get Opportunity Closed Date as filter or criteria


Solution: use the "Power of One"
Here you go:
1. Create a formula field in Account with return type = Number

2. Use this field in the report and Sum it

As you see in above report, it is exactly the same with the first report, but we just add a field to count. So, user can easily see that we have 8 closed won opportunity for 5 accounts last quarter.

You can use the same solution for other object related to Account, from: Case, Contact, Opportunity, etc and for other master-detail or lookup relationship objects.