Here's a Formula I built to create an "Ultimate Parent Account" field that you can use to create Opportunity Pipeline reports that roll up all Opportunities under the top Account in the hierarchy.
*** in this example I'm testing up to a 5 Tier Account Hierarchy (Compiled size: 342 characters) ***
Datatype: Formula
Result: TEXT
Formula:
BLANKVALUE(Parent.Parent.Parent.Parent.Parent.Name,
BLANKVALUE(Parent.Parent.Parent.Parent.Name,
BLANKVALUE(Parent.Parent.Parent.Name,
BLANKVALUE(Parent.Parent.Name,
BLANKVALUE(Parent.Name,
Name)))))
Ultimate Parent Account Tier
Datatype = Formula
Result = Number
Formula =
IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Parent.Name)), 6,
IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Name)), 5,
IF(NOT(ISBLANK(Parent.Parent.Parent.Name)), 4,
IF(NOT(ISBLANK(Parent.Parent.Name)), 3,
IF(NOT(ISBLANK(Parent.Name)),2,
1)))))
Ultimate Parent Account Type
Datatype = Formula
Result = Number
Formula =
BLANKVALUE(TEXT(Parent.Parent.Parent.Parent.Parent.Type),
BLANKVALUE(TEXT(Parent.Parent.Parent.Parent.Type),
BLANKVALUE(TEXT(Parent.Parent.Parent.Type),
BLANKVALUE(TEXT(Parent.Parent.Type),
BLANKVALUE(TEXT(Parent.Type),
TEXT(Type))))))
Marrying Ultimate Parent ID + Ultimate Parent Name with levels can create some great reporting. With the below, you can see all levels of the hierarchy and what they are bringing in from a forecast perspective. Ultimate Parent ID:
BLANKVALUE(Parent.Parent.Parent.Parent.Parent.Name,
BLANKVALUE(Parent.Parent.Parent.Parent.Name,
BLANKVALUE(Parent.Parent.Parent.Name,
BLANKVALUE(Parent.Parent.Name,
BLANKVALUE(Parent.Name,
Name)))))
All hierarchy level names:
IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Parent.Name)),
Parent.Parent.Parent.Parent.Parent.Name & " - " & Parent.Parent.Parent.Parent.Name & " - " & Parent.Parent.Parent.Name & " - " & Parent.Parent.Name & " - " & Parent.Name & " - " & Name,
IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Name)), Parent.Parent.Parent.Parent.Name & " - " & Parent.Parent.Parent.Name & " - " & Parent.Parent.Name & " - " & Parent.Name & " - " & Name,
IF(NOT(ISBLANK(Parent.Parent.Parent.Name)), Parent.Parent.Parent.Name & " - " & Parent.Parent.Name & " - " & Parent.Name & " - " & Name,
IF(NOT(ISBLANK(Parent.Parent.Name)), Parent.Parent.Name & " - " & Parent.Name & " - " & Name,
IF(NOT(ISBLANK(Parent.Name)),Parent.Name & " - " & Name,
Name)))))
Note that we can't use BLANKVALUE as the base for the formula because "-" would be not blank for each level. Thus using the nested IF(). I'm sure we can optimize all the same to reduce compile size.
