Course Completion Report?

Course Completion Report?

by Tania Wiese -
Number of replies: 6
Hi All,

I can't find the right configurable report to be able to run a report along these lines -

I'd like one that shows user name & email; course short name & long name; Course Completion status & date.

And be filterable by category, course, completion status, and to be able to run it as "all completions after / before such and such a date".

I'm struggling a lot to find this on moodle.org and have no idea how to write my own SQL....

Thanks smile
Tania


Average of ratings: -
In reply to Tania Wiese

Re: Course Completion Report?

by Peter Bowen -

Hi Tania,


This might set you on the right track.


SELECT CONCAT(u.firstname, ' ', u.lastname) AS Name, u.email, c.fullname AS 'Course Name', c.shortname AS 'Short Name', ccat.name AS 'Category',

IF (cc.timecompleted=0,"",DATE_FORMAT(FROM_UNIXTIME(cc.timecompleted), '%e %M %Y')) AS 'Date Completed',

IF ((cc.timecompleted=0 OR cc.timecompleted IS NULL), IF(cc.timestarted=0,"Not started","Not complete"), "Complete") AS Completed

FROM (prefix_user AS u
INNER JOIN (prefix_course AS c
INNER JOIN prefix_course_completions as cc
ON c.ID = cc.course)
ON u.ID = cc.userid)
INNER JOIN prefix_course_categories AS ccat
ON ccat.id=c.category

WHERE u.id<9

%%FILTER_COURSES:c.id%%


%%FILTER_USER:CONCAT(u.firstname, ' ', u.lastname)%%

%%FILTER_CATEGORIES:ccat.id%%
%%FILTER_STARTTIME:cc.timecompleted:>%% %%FILTER_ENDTIME:cc.timecompleted:<%%


You will also need to add the filters

  • category
  • course
  • start / end date
  • User name details

Hope this helps

Cheers
Peter


Average of ratings: -
In reply to Peter Bowen

Re: Course Completion Report?

by Darren Hedden -

Hi there Peter,


Thanks so much for this. I have been tinkling around for a while trying to get this kind of report with little success so hence this has been my saviour!


Just one quick question. I have custom profile fields that I would like to show - line manager for example,  within the report. I think I add the code u.xxxxxx where xxxxx is the profile field name but can you maybe offer me a pointer on this?

Also, is it then possible to add a filter on this? Can you add multiple filters on the user field filter? Seems like you can only add the one as once you add the username option, it doesn't allow you any extras? I could switch the username one to the line manager field I guess, but would be nice to have multiple user based filters

Again, thanks so much for this. Great help

Darren
Average of ratings: -
In reply to Darren Hedden

Re: Course Completion Report?

by Peter Bowen -

Hi Darren,

We use a number of custom fields. The code to add them would be:

INNER JOIN prefix_user_info_data AS aa
ON u.ID = aa.userid AND aa.fieldid=4

You would need to find the fieldid of the specific custom field you are wanting - found in the mdl_user_info table.

The way I worked multiple filters, was to duplicate the code for the filters into a new directory, with slight renaming, so I actually ended up with 5 filters I could use.

Cheers
Peter

Average of ratings: -
In reply to Peter Bowen

Re: Course Completion Report?

by Peter Bowen -

I just spotted on the above

WHERE u.id<9


We use user id's under 9 for admin type users, so they never show up in these reports - you may need to be careful just copying and pasting this report.


Cheers
Peter

Average of ratings: -
In reply to Peter Bowen

Re: Course Completion Report?

by Matt Polaniecki -

Hey, thank you for posting this! It seems really helpful. I am kinda new to this plugin so I am having a hard time knowing what to do with this.

I can just paste it into the SQL query editor? When I view the report it says there are no records found. What are the steps to implement this?

Thank you!

Average of ratings: -