Help configuring personalized reports!

Help configuring personalized reports!

by David Delcò -
Number of replies: 4

Hallo everybody!

I have some questions regarding the configurable reports plugin.

Since I don't know SQL language I need your help for some things, in particular to create two reports ...


But first... Is there a reference table with the name of the standard Moodle fields? smile


I need to build the following reports (or maybe someone has already done them):

A report must show which of the participants in a course has finished it but I am interested in the possibility of filtering by course, but also by department or service.

Display: Service, IDnumber, Last Name, First Name, Completion Date


Same thing for the activities completed by users

Display: Service, IDnumber, Last Name, First Name, Activity, Completion Date


It is possible to work also at the filter level of each single report, so as to obtain ev. results for a single Service or a single specific activity at a time?

Thank you for further help!

David

Average of ratings: -
In reply to David Delcò

Re: Help configuring personalized reports!

by Shirley Gregorczyk -
Picture of Particularly helpful Moodlers
Look through the list of available ad-hoc reports:
https://docs.moodle.org/39/en/ad-hoc_contributed_reports

Schema
https://www.examulator.com/er/
Average of ratings: -
In reply to Shirley Gregorczyk

Ri: Re: Help configuring personalized reports!

by David Delcò -
Hi Shirley,
Thank you for your answer.

Yes, I looked at the link you gave me and I find it interesting.

Something I have already found, but now I should modify it ... and this is a problem because I don't know the SQL code ... smile

I'll see if some good soul can help me wink

Good day.

Average of ratings: -
In reply to David Delcò

Re: Ri: Re: Help configuring personalized reports!

by Ron Meske -
Picture of Particularly helpful Moodlers
Hi David,

For your first report I had a similar need. Here is the SQL I was able to put together based on reviewing several other examples. Something to note, If you use multiple filters then a selection must be made in all of them, but you can Filter on Courses and one other and make a selection in one or both.

SELECT DISTINCT
course.fullname 'Course Name',
CONCAT(user.firstname, ' ', user.lastname) AS 'Learners Name',
user.email as 'Email',
user.city as 'City',
user.department as 'Department',
## FROM_UNIXTIME(completions.timeenrolled, '%m-%d-%Y') AS 'DateEnrolled',
CASE
WHEN completions.timecompleted IS NOT NULL THEN ( FROM_UNIXTIME(completions.timecompleted, '%m-%d-%Y'))
ELSE '-Not Yet Completed-'
END AS 'Completion Status'

FROM prefix_course_completions AS completions
JOIN prefix_course AS course ON completions.course = course.id
JOIN prefix_user AS user ON completions.userid = user.id 
AND user.deleted = 0 AND user.suspended = 0

WHERE (course.visible = 1) 
%%FILTER_COURSES:course.id%% 
%%FILTER_USERS:user.department%%

ORDER BY 'course name', 'learners name'



Average of ratings: -
In reply to Ron Meske

Ri: Re: Ri: Re: Help configuring personalized reports!

by David Delcò -

Hi Ron,

Thank you so much for your post and for the code. You have been very kind!

I will try to use it asap! For now I need a code to extend the days! 😱😀

Thanks again.

Average of ratings: -