Custom reports - counts wrong with group sessions

[kimberley-johnston]

Kia ora Dan,

I’m trying to use the custom reports but we’re finding that some of the numbers aren’t coming out as we’d expect. In the example attached I have a small course with 1 group in it. Some of the participants are in the group and some aren’t. I have 12 group only attendance sessions and one whole class session. When I look at the custom report I would expect that for the Points of all sessions column that would produce numbers based on what the student was able to access. i.e a group member would have 13 sessions and a non-group member would have 1? What I see however is a number out of 13 for everyone. This also is true of the column “Total number of sessions”.

We’re trying to reproduce the data that appears on the bottom of the mode=0 user attendance report but are really struggling to produce the same values.

Tstudent16 is not a member of the group so should only have access to one attendance session.

Debug info:
SELECT rbalias11.shortname AS c0_shortname, rbalias11.id AS c0_courseid, rbalias12.id AS c0_ctxid, rbalias12.path AS c0_ctxpath, rbalias12.depth AS c0_ctxdepth, rbalias12.contextlevel AS c0_ctxlevel, rbalias12.instanceid AS c0_ctxinstance, rbalias12.locked AS c0_ctxlocked, rbalias5.firstname AS c1_firstname, rbalias5.lastname AS c1_lastname, rbalias4.pointsallsessions AS c2_pointsallsessions, rbalias4.totalnumsessions AS c3_totalnumsessions FROM {attendance_log} rbalias1 JOIN {attendance_statuses} rbalias3 ON rbalias3.id = rbalias1.statusid JOIN {attendance_sessions} rbalias2 ON rbalias2.id = rbalias1.sessionid JOIN {attendance} rbalias0 ON rbalias0.id = rbalias2.attendanceid JOIN {course} rbalias11 ON rbalias11.id = rbalias0.course LEFT JOIN {context} rbalias12 ON rbalias12.contextlevel = 50 AND rbalias12.instanceid = rbalias11.id JOIN {user} rbalias5 ON rbalias5.id = rbalias1.studentid JOIN ( SELECT course, studentid, allpoints, studentpoints, totalnumsessions, numsessionstaken, '' || studentpoints || ' / ' || allpoints AS pointsallsessions, '' || studentpoints || ' / ' || maxgrade * numsessionstaken AS pointstakensessions, maxgrade * (totalnumsessions - numsessionstaken) + studentpoints AS maxpossiblepoints, studentpoints / allpoints * 100 AS percentageallsessions, studentpoints / (maxgrade * numsessionstaken) * 100 AS percentagesessionscompleted, (maxgrade * (totalnumsessions - numsessionstaken) + studentpoints) / allpoints * 100 AS maxpossiblepercentage FROM ( SELECT a.course, atlo.studentid, sescount.count * stm.maxgrade AS allpoints, SUM(atst.grade) AS studentpoints, COUNT(DISTINCT atse.id) AS numsessionstaken, sescount.count AS totalnumsessions, stm.maxgrade FROM {attendance_sessions} atse JOIN {attendance} a ON a.id = atse.attendanceid JOIN {course} c ON c.id = a.course JOIN {attendance_log} atlo ON atlo.sessionid = atse.id JOIN {attendance_statuses} atst ON atst.id = atlo.statusid AND atst.deleted = 0 AND atst.visible = 1 JOIN ( SELECT attendanceid, setnumber, MAX(grade) AS maxgrade FROM {attendance_statuses} WHERE deleted = 0 AND visible = 1 GROUP BY attendanceid, setnumber ) stm ON stm.setnumber = atse.statusset AND stm.attendanceid = atse.attendanceid JOIN ( SELECT attendanceid, COUNT(1) AS count FROM {attendance_sessions} GROUP BY attendanceid ) sescount ON sescount.attendanceid = a.id GROUP BY a.course, atlo.studentid, sescount.count, stm.maxgrade ) sd ) rbalias4 ON rbalias4.course = rbalias0.course AND rbalias4.studentid = rbalias1.studentid WHERE (rbalias11.shortname ILIKE :c0_rbparam26 ESCAPE '')c0_rbparam26 => 'kj\_moodle\_45%'1.4818551540375 secs

[KJ Attendance Check.csv](https://github.com/user-attachments/files/17056582/KJ.Attendance.Check.csv)

I hope that makes some sense!
Kimberley