Link to home
Create AccountLog in
Avatar of QPR
QPRFlag for New Zealand

asked on

Ogr chart query

I am looking into some data which I will query and then turn the results into an organisation chart (somehow!).
Basically this will be a piece of the overall org chart that I'll be returning.

The user will select a position title. This will return their title, any subordinates they have together with all the subordinates of their immediate manager as per the attached picture.
The highlighted box shows which position was selected. I need to work out how to convert this query into shapes etc .

ok each employee has an empcode and a parentcode. The parentcode is the empcode of their immediate manager.
So my thinking is, (based on the users position selection) get the parentcode for that position which in turn will give me all the subordinates on that level as they will all share the same parentcode.
Then I need to get the subs of the selected position. Easy as they will all have a parentcode that matches the empcode of the selected position. Still with me?

My results will look something like

Position                             empID             ParentID
House manager                  123                    999
Cleaner                               223                    123
Tea Lady                             402                    123
Mail Person                         556                    123
Asst Cleaner                       409                    223
Asst Cleaner                       492                    223

So either at the data layer or the application layer (ASP.NET) I need to somehow turn these values into images/shapes/coloured table cells/something! to depict the piece of the chart applicable to the query.
org.gif
Avatar of chapmandew
chapmandew
Flag of United States of America image

sql server 2005?
Avatar of QPR

ASKER

Alas in 2000. I do have a 2005 box and could run queries from there if a linked server would do it?
I see Vision has the ability to take data froma spreadsheet and convert this into an org chart. If I knew how it was doing it I could perhaps reproduce this on a .aspx page?
Here is how to do something similar in 2005:

USE AdventureWorks;
GO
WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS
(
    SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
    FROM HumanResources.Employee
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
    FROM HumanResources.Employee e
        INNER JOIN DirectReports d
        ON e.ManagerID = d.EmployeeID
)
SELECT ManagerID, EmployeeID, EmployeeLevel
FROM DirectReports ;
GO

Avatar of QPR

ASKER

I'm a bit new to the new bits of t-sql in 2005 - is DirectReports a common table expression?
Avatar of QPR

ASKER

I'll have to read up on that before I can understand what the query is doing. Don't have the AW db in my 2005 instance.
The data in question is...

Works (table)
positionID
description
worksID
ParentID

Position (table)
positionID
description

Appointment
EmployID
WorksID

Employee (table)
EmployeeID


ASKER CERTIFIED SOLUTION
Avatar of chapmandew
chapmandew
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of QPR

ASKER

Thanks Tim, will print and read up when I get a spare moment.
Avatar of QPR

ASKER

I've got my data now by using a cursor.
It's the ability to display my chart/images/something based on a column value now.
My results are (swapping positionX for real job titles)...

position1           position
position2           colleage
position3           colleage
position4           colleage
position5           Manager
position6           sub
position7           sub

This would need to display in the web page (or windows form or word doc or whatever!) as.....



                             manager
colleague  colleague   position colleague    colleague
                                sub      sub


in the usual org chart style (boxes and lines)