Salesforce Reporting on Related Records by Parent Account

Updated on August 3, 2020

Do you ever work with customers who have multiple subsidiaries or divisions? If so, you might be stuck figuring out a way to make this work in Salesforce.

Don’t worry. We have a solution!

By using the out-of-box “Parent Account” field on the Account page in Salesforce, you’re able to quickly and conveniently link Accounts and display this hierarchy information in a visual format to see how records relate to each other. This works well on the Account object; however, the hierarchy functionality does have some limitations.

This picture shows a multi-level hierarchy with multiple parent and child records in the chain. A Salesforce user could access Accounts with multiple parent-child Account combinations in the list.

What happens if you want to run a report on all of the Contact records for the Accounts in the hierarchy for a mass email? Or what if you wanted to run a report of all of the Opportunities records for the Account chain to see the total value of the top-node parent Account? Unfortunately, Salesforce does not natively have this type of functionality, but with a little formula fieldwork, you can run reports for the use cases above and more.

We’ve quite often run into these Salesforce reporting issues ourselves here at StarrData and with our clients, so we thought we would share our solution and ideas with the broader community. By creating a Cross-Object Formula field, you can reference linked records up to 10 levels away.

The text formula we developed allows reporting on Contacts, Cases, Opportunities, (or other objects related to accounts) for the whole hierarchy. By using a custom link (click here to see our blog series on creating these) you can then place access to the report on all account pages. The report will use the custom formula to pull all related records in the hierarchy, no matter where in the hierarchy you click on it.

The picture above shows the report for all contacts in the hierarchy: The formula field we created is named “Parent Account Report ID” for clarification, but you can call it anything you like.

We also included the Contact ID in the report so you could see that each contact, anywhere in the hierarchy, has a unique ID. The rest of the columns in the report just demonstrate that all accounts are represented, as well as their associated contacts.

IF( ISBLANK(ParentId) ,  Id ,

IF( ISBLANK( Parent.Parent.Id ) ,  ParentId ,

IF( ISBLANK( Parent.Parent.Parent.Id ) , Parent.Parent.Id,

IF( ISBLANK( Parent.Parent.Parent.Parent.Id ) , Parent.Parent.Parent.Id,

IF( ISBLANK( Parent.Parent.Parent.Parent.Parent.Id ) , Parent.Parent.Parent.Parent.Id,

IF( ISBLANK( Parent.Parent.Parent.Parent.Parent.Parent.Id ) , Parent.Parent.Parent.Parent.Parent.Id,

IF( ISBLANK( Parent.Parent.Parent.Parent.Parent.Parent.Parent.Id ) , Parent.Parent.Parent.Parent.Parent.Parent.Id,

IF( ISBLANK( Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Id ) ,  Parent.Parent.Parent.Parent.Parent.Parent.Parent.Id ,

IF( ISBLANK( Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Id ) ,  Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Id ,

IF( ISBLANK( Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.ParentId ) , Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Id,

IF( ISBLANK(Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.ParentId ) , Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.ParentId ,   Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.ParentId

)))))))))))

Learn more about Salesforce managed services