August 18, 2011 at 7:51 am
I have table
EmpID Name MngrID
1 A Null
2 B 1
3 C 1
4 D 2
5 E 2
6 F 3
7 G 4
I want hierarchy drill down like
+1
+2
+4
7
5
+3
6
How can I get this?
August 18, 2011 at 8:34 am
I don't think you can.
At first glance I thought just make your table self referencing, and do a group on the Manager ID. But then I realized you want employees with no direct reports to show up in the Manager group without any drill downs. They are not managers.
Also the order of your desired output doesn't make sense to me.
August 18, 2011 at 9:12 am
Utimate purpose is If we drilldown managerId, he should see his employees & sub employees. thats is.
As if like a hierarchy
August 18, 2011 at 1:23 pm
In that case you should just be able to add a row group on your MngrID column and a visibility toggle.
August 18, 2011 at 1:34 pm
This query should return the data in the format you are looking for and then you can walk through the report wizard to get it set up with drilldown enabled:
SELECT
em1.Name MgrName,
COALESCE(em2.Name, 'No Subordinates') AS EmpName
FROM EmployeeManager em1
LEFT OUTER JOIN EmployeeManager em2
ON em1.EmpID = em2.MgrID
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply