I am new to Gibbon and want to deploy this application in my college in near future. In our college students are offered different courses in a semester and in each course there are 8 classes per week. To appear in examination they must have certain percentage of attendance in each course. Is there any way in Gibbon so that we can get a course wise attendance percentage report for each individual student?
Hi Chinmoy, welcome to the Gibbon community 
Gibbon has a number of built-in attendance reporting options, however I don’t believe there’s a course breakdown by percentage. One place to start may be to download the Query Builder module from the Extend page: https://gibbonedu.org/extend/ This gives you the ability to write and run an SQL query to export data from Gibbon. There are also several pre-made queries available with a license key, which you can request by emailing support@gibbonedu.org These may not give you every type of export you need, but they’re a great starting point for modifying and adding your own queries.
Hope this helps!
Thanks Sandra for your comment. I have downloaded the query builder and trying to build my own query.
Best of luck! Here’s a link to a past discussion on writing attendance queries, and has an SQL snippet. It not identical to what you need but may help: https://ask.gibbonedu.org/discussion/2056/attendance-query
Hi Sandra,
I am just a beginner in SQL. With my limited knowledge and lots of googling I have worked out a query that is producing desired result.
I am copying it here for your perusal and to check whether there is any loop-holes in it or not:
SELECT CONCAT(gibbonPerson.firstName," ", gibbonPerson.surname) AS Name, gibbonSchoolYear.name AS Session, gibbonYearGroup.name AS Year, gibbonRollGroup.name AS Roll, gibbonstudentenrolment.rollOrder As No, gibbonCourse.name AS Course, COUNT(gibbonAttendanceLogPerson.direction) AS Total_Class,
sum(case when gibbonAttendanceLogPerson.direction = “In” then 1 else 0 end) Present,
sum(case when gibbonAttendanceLogPerson.direction = “Out” then 1 else 0 end) Absent,
ROUND(((sum(case when gibbonAttendanceLogPerson.direction = “In” then 1 else 0 end))/((sum(case when gibbonAttendanceLogPerson.direction = “In” then 1 else 0 end))+(sum(case when gibbonAttendanceLogPerson.direction = “Out” then 1 else 0 end)))*100),2) as Percentage
FROM (((((((gibbonAttendanceLogPerson
INNER JOIN gibbonCourseClass ON gibbonAttendanceLogPerson.gibbonCourseClassID=gibbonCourseClass.gibbonCourseClassID)
INNER JOIN gibbonCourse ON gibbonCourseClass.gibbonCourseID=gibbonCourse.gibbonCourseID)
INNER JOIN gibbonPerson ON gibbonAttendanceLogPerson.gibbonPersonID=gibbonPerson.gibbonPersonID)
INNER JOIN gibbonstudentenrolment ON gibbonAttendanceLogPerson.gibbonPersonID=gibbonstudentenrolment.gibbonPersonID)
INNER JOIN gibbonSchoolYear ON gibbonstudentenrolment.gibbonSchoolYearID=gibbonSchoolYear.gibbonSchoolYearID)
INNER JOIN gibbonYearGroup ON gibbonstudentenrolment.gibbonYearGroupID=gibbonYearGroup.gibbonYearGroupID)
INNER JOIN gibbonRollGroup ON gibbonstudentenrolment.gibbonRollGroupID=gibbonRollGroup.gibbonRollGroupID)
GROUP BY gibbonPerson.firstName, gibbonPerson.surname, gibbonSchoolYear.name, gibbonYearGroup.name, gibbonRollGroup.name, gibbonstudentenrolment.rollOrder, gibbonCourse.name
ORDER BY gibbonSchoolYear.name, gibbonYearGroup.name, gibbonRollGroup.name, gibbonstudentenrolment.rollOrder, gibbonCourse.name;`
The result is as shown below with my dummy data set:
Can such query be incorporated in gibbon side-bar menu to produce report?
Hi Chinmoy, well done on putting together what is quite a complex query. I’ve tweaked it in the following ways:
- Changed gibbonstudentenrolment to gibbonStudentEnrolment, as some mysql installs are case sensitive
- Adjusted the layout to make it easier to read
- Restricted the data to the current school year (you might not have noticed this if you are in your first year using Gibbon.
The result is attached. I wonder if @ross has any further input? My one concern is whether it works when students have multiple in/out records on a single day. I’ve not had time to look at that in detail…
If we can get it all finalised, then I can include it in Query Builder as a common query. Of course, you can add it for your own school if you like. In terms of adding as a report, this may be possible in v17, but we cannot add every possible report, otherwise the interface will become unwieldy.
Thanks,
Ross
Hi Ross,
Thanks for your opinion and correction in my humble query.
The layout got jumbled up while I copy-pasted it in the message box; sorry for that.
In case of adding as a report, I just want to made it available for other staffs of the school apart from “admin”. Other staffs could only run the query but they should not have the permission to view the code or change it.
I don’t want this particular query to be a part of core Gibbon, but want some means to made such custom built queries to be available to other user groups beside “admin” of that particular school. I think I can make you understand.
Thanks again and waiting for your response,
Chinmoy.
Hi Chinmoy,
This sounds like something my school could use as well, so I’ve put together a pull request to add these changes to the Query Builder module: https://github.com/GibbonEdu/module-queryBuilder/pull/10 This module update would be compatible with the upcoming Gibbon v16 release on June 20th.
Thanks!
Hi Chinmoy,
Amazingly, Sandra has come up with an amazing solution! I’ve now checked and released v1.5.01 of Query Builder, which is compatible with the latest commit of Gibbon’s Core v16.
In addition, I’ve added your query to Query Builder’s query set, under the name Class Attendance as Percentage, and it is live now. Sandra is going to do some further tests on it.
Great collaboration : )
Thanks,
Ross