Hierarchy Problem

I need to create a stored procedure that creates a hierarchy for each employee in a large employee data table (CCINFORMATION).

Each entry in CCINFORMATION contains, among other things, the ID number of the employee, their title, their manager's ID, and their manager's title.
I need to use this table to build a string for each employee. That string should contain the ID of everyone in that employee's hierarchy up to the CEO, and then I need to place that string into a different table.

I don't know Transact-SQL really well, so I am at a little bit of a loss how to set up the logic to go through each record in CCINFORMATION, and for each one build the string as I run a series of queries based on the manager's ID until I reach the CEO. Any ideas?I managed to get this stored procedure to work, so I thought I would share, in case it helps anyone else:

Declare @.AWID varchar(50), @.Title varchar(2000), @.MgrAWID varchar(50), @.MgrTitle varchar(2000), @.hstring varchar(2000), @.hnew varchar(2000), @.SEARCHAWID varchar(50)

Declare cursor1 CURSOR FOR
Select AWID, Title, MgrAWID, MgrTitle
FROM CCINFORMATION

OPEN cursor1
FETCH NEXT FROM cursor1
INTO @.AWID, @.Title, @.MgrAWID, @.Mgrtitle
WHILE @.@.FETCH_STATUS = 0
Begin
Set @.hstring = @.AWID + ',' + @.MgrAWID
IF patindex('%CEO%', @.Mgrtitle) > 0
BEGIN
--PRINT @.hstring + ' is going in right away'
INSERT INTO HIERARCHY (AWID, Hierarchy) VALUES (@.AWID, @.hstring)
END
ELSE
BEGIN
--PRINT @.hstring + ' begin subloop'
mgrloop:
Set @.SEARCHAWID = @.MgrAWID
Declare cursor2 CURSOR FOR
Select MgrAWID, MgrTitle
FROM CCINFORMATION
WHERE AWID = @.SEARCHAWID

OPEN cursor2

FETCH NEXT FROM cursor2
INTO @.MgrAWID, @.Mgrtitle

WHILE @.@.FETCH_STATUS = 0
Begin
set @.hstring = @.hstring + ',' + @.MgrAWID
IF patindex('%CEO%', @.Mgrtitle) > 0
BEGIN
--PRINT @.hstring + ' is going into Hierarchy'
INSERT INTO HIERARCHY (AWID, Hierarchy) VALUES (@.AWID, @.hstring)
CLOSE cursor2
Deallocate cursor2
set @.hstring=''
GOTO ceofound
END
ELSE
BEGIN
--PRINT @.hstring + ' isnt ceo'
CLOSE cursor2
Deallocate cursor2
GOTO mgrloop
END
END
FETCH NEXT FROM cursor2
INTO @.MgrAWID, @.Mgrtitle

CLOSE cursor2
Deallocate cursor2
--PRINT @.hstring + ' ends before ceo'
set @.hstring = @.hstring + ',fail'
INSERT INTO HIERARCHY (AWID, Hierarchy) VALUES (@.AWID, @.hstring)
set @.hstring=''
END

ceofound:
FETCH NEXT FROM cursor1
INTO @.AWID, @.Title, @.MgrAWID, @.Mgrtitle
END

CLOSE cursor1
DEALLOCATE cursor1

Hiding table columns creates solid black columns in PDF

Hello all,

I am having a slight problem when dynamically hiding columns in a data table.

When I use the set the "Hidden" property to true, I seem to end up with a solid black column on the far right of the report... but only when generating the report as a PDF.

I've searched the internet quite a bit trying to track down the issue, but I can't seem to find anything.

Has anyone encountered this problem before? Suggestions?

Thanks,

David

check the background color

it should be transperant

|||

All the cells and columns are set to have a background of transparent

no luck :(

|||Anyone else have any thoughts on this problem?|||

ur best bet would be to hide table row itself rather than just hiding a column and u can give a condition instead of just setting the hidden column to true.

Hiding series in a chart

Good afternoon
I have a hidiously complicated union select statement that i am feeding in
to a chart.
This creates two series and two data fields, some of these combinations dont
hold usefull data and so i would like to hide them. e.g
Series 1 and data field 1 = good data
Series 1 and data field 2 = crap data
Series 2 and data field 1 = crap data
Series 2 and data field 2 = good data
I cant find a way of hiding the series-datafield combinations that i dont
need.
The crap data that i dont want to show is always 0's but by filtering i
remove a whole data field not just part of it.
Please help
Thanks
Steve Dcan you get needed fields from your existing query?
something like this:
select Series1, Field1 from ( your complicated query ) as
ExistingQuery
where Series1 = 'whatever'
union
select Series2, Field2 as Field1 from ( your complicated query) as
ExistingQuery
where Series2 = 'whatever'
you might add extra integer field to your existing query
when Series 1 then 1
Series 2 = 2
and use it in where statement above
Steve Dearman wrote:
> Good afternoon
> I have a hidiously complicated union select statement that i am feeding in
> to a chart.
> This creates two series and two data fields, some of these combinations dont
> hold usefull data and so i would like to hide them. e.g
> Series 1 and data field 1 = good data
> Series 1 and data field 2 = crap data
> Series 2 and data field 1 = crap data
> Series 2 and data field 2 = good data
> I cant find a way of hiding the series-datafield combinations that i dont
> need.
> The crap data that i dont want to show is always 0's but by filtering i
> remove a whole data field not just part of it.
> Please help
> Thanks
> Steve D