Full Support Needed: Creating a Weekly Student Progress Report (Custom Layout) - Moodle 5.1.1+

Full Support Needed: Creating a Weekly Student Progress Report (Custom Layout) - Moodle 5.1.1+

by Anam Zehra -
Number of replies: 8

Hi ,

I am currently using Moodle 5.1.1+ and I am looking to create a specific Weekly Student Progress Report that follows a portrait layout, similar to a traditional  report card. I have attached a sample image of the design I am aiming for.

Requirements:

  1. Header Section: It should dynamically display the Student's Name, Father's Name (from a custom profile field), and the Date Range for the week.

  2. Report Table: A table that lists:

    • Course Name: Only courses the student accessed or worked on during that specific week.

    • Lead: The name of the Teacher/Lead for that course.

    • Attendance (Mon-Sunday): A daily status (P for Present if they logged into the course, A for Absent if they didn't).

    • Grades: The average grade for the course (since some courses have 2 levels and some have 3, I just need the overall average).

My Request: I am looking for a complete, step-by-step solution. Could you please provide everything needed to achieve this? Specifically:

  • The exact SQL query (if using Configurable Reports or Ad-hoc reports).

  • Any custom code or CSS needed to make the layout portrait and match my attached sample.

I need a detailed guide to implement this from scratch on my Moodle site. Any help with the technical details would be highly appreciated.

Thank you!

Attachment Green Dots Illustrative Modern Preschool Report Card.jpg
Average of ratings: -
In reply to Anam Zehra

Full Support Needed: Creating a Weekly Student Progress Report (Custom Layout) - Moodle 5.1.1+

by Dave Foord -
Picture of Particularly helpful Moodlers
I think you are asking quite a lot here, so unlikely that someone will be able to provide a full solution for you for free. Who would be viewing the report - is it the student, or the parent or a teacher.

I expect you would need to create this as 2 reports, which you would then embed into a single page to make it look like one report. the top bit being 1 report, and the table being the 2nd report.

If the student is viewing their own report, then you can easily add a filter in to only show the data to the person viewing. If you want the other options, then this will be trickier, as you will need to add a filter mechanism in to choose the desired student, but I am not sure how to apply this to both reports at the same time.
Average of ratings: Useful (1)
In reply to Anam Zehra

Full Support Needed: Creating a Weekly Student Progress Report (Custom Layout) - Moodle 5.1.1+

by Anam Zehra -

"Thank you for the reply. To clarify, this report is intended for Teachers and Administrators to track student progress, but we also want Students to be able to view their own weekly performance.

I understand it might be complex, but if you could provide the SQL query for the table part (attendance and grades) first, that would be a great start. I am using the Configurable Reports plugin. How can I pull the 'Father's Name' from the custom profile field into the first part?"

Average of ratings: -
In reply to Anam Zehra

Full Support Needed: Creating a Weekly Student Progress Report (Custom Layout) - Moodle 5.1.1+

by Rick Jerz -
Picture of Particularly helpful Moodlers Picture of Testers
Students have their grade book that if the instructor is keeping up with grading, will show students where they currently stand.

I'm a little puzzled by your mockup, since it shows daily progress. I wonder if most students would focus on their current grade and not care what their grade was six days ago. Moodle contains some kind of "grade history" feature, but I haven't ever found a need to use it. The grade book suffices.

Teachers and administrators can also look at the students' gradebook, the "grader report," to get an overview of where students stand. The spreadsheet-like report can be sorted, too. This report can also be exported if teachers and administrators prefer to use their favorite spreadsheet program to view it.
Average of ratings: -
In reply to Anam Zehra

Full Support Needed: Creating a Weekly Student Progress Report (Custom Layout) - Moodle 5.1.1+

by Dave Foord -
Picture of Particularly helpful Moodlers

Writing the code to create the attendance/grade part is a few hours of work - so not something that I can do for free for you (and I doubt anyone else will be able to offer this for free, unless they have created something in the past that is very close and easy to adapt).

In terms of pulling data from a custom user profile field, this is easy and quick for me to share. There are a few different ways that people go about this, but I personally use the following method - as easy to replicate and add additional fields into the query as required, and I find relatively easy to read:

The following code, will give you a list of users on the site who are not suspended with their: ID, Firstname, Lastname, Fathers name and Date of Birth. (I added in Date of birth to show how you add additional fields in). 

----

SELECT

u.id AS 'userID',
u.fistname,
u.lastname,
uid2.data AS "Father_name",
uid3.data AS "Date of Birth"

FROM prefix_user AS u

JOIN prefix_user_info_data AS uid2 ON uid2.userid = u.id
JOIN prefix_user_info_field AS uif2 ON uid2.fieldid = uif2.id
  
JOIN prefix_user_info_data AS uid3 ON uid3.userid = u.id
JOIN prefix_user_info_field AS uif3 ON uid3.fieldid = uif3.id


WHERE u.suspended = 0
AND u.deleted = 0
AND uif2.shortname = "parent"
AND uif3.shortname = "dob"

----

So there are 3 parts to adding in the custom field - I have highligthed these in bold above

  1. In the 'Select' part we add uid.data2 AS 'Whatever name you want the column to be headed with'
  2. We add a pair of 'JOIN' lines for each custom field (just copy and paste from above - this will always be the same)
  3. In the 'conditions' we add 'uif2.shortname' = "Whatever the shortname is for your custom field"

In the example above the assumption is that the short name for the Fathers name field is 'parent' and the shortname for Date of Birth is 'dob'

If you want to add a new field in, then copy the 'bold' lines of code, then paste them but change the 2 to a 3, update any other changes and you have added in a second profile field (shown above in italics).

Average of ratings: -
In reply to Dave Foord

Full Support Needed: Creating a Weekly Student Progress Report (Custom Layout) - Moodle 5.1.1+

by Anam Zehra -

"Thank you so much for sharing the SQL code for the custom profile fields! It works perfectly for the header part.

I understand that a full daily (Mon-Sun) breakdown is complex to code for free. However, could you please help me with a simpler version of the Attendance and Grade table?

Instead of daily columns, could you provide a query that shows:

  1. Course Name

  2. Final Grade (Average)

  3. Attendance Status: Just a simple count or percentage of how many times the student accessed the course in the last 7 days?

If I can get these three things in a table, I can manage the rest. Also, could you tell me how to JOIN the grades table with the user query you provided?

Thank you again for your time and guidance!"

Average of ratings: -
In reply to Anam Zehra

Full Support Needed: Creating a Weekly Student Progress Report (Custom Layout) - Moodle 5.1.1+

by Dave Foord -
Picture of Particularly helpful Moodlers

The Moodle community is great because lots of people help other users - I regularly help others as and when I can, and similarly, I am grateful for the help I receive from others.

But - what you are asking is still multiple hours of work, and something that goes above and beyond simple advice, and as such I cannot offer this for free. Even if I did have some code that I could share with you, it is unlikely to work first time for you, so would require some back and forth adjustments and tweaks to get it right.

I am a freelancer who makes a living from this sort of thing, hence I cannot dedicate hours to such requests for free. If you have a budget for this, then happy to have a conversation offline, or there are lots of other freelancers out there who could help.

Average of ratings: -
In reply to Dave Foord

Full Support Needed: Creating a Weekly Student Progress Report (Custom Layout) - Moodle 5.1.1+

by Anam Zehra -

"I completely understand and respect that this is your professional work and time is valuable. Thank you for being honest about it!

Since I don't have a budget for a freelancer at the moment, I will try to build it myself piece by piece using the Configurable Reports plugin.

Could you please just point me in the right direction regarding the database tables?

  1. Which table should I look into for Course Access/Logs to determine if a student was 'Present' on a specific date?

  2. Which table stores the Final Course Grade (the average you mentioned)?

If you can just give me the table names, I will try to write the SQL myself. Thank you again for the 'Father Name' code, it was a great help!"

Average of ratings: -