Don't know about server side setting at all, but tried to show some conceptual logic only.
If you have more layers of reporting, self join at very first step can be multiplied.
reporting_tree_SM_10.5.twbx 11.5 KB
Based on example table you have given and I assume username function will return employee id i.e. 100 , 101 etc .
Then i will create below calculated field and filter it for true.
Hope this helps
if username() = emaloyee id and manager id is null then TRUE
else if username() = emaloyee id OR sername() = manager id then TRUE
Thanks for the reply. So are you saying that if we have multiple levels I would have to join all levels? Let's assume I have 7 levels. I would have to join 7 times to get the functionality I am looking for? I as hoping for something a little more dynamic as having 7 left joins I think will kill performance.
Not sure multiple join really result in bad performance.
But as long as I know, this process is needed to create hierarchy pattern.
And I am not sure the server setting side, so there might be completely different approach.
You can throw the post in this site where server/admin specific question are more discussed.
Based on your requirement and the table structure you mention above, you can't avoid having multiple joins as you've got a recursive structure. The alternative, is you change the manager/employee table you want to join to by making it a 'flattened' structure which has a row for every manager -> employee relationship regardless as to what level of the hierarchy the manger is actually at eg
Obviously this will be very large.
We do this, but build up the flattened hierarchy table as part of overnight processing in our SQL database, so the data source we use through Tableau is simply making 1 join to this table.
Thanks all. I was able to use the solution that Shin provided and it meets our needs. Thanks again for your help!