I have a report that lists a group of employees with their supervisor including both the employee ID and the supervisor ID. What I'd like add now is a flag to determine if an employee is listed as a supervisor. For instance, if an employee is listed as a supervisor in the report, they need to have a 2 in the "Is Supervisor" field, if they're not listed they need to have a 1. I've placed an example of what I want to see below.
Employee ID | Employee Name | Supervisor ID | Supervisor Name | Is Supervisor |
10001 | John Smith | 20001 | Jane Doe | 1 |
20001 | Jane Doe | 30001 | George Simpson | 2 |
Does anyone have thoughts on this? I've tried the count function coupled with a when statement, but I can't get it to work.
I would add a second query for employees, joined via employee ID on your existing query to Manager ID on the new query with an optional cardinality. You could add a query item with an expression of 1 to the new query, then in your main query (which sees the results of the two joined queries) add a query calc which looks for a null in this item (ie the employee is not a manager) and replaces it with a 1, else displays a 2
Just a thought...
MF.