Turnover Reports

Hi M.J.,
I have developed a number solutions using both Crystal as well as COBOL.

In addition, there are a couple of other Lawson-delivered solutions that attempt to do the same type of thing:
- There's a new report (PA223 - Starters & Leavers Report) in 9.0 applications
- You can purchase the "headcount" data mart, which will do similar, for $$$ and lots of implementation time & effort.

john, how long do your crystal and cobol reports take? In a past life, IT created a cobol-based report and it took upwards of 4 hours to run. 8000 employees.

3500 employees ~ 2 minutes.

that's hilarious. I thought they messed up something.

I wrote a perl script to do this.  I'm never sure of all the low level details on how the Lawson reports work, so I don't know how to cross-check the results, so I almost always develop my own custom script to do this stuff because I expect to be able to justify the data I am providing.

For department turnover, wrote a perl script that operates on output from a DME query, is flexible enough to actually look at change in any field within a time period.  I get requests to find department changes, job code changes, position changes, and report ONLY those types of changes by different groups.  I'm sure you can relate.  Saved a lot of work by designing it generically.  And now, because we have the capability, people are asking me for Status changes, and other things that I'm sure are very useful

See my guide on how to call DME from perl.

The script I have developed takes less than 1 minute to execute, all the time is taken in querying the data from Lawson through the DME call.

We have other reports build by other people, one in Crystal that is slow (minutes), one in 4GL that is really fast (seconds), but these are not department turnover, they are overall turnover, as in people who have terminated.  Not changes within the company.

Jeremy Zerr
http://www.zerrtech.com

Here is an SQL query that I used to produce a "Position Turnover" list. It counts the number of employees who have been in each position, starting with a specific date:
-----------------------------------
select distinct(s.POSITION) "POSITION",
count(distinct(e.EMPLOYEE)) "EMPLOYEE_COUNT"
from (select distinct(s1.employee), s1.position
from prodlaw.paemppos s1,
prodlaw.employee e1
where s1.employee = e1.employee
and s1.end_date >= '01-JAN-2009'
order by s1.position) s,
prodlaw.employee e
where s.employee = e.employee
group by s.position