Organization Heirarchy

[vummadis]

Hi - I have a requirement to show the Head of the organization (President Name) for all the employees. Below is the structure of the Table. This is High priority for me, any help is appreciated.

Employee Name   Supervisor Name
ABC                     BCD
BCD                     CDE
CDE                     DEF
DEF                      EFG
etc....
TUV                     XYZ

XYZ is the President of this organization.. I need to display this value in the report along with the above two columns. The report should like below.

Employee Name   Supervisor Name  President's Name
ABC                     BCD                      XYZ
BCD                     CDE                      XYZ
CDE                     DEF                      XYZ
DEF                      EFG                      XYZ
etc....                                              XYZ                   
TUV                     XYZ                      XYZ
 

[Francis aka khayman]

if the governor settings of your package allows you to do a cross join (or if you are allowed to change the setting in your package to allow cross join and re publish, much better), you can create two queries from RS. the first query looks like your table:

Query1
Employee          Supervisor
AAA                       BBB
etc                           etc

your second query is only one row, one column containing the president name

Query2
Pres
XYZ

do a cross join between Query1 and Query2. The resultant query would be the one you want.

[Francis aka khayman]

if crossjoin is not an option try this:

Query1
Employee       Supervisor       ID
AAA                      BBB           1

Query2
ID        Pres
1           XYZ

join query1 and query2 on Query1.ID = Query2.ID

better yet if you are allowed to hard code, just create a query using your first table, then add a column which value is the fix name of the president

[vummadis]

Thankyou for your reply.
First thing is I need to get the President's name. This is what I can do to identify the president. If the Employee name & the Supervisor names or equal, then he is President. So how I can use this result and apply it to the rest of the rows.

Employee Name   Supervisor Name  President's Name
ABC                     BCD                      XYZ
BCD                     CDE                      XYZ
CDE                     DEF                      XYZ
DEF                      EFG                      XYZ
etc....                                              XYZ                   
XYZ                     XYZ                      XYZ

[Francis aka khayman]

from cognos you can filter the data item to have emp_name = sup_name

in SQL you can do something like:

select emp_name
from table
where emp_name = sup_name

[vummadis]

Once I get the result from the below Query, how I should Join  this result to the main Query.

select emp_name
from table
where emp_name = sup_name

[vummadis]

I could able to get the result of that Query and then add it to the list report. Now the President name is coming for all the employees. But we have multiple Presidents in the Hierarchy. So for first the few rows,  the result is correct. But later when the new President starts, it is still showing the first President's name. Can you please help me how to resolve this.
Thanks in advance.

[Francis aka khayman]

your first post indicate there is only one president, hence the design i gave you. now you say that you have many president.

you need to give an exact approximation  :P ??? ;D of your current table design so i can give you a correct solution.

I could able to get the result of that Query and then add it to the list report. Now the President name is coming for all the employees. But we have multiple Presidents in the Hierarchy. So for first the few rows,  the result is correct. But later when the new President starts, it is still showing the first President's name. Can you please help me how to resolve this.
Thanks in advance.

[vummadis]

Sorry for the confusion.
Actually I need to Print the Vice President Name on the report. So there will be multiple Vice Presidents in the Organization. For the Vice President row, Employee Id and Supervisor Id values are equal. And I can get the Vice President Name as first row when each hierarchy begins.
I don't have to Print the President's name at all on the report.

[Francis aka khayman]

the easiest way is you do a cross join

Table1                         Table2

Emp    Sup                 ViceP
E1        S1                     VP1
E2        S2                      VP2
E3        S3

Table1 cross join Table2 =

Emp  Sup    ViceP
E1     S1       VP1
E2     S2       VP1
E3     S3        VP1
E1     S1       VP2
E2     S2       VP2
E3     S3        VP2