Tuesday, April 12, 2011

Making an org chart from Exchange, part 4

So, I want to build a simple report to draw out my org chart from Outlook / Exchange / Active Directory. To do that, I outlined a pretty straightforward data table:

CREATE TABLE [dbo].[reportsto](
[ID] [varchar](1000) NULL,
[pname] [varchar](1000) NULL,
[pmanager] [varchar](1000) NULL,
[paddress] [varchar](1000) NULL,
[INSERTED] [datetime] NULL
) ON [PRIMARY]

I then wrote a script to draw down various entities from Active Directory to my local SQL Server. You can simulate what data I got with the following inserts:


insert into dbo.reportsto(pname,paddress,pmanager,pmid) values ('test person 1', '123 Main Street', '',null);
insert into dbo.reportsto(pname,paddress,pmanager,pmid) values ('test person 2', '123 Main Street', 'test person 1',(select pid from dbo.reportsto where pname = 'test person 1'));
insert into dbo.reportsto(pname,paddress,pmanager,pmid) values ('test person 3', '123 Main Street', 'test person 1',(select pid from dbo.reportsto where pname = 'test person 1'));
insert into dbo.reportsto(pname,paddress,pmanager,pmid) values ('test person 4', '123 Main Street', 'test person 2',(select pid from dbo.reportsto where pname = 'test person 2'));
insert into dbo.reportsto(pname,paddress,pmanager,pmid) values ('test person 5', '123 Main Street', 'test person 2',(select pid from dbo.reportsto where pname = 'test person 2'));
insert into dbo.reportsto(pname,paddress,pmanager,pmid) values ('test person 6', '123 Main Street', 'test person 2',(select pid from dbo.reportsto where pname = 'test person 2'));
insert into dbo.reportsto(pname,paddress,pmanager,pmid) values ('test person 7', '123 Main Street', 'test person 2',(select pid from dbo.reportsto where pname = 'test person 2'));
insert into dbo.reportsto(pname,paddress,pmanager,pmid) values ('test person 8', '123 Main Street', 'test person 2',(select pid from dbo.reportsto where pname = 'test person 2'));
insert into dbo.reportsto(pname,paddress,pmanager,pmid) values ('test person 9', '123 Main Street', 'test person 2',(select pid from dbo.reportsto where pname = 'test person 2'));
insert into dbo.reportsto(pname,paddress,pmanager,pmid) values ('test person 10', '123 Main Street', 'test person 3',(select pid from dbo.reportsto where pname = 'test person 3'));