Expression Field

[acmoseley]

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.

[MFGF]

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.