Solved

Manager Employee Heirarchy

Posted on 2008-06-14
6
497 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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

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…
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.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

730 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