I have Employee database , now I want to get all the employees which are comes in hierarchy for reporting purpose Let me know any feasible solution. Thanks a lot. prakash

how are the rows related in your table..?Vishal Gajjar

I find the hierarchical report data using CTE : WITH Managers AS ( --initialization SELECT EMP_ID,EMP_RO_Code,RepMgr FROM Employee WHERE RepMgr=61 UNION ALL --recursive execution SELECT e.EMP_ID,e.EMP_RO_Code, e.RepMgr FROM Employee e INNER JOIN Managers m ON e.RepMgr = m.EMP_ID ) SELECT distinct * FROM Managers OPTION (MAXRECURSION 10)prakash

I find the hierarchical report data using CTE : WITH Managers AS ( --initialization SELECT EMP_ID,EMP_RO_Code,RepMgr FROM Employee WHERE RepMgr=61 UNION ALL --recursive execution SELECT e.EMP_ID,e.EMP_RO_Code, e.RepMgr FROM Employee e INNER JOIN Managers m ON e.RepMgr = m.EMP_ID ) SELECT distinct * FROM Managers OPTION (MAXRECURSION 10)prakash

Hi Prakash, Do you mean the relationship of each employee record in the employee table is self-referenced? For example, if each employee record has a key column to uniquely identify the employee and a parent key column that points to the parent record-the employee’s manager-in the same table, I would suggest you use Recursive Hierarchy Groups in Reporting Services. For more information, please refer to: http://msdn.microsoft.com/en-us/library/bb630438(v=SQL.100).aspx If I have misunderstood, please point out and elaborate the requirement with more details. Thanks, Tony ChainTony Chain [MSFT] MSDN Community Support | Feedback to us Get or Request Code Sample from Microsoft Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Hi Prakash, Do you mean the relationship of each employee record in the employee table is self-referenced? For example, if each employee record has a key column to uniquely identify the employee and a parent key column that points to the parent record-the employee’s manager-in the same table, I would suggest you use Recursive Hierarchy Groups in Reporting Services. For more information, please refer to: http://msdn.microsoft.com/en-us/library/bb630438(v=SQL.100).aspx If I have misunderstood, please point out and elaborate the requirement with more details. Thanks, Tony ChainTony Chain [MSFT] MSDN Community Support | Feedback to us Get or Request Code Sample from Microsoft Please remember to mark the replies as answers if they help and unmark them if they provide no help.