I am trying to show team Hierarchy like User and Manager - by using User and Manager lookup join in recipe I am able to get only immediate(direct) reporting and opportunity amount rollup. I need to show rollup based on hierarchy top to bottom such as EmpM1 ->EmpM2 -> SalesRep1 and SalesRep2,SalesRep3 and SalesRep1 > SalesSubRep1 , SalesSubRep2, SalesSubRep3,SalesSubRep4
This is my Expectation
EmpM1 - 8
EmpM2 - 7
SalesRep1 - 4
I am getting from my recipe
EmpM1 - 1
EmpM2 - 3
SalesRep1 - 4
Can any one hep how to achieve this requirement ?
Did you try flatten transformation (not a lookup join)?
If not, try this
Add a Flatten node in your recipe (or dataflow) on the hierarchy, using the User Id as the self ID and Manager Id as the parent. Flatten outputs a multi-value roll-up hierarchy field containing every ancestor for each user.
Then join your opportunity data to users. When you group by that flattened ancestor field instead of the direct manager field, each opportunity counts toward every manager in that user's chain.
Basically, don't roll up by 'who's your manager' instead, roll up by 'who's anywhere above you' Flatten gives you that second thing as a queryable field, and grouping on it produces the top-to-bottom totals you're expecting.
Hope this works. thanks!
