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
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.
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
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
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
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
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.
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.
Quote from: vummadis on 26 Jan 2015 10:16:36 PM
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.
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.
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