One of the things that Salesforce allows for managers to do is to see rich reports in their subordinates work. Well, what if it could be used to identify areas that need attention sooner and point out areas of success for celebration. This post is aimed to do just that and with not one, but two formulas. Let me start off by saying that these formulas and reports are aimed highly at those that have larger structured organizations and those that manage several groups of people (managers of managers, one might even say).
These reports that I have made are out to answer two questions:
1. What are areas of success and improvement for sales teams?
2. What are the products that people are good at selling or what products can we help them improve on selling?
In the below example, we can see a few things:
1. That many of the future opportunities are stuck in the prospecting stage and may need help going along.
2. That Bruce’s direct reports have a sizable pipeline in amount and have a good close rate, but why is their pipeline smaller than Steve’s reports? Are they not getting big deals? Is it a product of the business that they work?
3. That Steve’s direct reports deserve a pizza party!
From this report, we can see a few things:
1. That not many opportunities have a product attached. Whisper: ** you can add a validation for that Mr. / Ms. Administrator **.
2. That Steve Roger’s reports best selling product is of the Executive product and their worst is our web product (with only one record in each group, but you get the idea).
Onto the magic, the formula to make it happen!
The first formula, for the “Owner / Team” field can be broken down into two components:
1. The part that determines if the current user is the owner for the opportunity.
2. The part that determines the level of the opportunity owner from the manager in relation to the running user.
The formula for that is:
/* Determine if the opportunity owner is the current user */ IF(OwnerId = $User.Id, "Owner", /* Else, start determining the org level via the manager IDs */ IF( Owner.ManagerId = $User.Id, "1", IF( Owner.Manager.ManagerId = $User.Id, "2", IF( Owner.Manager.Manager.ManagerId = $User.Id, "3", IF( Owner.Manager.Manager.Manager.ManagerId = $User.Id, "4", IF( Owner.Manager.Manager.Manager.Manager.ManagerId = $User.Id, "5", IF( Owner.Manager.Manager.Manager.Manager.Manager.ManagerId = $User.Id, "6", IF( Owner.Manager.Manager.Manager.Manager.Manager.Manager.ManagerId = $User.Id, "7", IF( Owner.Manager.Manager.Manager.Manager.Manager.Manager.Manager.ManagerId = $User.Id, "8", IF( Owner.Manager.Manager.Manager.Manager.Manager.Manager.Manager.Manager.ManagerId = $User.Id, "9", null))))))))))
Now, for the second field for determining manager name.
1. Create a formula field on the user record called “Manager Name”:
Manager.FirstName & " " Manager.LastName
2. Create a formula field on the opportunity:
It goes without saying that this concept can apply to all sorts of reports, cases, account distribution (who has the most or least accounts?), activites. The list goes on and on. Use your imagination my friends.
Go forth and make your matrix reports and while you’re at it, hit the Easy button. You deserve it.