Salesforce Reporting on Related Records by Parent Account

By using the “Parent Account” field on the account page in Salesforce, you are able to link accounts and display the hierarchy from any account record (picture below) to see how your accounts relate to each other.

This works well for the accounts and allows you to quickly click into any related account but it does have its limitations.

The picture at left shows each parent and its child (or grandchild depending on where you are in the hierarchy). This is for demonstration purposes but you get the idea. You can have many child and grandchild accounts in the list.

What happens if you want to report on all of the contacts in the hierarchy for a mass email? Or how about a report of all of the opportunities in the hierarchy to see the total value of the parent?  Salesforce does not have this type of functionality but with a little formula field work, you can report on the above and more.

We have had these Salesforce reporting issues come across our plate quite often here at StarrData so we thought we should share our formula and ideas with the community. By creating a Cross-Objectformula 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 link 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 eleven accounts are represented, as well as their associated contacts.

Would you would like more Salesforce Best Practices?

StarrData is a Salesforce Silver Partner that helps companies deploy, optimize and support Salesforce.com. If you would like information on the services we offer call us at (888) 391-4493 Ext. 103.