Employee's First Visit

[Ox73]

Hi - I have a crystal report where I am trying to count in a Work Order grouped by Employee. I am able to count and get a percent per order but now I want my main group to be by employee visits per work order. The problem is that when I group it by employee it breaks my calculation because I may have one work order with two visits the first visit was the attempt to deliver a package but the customer was not there to receive the package. Then later a second employee was sent out and then finally delivered the package.

We are trying to measure the first visit but when I group it by employee it is counting one work order as two because it was assigned to two different employees. I want to eliminate the second visit all together or supress it somehow. I looked for a ordinal count, or some kind of sequence count that keeps record of the number of visits but our database does not have one, so somehow I have to eliminate the second record at the report level.

I have also tried a couple of running totals but with no avail.

 

[IanWaterman]

Try grouping by work order and then doing a distinct count on workorder numbers.

Move you details into the workorder Group header, suppress detail. And sort by visit number desc. This will push detail of first vist into the header.

Ian

 

[Ox73]

Thanks for your reply, I did start doing that initially and it works. However because there are more orders than there are employees I want the preview to show the employee's name first so then I can drill to the detail and see the first visit for that WO.

My main objective is to show Employee 1 totals, Employee 2 totals, and so forth.

 

[IanWaterman]

You can do that, by hiding the details rather than suppressing.

If you want to count the way you have indicated this will require a separate report, where you only bring back to the report visit one details. If you then wanted to drill down you can have an ondemand subreport linked by work order. when clicked on it would show details of all visits.

Ian

 

[lbass]

What version of CR are you using. It sounds like you need to return just the first visit/employee per workorder. This can be done using a command or a SQL expression, depending upon your version (which you should ALWAYS identify).

-LB

 

[Ox73]

I have version 10. I can add a command with the SLQ statements.

 

[lbass]

I'm unclear--do you need help with this?

-LB

 

[Ox73]

Yes

 

[lbass]

Set up the command like this:

Select table.`datetime`, table.`WO`, table.`employee`
From table
where table.`datetime` =
(
select min(A.`datetime`) from table A
where A.`WO` = table.`WO`
)

This should return only the first visit per WO, regardless of employee. Add in all the fields you need in the report. The punctuation will be specific to your datasource, so if you are unsure, take a look at database->show SQL query for a report that uses the same datasource.

-LB