?
Solved

Manager Employee Heirarchy

Posted on 2008-06-14
6
Medium Priority
?
505 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
6 Comments
 
LVL 3

Accepted Solution

by:
ibrusett earned 750 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 750 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
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 
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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

764 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