Account Hierarchy Report!!! How does one do it?

[cpierre]

Rather than viewing Account Hierarchy from an individual account page, we would like to create a report that presents this information.

We have tried until blue in the face and are not sure how to accomplish this, Salesforce "Premier" Support says  - from what they see it can not be done, but that we could check the community to see if someone found a work around  (Hmmm -Why do we pay for premier support?)

Anyone out there with a trick up their sleeve?

[Jakester]

Do you have more than 3 levels of hierarchy?

[cpierre]

No. 3 is the most we have, the majority of accounts are 2 levels

[Jakester]

We were in the same situation, and the way I handled it was to create two fields on the Account record. One called parent and the other called Grandparent. The login on both them says "check to see if you have a parent/gp. If so, enter it. If not, enter the highest level in the hierarchy." For, if you have company a whose parent is company x whose parent is company 1, then on company a's record the parent name field gets company x and its grandparent field gets company 1. However, since company 1 has no parent, on company x's parent field it gets company 1, but the grandparent field also gets company 1. And on company 1's record, both the parent and gp fields are also company 1. Once you set this up, you can do a Summary Report that groups by gp, then parent, then account, and you can hide the details of the report.

If all of that makes sense, and you're still interested, I can talk about how I got the parent and gp fields populated with that logic.

[cpierre]

Great!...sounds doable, its clicking.   Please continue....

[Jakester]

Cool-

In theory this is doable with Apex triggers, but I'm not that smart. If anyone figures this out, I would love to use their code. What I did was buy DBAmp. It's a very inexpensive yearly rate and it is able to replicate your data down to a local SQL database and then run scheduled jobs there that will update your data directly in Salesforce. It does require having a local SQL database, though - hope you have one...

Once that's set up, I have a job that runs every 30 mins that updates all of these fields. Before I show the code, I should explain that our small stores (called Tier 4) all get a grandparent named Tier 4 so that the grandparent roll-ups "hide" the small stores. You could obviously remove that code. Here's the code:

SELECT    a.id as accountid, coalesce(parent.name,a.name) as Parent, a.billingpostalcode, a.shippingpostalcode,
case when a.market_segment_uk__c = 'Tier 4' then 'Tier 4' else coalesce(gp.name, parent.name, a.name) end as GrandParent


into #parent_load
FROM       account a
	left join account parent on a.parentid = parent.id
	left join account gp on parent.parentid = gp.id


where a.parent_name__c is null or a.grandparent_name__c is null or a.parent_name__c <> parent.name or a.grandparent_name__c <> case when a.market_segment_uk__c = 'Tier 4' then 'Tier 4' else coalesce(gp.name, parent.name, a.name) end

update salesforce...account set parent_name__c = pl.parent
from salesforce...account a 
inner join #parent_load pl on a.id = pl.accountid 

update salesforce...account set grandparent_name__c = pl.grandparent
from salesforce...account a 
inner join #parent_load pl on a.id = pl.accountid 

drop table #parent_load

 

[csidesinternap]

Great stuff, I have exactly the same issue.

Here is my question though. For your parent and grandparent fields, it appears you are storing the account.id (i'm not well versed in SQL so forgive my ignorance).  Did you set these fields up as lookups on the account object?

I tried creating a lookup relationship field based on the account object but when you try to select the same account as the current record (as in the case where an account doesn't have a parent and is therefore its own parent), I get an error message that says you can't reference the same record.