Please apply the query:
SELECT
ta.employee AS "Employee:Link/Employee:200",
COUNT(CASE WHEN ta.status = 'Present' THEN 1 END) AS "Total Present:Int:200",
COUNT(CASE WHEN ta.status = 'Absent' THEN 1 END) AS "Total Absent:Int:200",
COUNT(CASE WHEN ta.late_entry = 1 THEN 1 END) AS "Total Late Entry:Int:200",
COUNT(CASE WHEN ta.early_exit = 1 THEN 1 END) AS "Total Early Exit:Int:200",
SUM(TIMESTAMPDIFF(HOUR, ta.in_time, ta.out_time)) AS "Total Working Hours:Float:200"
FROM
tabAttendance ta
WHERE
ta.docstatus = 1
AND MONTH(ta.attendance_date) =
CASE
WHEN %(month)s = 'January' THEN 1
WHEN %(month)s = 'February' THEN 2
WHEN %(month)s = 'March' THEN 3
WHEN %(month)s = 'April' THEN 4
WHEN %(month)s = 'May' THEN 5
WHEN %(month)s = 'June' THEN 6
WHEN %(month)s = 'July' THEN 7
WHEN %(month)s = 'August' THEN 8
WHEN %(month)s = 'September' THEN 9
WHEN %(month)s = 'October' THEN 10
WHEN %(month)s = 'November' THEN 11
WHEN %(month)s = 'December' THEN 12
END
AND YEAR(ta.attendance_date) = %(year)s
GROUP BY
ta.employee
ORDER BY
ta.employee;
Output:
If you want to make a more dynamic report then you have to build the server script report so please check the reference: