Solved

Manager Employee Heirarchy

Posted on 2008-06-14
6
478 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 9

Author Comment

by:srivatsavaye
Comment Utility
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
Comment Utility
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
Comment Utility
I shared the points between you too. Thanks for the help.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

762 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

12 Experts available now in Live!

Get 1:1 Help Now