May 28, 2004 at 12:38 am
i have a problem,
my problem is iam making one report which will be showing the data of
of activities
now the data of activity report can be seen by the person who is making it
and the superior above him
now there is one table called "RMCPL_EMP_MASTER "
which has all the details of the employees
and it has one column rmcpl_reporting_to
which has the emplyee cd of the person whom he is reportingto
hirearchy is as follows(designation)
Executive Director
President
Vice President
General Manager
Deputy General Manager
Zonal Manager
Regional Manager
Deputy Manager
Technical Executive
out of these designation technical executive is the lowest designation
he will be reporting to deputy manager
suppose debuty manager is MR ABC and his employeecd is 19
and technical executive name is bbc and his employecd is 20
now in table for the following row will be
empcod name reporting
20bbc 19
now while seeing a report technical executive can see the activity report of his only, becoz nobody is reporting to him.
now if a deputy manager is seeing the report he can see the data of activity of his and the technical executive who is reporting to him
iam getting that by refering to "reportingto" column it will have that deputy manger code
now a single hirearchy is possible, but if general manager wants to see the report
he should be able to see the data of
deputy general manger
zonal manger
regional manager
deputy manager
and technical executive (note he cannot see the activity data of Vice President,president and executive director)
becoz they are above him,
but now see i have the reporting to column and i will use it to get the data of related ppl
now in this case zonal manager will have the reportingto code of deputy manager
so i will be able to see data of only zonal manager not of the ppl below him
now how do i do that?., loops functions, stored procedure which is the best
way to do it.
Thanks in Advance
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
May 31, 2004 at 8:00 am
This was removed by the editor as SPAM
May 31, 2004 at 9:11 am
Sukio,
Like I said in one of your previous posts, lookup "expanding hierarchies" in "Books on Line". With a minor amount of tweeking, it will do exactly what you want.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 31, 2004 at 9:35 am
You could flatten the hierarchical data, and use this flattened view for the reporting requirements.
I dont know enopough about the database schema and the reporting requirements, but anyway, here is a sample method of flattening the hierarchy:
select
[Executive Director].empcod as [Executive Director Cd],
[Executive Director].name as [Executive Director Name],
[President].empcod as [President Cd],
[President].name as [President Name],
[Vice President].empcod as [Vice President Cd],
[Vice President].name as [Vice President Name],
[General Manager].empcod as [General Manager Cd],
[General Manager].name as [General Manager Name],
[Deputy General Manager].empcod as [Deputy General Manager Cd],
[Deputy General Manager].name as [Deputy General Manager Name],
[Zonal Manager].empcod as [Zonal Manager Cd],
[Zonal Manager].name as [Zonal Manager Name],
[Regional Manager].empcod as [Regional Manager Cd],
[Regional Manager].name as [Regional Manager Name],
[Deputy Manager].empcod as [Deputy Manager Cd],
[Deputy Manager].name as [Deputy Manager Name],
[Technical Executive].empcod as [Technical Executive Cd],
[Technical Executive].name as [Technical Executive Name]
( select empcod, name, rmcpl_reporting_to from RMCPL_EMP_MASTER ) [Executive Director]
join ( select empcod, name, rmcpl_reporting_to from RMCPL_EMP_MASTER ) [President]
on [Executive Director].empcod = [President].rmcpl_reporting_to
join ( select empcod, name, rmcpl_reporting_to from RMCPL_EMP_MASTER ) [Vice President]
on [President].empcod = [Vice President].rmcpl_reporting_to
join ( select empcod, name, rmcpl_reporting_to from RMCPL_EMP_MASTER ) [General Manager]
on [Vice President].empcod = [General Manager].rmcpl_reporting_to
join ( select empcod, name, rmcpl_reporting_to from RMCPL_EMP_MASTER ) [Deputy General Manager]
on [General Manager].empcod = [Deputy General Manager].rmcpl_reporting_to
join ( select empcod, name, rmcpl_reporting_to from RMCPL_EMP_MASTER ) [Zonal Manager]
on [Deputy General Manager].empcod = [Zonal Manager].rmcpl_reporting_to
join ( select empcod, name, rmcpl_reporting_to from RMCPL_EMP_MASTER ) [Regional Manager]
on [Zonal Manager].empcod = [Regional Manager].rmcpl_reporting_to
join ( select empcod, name, rmcpl_reporting_to from RMCPL_EMP_MASTER ) [Deputy Manager]
on [Regional Manager].empcod = [Deputy Manager].rmcpl_reporting_to
join ( select empcod, name, rmcpl_reporting_to from RMCPL_EMP_MASTER ) [Technical Executive]
on [Deputy Manager].empcod = [Technical Executive].rmcpl_reporting_to
/rockmoose
You must unlearn what You have learnt
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply