Posts Tagged "Org Chart"

Create A Simple Org Chart Using SharePoint, PeopleSoft and Google Visualizations

Create A Simple Org Chart Using SharePoint, PeopleSoft and Google Visualizations

If your organization is anything like mine, you have a collection of Org Charts from every department.  Most of them are formatted differently and all of them get out-of-date pretty quickly.  Sure, there are tons of third party solutions, but they all assume that you:

a. Keep all of your organizational information in Active Directory or

b. Don’t mind duplicating (and maintaining) your organizational information in yet another system.

Well, neither of those was true for us.

What I plan to show in this post is a simple approach to create a completely dynamic Org Chart using SharePoint, PeopleSoft, and Google Visualization.  If you don’t use PeopleSoft, don’t worry, these can be EASILY modified to use your data source of choice.

What You Need:

  1. Access to a view of the PeopleSoft data that contains the ID of each employee and that of their manager.
  2. SharePoint Designer
  3. Ability to create a Stored Procedure.
  4. Basic knowledge of XSL, Javascript, and CSS won’t hurt.

Steps:

  1. Create a stored procedure to recursively retrieve managers and their staff, accepting a parameter for the Employee ID of the top level manager you are looking for and Level <= 2 means give me 2 levels below the top level manager:
    WITH DirectReports (Name, ReportsTo, Position, JobTitle, EmployeeID, Level)
    AS
    (
         SELECT
           rtw.Name,
           rtw.ReportsTo,
           rtw.position,
           rtw.JobTitle,
           rtw.EmployeeID,
           0 AS Level
        FROM [PeopleSoft].[WhoReportsToWhoView] AS rtw
        WHERE EmployeeID = @EmployeeID 
    
    UNION ALL 
    
        SELECT
           rtw.Name,
           rtw.ReportsTo,
           rtw.Position,
           rtw.JobTitle,
           rtw.EmployeeID,
           Level + 1
        FROM [PeopleSoft].[WhoReportsToWhoView] AS rtw
    
        INNER JOIN DirectReports AS d ON rtw.ReportsTo = d.Position
    )
    Select
       dr.Name,
       dr.JobTitle,
       dr.EmployeeID,
       dr2.EmployeeID as ReportsTo
    FROM DirectReports as dr
    LEFT OUTER JOIN
    DirectReports as dr2 ON dr.ReportsTo = dr2.Position Where dr.Level <= 2
  2. From SharePoint Designer create a SQL DataSource for the Stored Procedure above, passing in the EmployeeID paramter as a querystring parameter. You should be able to Google that, it is pretty well documented.  Sorry, I can’t hold your hand forever.
  3. Download this XSLT file and add it to your SharePoint Style Library:
  4. From SharePoint Designer add a Data View WebPart to the page. Set the XSLT path to that of the newly uploaded file.
  5. Viola! You should end up with something similar to the image below.  Just click on any employee to drill down into his/her organization.

To learn more about the inner-workings and various options of Google Visualization, check out Google’s documentation.

Read More