|
I am creating a new report that has to be sorted by department, seniority date and name. The incoming data will look similar as follows:
Dept Seniority Date Name
100 1990/02/01 Test4
100 1984/10/01 Test2
100 1983/02/01 Test1
100 1985/01/02 Test3
200 1981/10/01 Test2
200 1979/02/01 Test1
200 1985/01/02 Test3
100 1985/03/02 Test11
100 1985/04/02 Test12
I will sort this report in Dept, Seniority Date and Name so it will look as follows:
Dept Seniority Date Name
100 1983/02/01 Test1
100 1984/10/01 Test2
100 1985/01/02 Test3
100 1990/02/01 Test4
100 1985/03/02 Test11
100 1985/04/02 Test12
200 1979/02/01 Test1
200 1981/10/01 Test2
200 1985/01/02 Test3
I'd like to do a count of the employees by department and highlight the top 1/3 of people out of total employees within each department and put '*' beside them and print a message at dept break saying eg. "3 out of 6 have the highest seniority" in case dept 100 or "1 out of 3 have the highest seniority" etc.
The report should look as follows:
Dept Seniority Date Name
* 100 1983/02/01 Test1
* 100 1984/10/01 Test2
* 100 1985/01/02 Test3
100 1990/02/01 Test4
100 1985/03/02 Test11
100 1985/04/02 Test12
Department 100 3 out of 6 have the highest seniority
* 200 1979/02/01 Test1
200 1981/10/01 Test2
200 1985/01/02 Test3
Department 200 1 out of 3 have the highest seniority etc.
Is there a way to do this ?
Thanks in advance.
|