Hi @Dechen,
If your goal is to generate reports based on calculations performed on grades in the Markbook—enabling you to visualize them instantly and export the results to Excel—one approach is to use the Query Builder module. Below is an example query you can build upon, followed by its output.
WITH RankedResults AS (
SELECT
gibbonSchoolYearTerm.name AS Term,
gibbonCourse.nameShort AS Course,
gibbonPerson.Surname AS Surname,
gibbonPerson.PreferredName AS PreferredName,
CONCAT(
ROUND(MAX(IF(gibbonMarkbookColumn.name = 'Language skill 25%',
CAST(REPLACE(gibbonMarkbookEntry.attainmentValue, '%', '') AS DECIMAL),
NULL)), 1), '%'
) AS Language_Skill,
CONCAT(
ROUND(MAX(IF(gibbonMarkbookColumn.name = 'Effort 35%',
CAST(REPLACE(gibbonMarkbookEntry.attainmentValue, '%', '') AS DECIMAL),
NULL)), 1), '%'
) AS Effort,
CONCAT(
ROUND(MAX(IF(gibbonMarkbookColumn.name = 'Behaviour 40%',
CAST(REPLACE(gibbonMarkbookEntry.attainmentValue, '%', '') AS DECIMAL),
NULL)), 1), '%'
) AS Behaviour,
CONCAT(
ROUND((
MAX(IF(gibbonMarkbookColumn.name = 'Language skill 25%',
CAST(REPLACE(gibbonMarkbookEntry.attainmentValue, '%', '') AS DECIMAL),
NULL)) * 0.25 +
MAX(IF(gibbonMarkbookColumn.name = 'Effort 35%',
CAST(REPLACE(gibbonMarkbookEntry.attainmentValue, '%', '') AS DECIMAL),
NULL)) * 0.35 +
MAX(IF(gibbonMarkbookColumn.name = 'Behaviour 40%',
CAST(REPLACE(gibbonMarkbookEntry.attainmentValue, '%', '') AS DECIMAL),
NULL)) * 0.4
), 1), '%'
) AS Total,
ROW_NUMBER() OVER (
PARTITION BY gibbonCourse.nameShort, gibbonPerson.Surname, gibbonPerson.PreferredName
ORDER BY gibbonSchoolYearTerm.name ASC
) AS rankOrder
FROM
gibbonMarkbookColumn
LEFT JOIN gibbonSchoolYearTerm
ON gibbonMarkbookColumn.gibbonSchoolYearTermID = gibbonSchoolYearTerm.gibbonSchoolYearTermID
JOIN gibbonCourseClass
ON gibbonMarkbookColumn.gibbonCourseClassID = gibbonCourseClass.gibbonCourseClassID
JOIN gibbonCourse
ON gibbonCourseClass.gibbonCourseID = gibbonCourse.gibbonCourseID
JOIN gibbonMarkbookEntry
ON gibbonMarkbookEntry.gibbonMarkbookColumnID = gibbonMarkbookColumn.gibbonMarkbookColumnID
JOIN gibbonPerson
ON gibbonMarkbookEntry.gibbonPersonIDStudent = gibbonPerson.gibbonPersonID
GROUP BY
gibbonSchoolYearTerm.name,
gibbonCourse.nameShort,
gibbonPerson.Surname,
gibbonPerson.PreferredName
)
SELECT
r1.Course,
r1.Surname,
r1.PreferredName,
r1.Term,
r1.Language_Skill,
r1.Effort,
r1.Behaviour,
r1.Total,
CASE
WHEN r1.rankOrder > 1 THEN
ROUND((CAST(REPLACE(r1.Total, '%', '') AS DECIMAL) - CAST(REPLACE(r2.Total, '%', '') AS DECIMAL)) / CAST(REPLACE(r2.Total, '%', '') AS DECIMAL) * 100, 1)
ELSE NULL
END AS Improvement
FROM
RankedResults r1
LEFT JOIN
RankedResults r2
ON r1.Course = r2.Course
AND r1.Surname = r2.Surname
AND r1.PreferredName = r2.PreferredName
AND r1.rankOrder = r2.rankOrder + 1
ORDER BY
r1.Course,
r1.Surname,
r1.PreferredName,
r1.Term;
+++++++++++++++++++++++++++++++++++
These are the only columns you need in the gradebook for each term/semester:
+++++++++++++++++++++++++++++++++++
In the Query Builder module, we calculate the [Total 100%] and [Improvement] values for consecutive terms, using the formulae provided.
I hope this helps,
Tieku
I don’t know how difficult would it be to do something similar that would pull information from the reports’ data? I am not sure which interface the teachers will prefer and what will work best long term…
