Grouping two similar fields for sums 1

[socalvelo]

CR XI
Oracle db

For this question I have four non-numeric fields
{Incident_Number} - unique ID for each incident
{Level} - level of incident is F or M
{Employee1} Employee ID number
{Employee2} Employee ID number

I am creating a report that will tell you how many F and M incidents were handled by an employee. This is very easy to do if I only want it to report on one employee field.

The problem I have is that some incidents are handled by one employee and some are handled by two employees. So an employee number could show up in either field. I need to group by employee and have sums for M and F next to their respective Employee ID number. The group sums need to pull from employee ID numbers in either field. An incident handled by one employee would have a null field in {employee2}.

To accomplish this to date I have a report with {employee1}plus the other fields in Section DetailA and {employee2} plus the other fields in DetailB. This is exported to Excel. Then I run a second report from the Excel worksheet which will group by employee and it works fine.

What I am trying to do is create the group summaries by employee in one report and not use the two reports and Excel steps. Combining the results for each employee, then grouping them is where I am stumped.

Any suggestions are appreciated.

 

[lbass]

If you are grouping and there are two employees for the same record, you have to create a second record so that the counts will be accurate. You can do this by using a command as your datasource. Set it up like this:

select 'Empl1' as whichemployee, table.`incidentno`, table.`level`, table.`employee1` as employee
from table
union
select 'Empl2' as whichemployee, table.`incidentno`, table.`level`, table.`employee2`
from table

You can then simply group by {command.employee}. If you need to distinguish employee1 and employee2, you can use the {command.whichemployee} field.

-LB

 

[socalvelo]

LB
Thank you once again for saving the day for me. I apologize for taking so long to test this.

On the second part of the union, should table.'employee2' be table.'employee2' as employee ?

 

[lbass]

It is not necessary--the label you add to the first part of the union will determine the field name, and does not need to be repeated.

-LB