Hi All,
I created new query report, Attedance Summary report my query code is
SELECT
ta.attendance_date AS “Attendance Date:Date:100”,
ta.employee AS “Employee:Link/Employee:150”,
COUNT(CASE WHEN ta.status = ‘Present’ THEN 1 END) AS “Total Present:Int:100”,
COUNT(CASE WHEN ta.status = ‘Absent’ THEN 1 END) AS “Total Absent:Int:100”,
SUM(TIMESTAMPDIFF(HOUR, ta.in_time, ta.out_time)) AS “Total Working Hours:Float:120”,
COUNT(CASE WHEN ta.late_entry = 1 THEN 1 END) AS “Total Late Entry:Int:100”,
COUNT(CASE WHEN ta.early_exit = 1 THEN 1 END) AS “Total Early Exit:Int:100”
FROM
tabAttendance ta
WHERE
ta.docstatus = 1
GROUP BY
ta.employee, ta.attendance_date
ORDER BY
ta.employee, ta.attendance_date;
but in my case need like above image summary report can u please guide me for a custom report or query report if they choose the employee monthly present,absent,Late entry,early exit need
Thanks in Advance
can guide for the server script code for following fields “Employee”,“Total Present”,“Total Absent”,Total Late Entry",“Total Early Exit”,“Total Working Hours” and month wise filter need like the above image
Thanks in Advance
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:
