Solved

Manager Employee Heirarchy

Posted on 2008-06-14
6
484 Views
Last Modified: 2012-05-05
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
Comment
Question by:srivatsavaye
  • 4
6 Comments
 
LVL 3

Accepted Solution

by:
ibrusett earned 250 total points
ID: 21786558
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
 
LVL 9

Author Comment

by:srivatsavaye
ID: 21786603
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
 
LVL 12

Assisted Solution

by:kselvia
kselvia earned 250 total points
ID: 21787707
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 9

Author Comment

by:srivatsavaye
ID: 21787784
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
 
LVL 9

Author Comment

by:srivatsavaye
ID: 21789626
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
 
LVL 9

Author Closing Comment

by:srivatsavaye
ID: 31467235
I shared the points between you too. Thanks for the help.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now