Hey all! A while since I posted a time saver – not because I didn’t want to though! Just been a busy six months. Have a few time savers that people have told me I should pass along. Here is the first…I’ll post the others as time allows!
Some clients we have use Account hierarchies extensively. It’s a great way to break out the structure of large organizations you work with. When teamed with the force.com labs Appexchange app Inline Account Hierarchy it is very powerful. However, when you have more than one level of hierarchy, reporting can become an issue. I had two clients in particular -where this became troublesome. The two needs were:
1) Client A wanted a button that would list out all contacts for that company AS WELL as all the contacts with the other organizations in the account hierarchy. The hierarchy level went four deep.
2) Client B wanted an opportunity report that will combine all the opportunities in a report, sorted by parent account. Like Client A, they also had four levels of account hierarchy set up within salesforce.com.
Now, salesforce.com does have the “Parent Account” field. This allows you to state who the actual account’s parent is. However, in the case of my clients, this would not produce the desired results. By reporting on Parent Account, you would not have a full picture of the whole hierarchy, only the level above you (especially if you happened to be a grandchild or a great-grandchild in the levels).
To fix this issue, I created a hidden formula field. What I wanted to do is, on any account, figure out what the top level account was for the hierarchy it was part of. To do this, it needed to look above until it found the account that did not have a Parent Account set. To complete this, I created a formula field (text) called Top Level Account with the following formula:
IF (NOT(ISBLANK(Parent.Parent.ParentId)), Parent.Parent.Parent.Name, (IF (NOT(ISBLANK(Parent.ParentId)), Parent.Parent.Name, (IF (NOT(ISBLANK(ParentId)), Parent.Name, Name)))))
The formula is created for a hierarchy with a maximum of four levels – theoretically you could add more (NOT(ISBLANK(Parent.Parent.Parent.ParentId)) to the formula at the beginning to meet your needs (if you go to five, six or seven levels). And with this field now populated on the Account, the clients have easy access to the highest level account in the account hierarchy. How did this help the clients?
1) Client A now could create a report which was a contact list, showing all the information they wanted (including account), and could summarize to list all contacts based on Top Level Account. We then placed a custom button on the account page so they have quick and easy access to this contact list for whatever account they are on.
2) Client B could now do a simple opportunity report by Top Parent Account, showing the full opportunity amounts across the hierarchy in one fell swoop, giving their executive the information they were looking for in an instant!
Hope this time saver helps! Enjoy the cold week, and keep safe!
Nik
PS: Spring ’11 rollout finishes this weekend. You can read the release notes here!
3 Comments
This is really interesting to me. Where did you put this formula? Was it in the report or on the account page?
Hi David,
The formula was placed on the account page itself.