• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 516
  • Last Modified:

Manager Employee Heirarchy

This is a 2 part question. Query and Reporting
My users need a report where they enter manager(s) name and i need to pull the data of the employees under him to the lowest level

Here is the way they want the report ot look like

Manager parameter : Davis Barela & Kim Wester
Initial view (notice the + sign next to Davis Barela's row)

      dept     Employee       Manager        job      dob    
      -------------------------------------------------------
+      dept1    Jon Barrett    Davis barela   GM       5/12/76
      dept3    Brett Vaughn   Kim Wester     IC       5/12/80
      dept3    Fred Khali     Kim Wester     IC       5/12/80

When they click on the plus sign

      dept     Employee       Manager        job      dob    
      -------------------------------------------------------
-      dept1    Jon Barrett    Davis barela   GM       5/12/76
+      dept2    Chris Weber    Jon Barrett    DM       5/12/77
      dept3    Atkul Harize   Jon Barrett    QA       5/12/73
      dept1    Kristin Dey    Jon Barrett    IC       5/12/72
      dept3    Jim Cook       Jon Barrett    WF       5/12/80
      dept3    Brett Vaughn   Kim Wester     IC       5/12/80
      dept3    Fred Khali     Kim Wester     IC       5/12/80

When they click on the plus sign again

      dept     Employee       Manager        job      dob    
      -------------------------------------------------------
-      dept1    Jon Barrett    Davis barela   GM       5/12/76
-      dept2    Chris Weber    Jon Barrett    DM       5/12/77
      dept2    Sam Auer       Chris Weber    DM       5/12/77
      dept3    Atkul Harize   Jon Barrett    QA       5/12/73
      dept1    Kristin Dey    Jon Barrett    IC       5/12/72
      dept3    Jim Cook       Jon Barrett    WF       5/12/80
      dept3    Brett Vaughn   Kim Wester     IC       5/12/80
      dept3    Fred Khali     Kim Wester     IC       5/12/80


I started working on my query, but i don't know which format i need to get the data to fit this kind of report.
0
Raju Srivatsavaye
Asked:
Raju Srivatsavaye
  • 4
2 Solutions
 
ibrusettCommented:
If you use SQL server 2005 you can take advantage of the use of CTE and recursive query.

Suppose the simplest structure for you table:
CREATE TABLE Employees (Employee_ID int PRIMARY KEY, Employee_Name Varchar(50), Manager_ID int)

suppose that Davis Barela has a Employee_ID = 2


Your query looks like:
WITH Recursion (Employee_ID, Employee_Name, Manager_ID, Level)
AS
(SELECT Employee_ID, Employee_Name, Manager_ID, 0
FROM Employees WHERE Employee_ID = 2
UNIO ALL
SELECT t.Employee_ID, t.Employee_Name, t.Manager_ID, Level+1
FROM Employees AS t inner join
Recursion AS a on a.Employee_ID = t.Manager_ID
)
SELECT sr.Employee_Name as Employee,
et.Employee_Name AS Boss,
sr.Level AS Level
FROM Recursion AS sr INNER JOIN
Employees et ON sr.Manager_ID = et.Employee_ID

The "Level" field allows you to obtain the last result (with no hidden data) and allows you to order the data and can be used as a variable to hide them

For the Sql Server Reporting Services part (so how to obtain + to hide /show data) I have no idea...
0
 
Raju SrivatsavayeSoftware EngineerAuthor Commented:
Ibrusett,

Thanks for the quick response. Actually this is the way i started my query. But i need to know the reporting services part of it and need to format the data from the SQL to get the +/- functionality.

Thanks
0
 
Ken SelviaRetiredCommented:
I think this is pretty close, but probably not exact. Hopefully it will get you started;

Add a matrix to your report. Add Level, Employee_ID, Employee_Name, Manager_ID as columns. Add a Column Group (select Matrix1 , Right Click, Add Column Group) for expression  = Level, right click properties on the other three columns one at a time, under the visibility tab, check the box that says visibility can be toggled by another report item, and choose Level from the dropdown.

Look at the Sales Report example for SRS if you have the Northwind db installed someplace.


0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Raju SrivatsavayeSoftware EngineerAuthor Commented:
kselvia,

"Add Level, Employee_ID, Employee_Name, Manager_ID as columns. Add a Column Group (select Matrix1 , Right Click, Add Column Group)"

what is the difference between a column and a column group ( i think they are same)

Thanks
Raju


0
 
Raju SrivatsavayeSoftware EngineerAuthor Commented:
Hi,

I figured it out from this article http://www.sqlservercentral.com/articles/Development/reportingonhierarchicalrecursivedatausingreporting/2476/
Thanks for the help though. (i will share the points between you too :-) )

This is what i did for anybody those need this kind of report.
created a table
created a detailed grouping on the table row.
In the group, i grouped on Employee and set the parent group as manager.
In the visibility tab, set the visibility to hidden and toggle by dept textbox.

Thanks once again for your help

Raju


0
 
Raju SrivatsavayeSoftware EngineerAuthor Commented:
I shared the points between you too. Thanks for the help.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now