Hi,
I have been trying with the PHPRunner report, but I still cant figure out how it works to retrieve the following data.
I have created an award form as below, with each employee only can nominate once (meaning each employee only can add one record) but for different type of awards there can be the same employee and the user can also don't vote in certain award:
Example: User A login and do nomination
Employee id | Employee Name | Award A | Award B | Award C
B324 | User A | Tan Simon | Koh Peter | Tan Simon
Next, I need to have a report to calculate the number of votes casted for each employee in each award category with highest vote on top till lowest vote bottom. For null, it will not show in the report.
Example:
Award A | Award B | Award C
Tan Simon (10 votes) | David (8 vots) | Tan Simon (1 vote)
Loh Arthur (5 votes) Koh Peter (2 votes)
Low Mary (1 votes)
Not sure how PHPRunner report produce the above result, can someone please kindly help? Thanks.
If you store the choices of an employee in 1 record, it will be very difficult tot retrieve the information you want.
I would make a record for 1 award so you can use group by, order and count options to make a query.
Table choices
id
employee id
award id
award employee id
define a primary key on id
define a unique key on employee id, award id, award employee id
now you can make a query like
select award id, employee id, award employee id, count(award employee id) as votes
from choices
group by award id, employee id, award employee id
order by award id asc, votes desc , award employee id