Salesforce Report Challenge: Total of Opportunities for All Accounts Within a Hierarchy

I received a report request today that sounded something like this: I need to get a total of all won opportunities for any account in this account hierarchy, regardless of whether we worked directly for them or were subcontracted*.

Sounds simple, right? 

In theory, yes. But, if you happen to have a complicated account hierarchy (eg: you have any federal government accounts), this is not an easy task. In fact, it's not natively possible to run a report like this in Salesforce.

In my case, I was looking at 80+ accounts organized in a hierarchy that was five levels deep. 

Rather than tell the requestor that the report was impossible, I decided to get a little creative.

Step 1: Get a list of all the accounts to include

I clicked on the [View Hierarchy] link next to the account name. Because I had multiple parent/child relationships, I scrolled until I found the account name that I was looking for within the hierarchy. Then I highlighted all the accounts underneath that, copied and then pasted them into Excel.

This is what my spreadsheet looked like:

At this point, I needed to extract the record IDs from the account name url. I remembered blogging about this before and read through the old post to remind myself how to extract the record ID from a hyperlink.

My spreadsheet then looked something like this:

Step 2: Generate a list of record IDs to use as a report filter

After I extracted my record IDs, I created a formula to concatenate the record IDs into a format that I could use as my report filter. (I demonstrated this method in a previous post.)

Step 3: Create a report and add the filter criteria

I created the report in Salesforce and pasted the concatenated IDs in my filter. Since I wanted to filter by two account fields on the opportunity, as well as by the parent relationship, I had to add those three fields to my filter. (Note: Several of these fields are custom formula fields based on the lookup to the Account object.)

Because the filter criteria can include only so much text, I split the record ID list into two filters for each field that I wanted to apply the filter to.

Here's how it looked:

Final Thoughts

I'd much prefer an approach that is more dynamic, rather than hard-coding the record IDs to generate a report. But for now, it does what I need it to do.

If you'd like to see some improvements to functionality surrounding parent/child accounts and account hierarchy functionality, vote up these ideas on the IdeaExchange: Add more account hierarchy functions for parent/child accounts, Report on Account Hierarchy

Have you ever received a request for a Salesforce report that stumped you? Or do you have an idea to improve my workflow? I'd love to hear your suggestions and stories in the comments below.

*We have a Secondary Account custom lookup field so that we can track not only the direct client the opportunity is for, but also the account that may have hired them (when we are operating as a subcontractor).