Display Employee and Supervisor Tasks

[Former Member]

I would like to use a filter to select by supervisor ID and show all tasks assigned to active employees under the selected supervisor, including tasks assigned to the supervisor selected. I'm doing ok with all the employees tasks but I've been unable to retrieve the supervisors tasks along with those under him/her. I'm using Crystal XI; SQL Server 2000. I tried a cascading parameter but that didn't seem to work either. Any ideas? I'm also stumped on how to write the selection formula when using a cascading parameter.

[former_member184469 · Accepted solution]

Hi,

It's a little hard to understand what you are trying to do. What are the parameters you want to have? How does your filter statement look?

With a cascading prompt group you can bind 1 or more parameters. You probably only have a parameter at the bottom level for picking the employees. You can also bind a new parameter for the supervisor. Then in your filter statement you can have a statement like taskowner=?employee or taskowner=?supervisor.

[Former Member · Accepted solution]

What I would like is for the supervisor that runs the report to see the tasks assigned to him/her as well as the tasks assigned to his/her supervised employees. The only way I can figure to get around it is to include parameters for both employee and supervisor and have the user select himself and his supervisor in the supervisor parameter and then himself/herself and the employees he/she supervises in the employee selection parameter. My current select statement follows:

{@PA_Equipment Type} and

{@PA_Reactive Date} and

{@PA_Closed} and

{@PA_Entity ID} and

{@PA_Equipment ID} and

{@PA_Work Order No} and

({@PA_Assigned To} or

{@PA_SupervisorID})

[former_member184469 · Accepted solution]

What would you like to see beyond your current solution of utilizing a parameter for supervisor and a different parameter for employees? i.e. where you hoping only have one parameter for the supervisor? If so, your data will have to have the relationships of supervisor to employees or you could hardcode these relationships into a formula field.

[Former Member · Accepted solution]

Yes, I would like to enter supervisor number only and have the report return the supervisor and his employees. I'm only getting the employees not the supervisor. I've tried different links, adding the employee table twice and linking my {task table.assigned to id} to {employeetable.emp id} (left outer) and {tasktable.assigned to id} to {employeetable1.mgr id} (left outer) but then I don;t return any results at all. The best idea I've come up with so far is to require the user to select both supervisor and employees in which case the supervisor that wishes to view his records and his employees records would need to enter both his and his supervisors ID in addition to his emp ID and his employees. Does that totally cross your eyes? Am I even making sense to the outside world?

[former_member184469 · Accepted solution]

The links should be:

{tasktable.assigned to id} to {employeetable1.mgr id} (left outer)

{employeetable1.mgr id} to {employeetable2.mgr id} (left outer)

Then you should only need to filter on employeetable2.mgr id.

[Former Member · Accepted solution]

I was optimistic that your link suggestion would work but all I get is the supervisors assigned to task only. Again I tried every which way I could think of to return both the supervisors and his employees tasks but I get supervisors records only or supervised employees records, so far, never all the records. You have been very helpful with your suggestions and I do appreciate your assistance.

[former_member184469 · Accepted solution]

In addition to the correct linking you still need to have your record selection formula filter for both. So to capture all the employees you would have something like {employeetable2.mgr id} =?manager_parameter.

Then you need to "OR" this with tasks the manager owns himself: OR {tasktable.assigned to id} =?manager_parameter.

So the complete record selection should look like:

{employeetable2.mgr id} =?manager_parameter OR {tasktable.assigned to id} =?manager_parameter

[Former Member · Accepted solution]

Thanks for sticking with me on this. That seems to have solved the problem. I show the correct count of records on my report (21) but the count in the status bar shows 83 records are being returned from the DB. I'm not sure if this will be a problem or not. Optimally I'd like the records showing up on the report to be the only records retrieved from the database.

[former_member184469 · Accepted solution]

If you put the all the fields into the details section you'll be able to see exactly what those 83 records are. Also, you can copy and paste the SQL statement into a SQL query tool to see if your db is also returning 83 records.

[Former Member · Accepted solution]

Thanks for the response. I did put the SQL statement into the query analyzer and discovered, that for some reason I have yet to determine, that I'm getting duplicate records. Since I'm using a distinct count in the report it's only displaying the 21 records I expect. I'm going to need to work this one out on my own in the hopes that I'll come out on the other end more enlightened! I do appreciate your helping me get to this point.

[Former Member · Accepted solution]

Henry,

Finally got the report to work. I started a blank report and started placing the Tables/Views in one by one while checking to see how many records were returned. I finally decided to remove the employee1 alias table and modified a supervisor formula to incude the statement you suggested I use in my select formula; in addition to allowing for no supervisor selection at all. I added this formula to my select statement. To my surprise I got the expected 21 records and the status bar displayed 21 as well. Again thanks so much for your help. I couldn't have done it without you.