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.

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.