Group Count

[john4040]

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.

 

[skram68]

First step is to add a group to your report. Group by Dept.

Add a summary field (count) to the group. This will give you the total number of employees within the group.

Add a formula field that takes the new summary count field and divide it by 3 (will need to round or truncate to an integer, you will need to decide if you want a value of 3.6 to round to 4 or truncate to 3).

You can then use the summary field and the formula field to embed in a text field to get the 'x out of y have the highest'.

For the *, put that as a text field on the report.
Add a new formula (or set of formulas) to keep track of which record within the group you are on, then format the '*' field to suppress if the record number is > the result of the formula field.

Hope that helps. Let me know if you need more detail.