I love this plugin. However, no luck writing custom SQL report. Seems like everything I try is wrong syntax (such as using semicolons). Where can I find the proper SQL syntax to use in this block for a custom report? All I ever see is common SELECT and JOIN statements. I need some variable defines, while, do, etc. My server is using MySQL 5.5 but, again, none of these commands seem to work and only generate errors that are too general to be of use.
As far as I know, the configurable reports plugin does not support transact sql, which means you are limited to basic SQL functions (SELECT, JOIN, etc.).
However, if you could share what you want your report to present, maybe someone can help you achieve the desired result within these limitations.
Thanks for the reply, Yaniv. I'm trying generate a report that provides a list of all users (first name, last name, ID#) and the total number of courses each user has completed. It would look like the following:
Name ID Completes
Bob Smith 12345 12
Jane Doe 42514 3
Ed Doe 06751 26
You could us this report on completions as a starting point
https://docs.moodle.org/31/en/ad-hoc_contributed_reports#User_Course_Completion
Then turn that third select column into a subquery using COUNT. That should do it.
Thanks, Randy. Don't know how I missed this one. I looked through all of these before - thanks - I think this might work.
Randy - thanks again for the help. I've made some changes to the basic query to get rid of some nulls. I am having difficulty figuring out how to use COUNT after I've retrieved the table.
The current query is:
SELECT u.lastname, u.firstname, c.shortname,
DATE_FORMAT(FROM_UNIXTIME(p.timecompleted
),'%Y-%m-%d')
AS completed
FROM prefix_course_completions AS p
JOIN prefix_course AS c ON p.course = c.id
JOIN prefix_user AS u ON p.userid = u.id
WHERE c.enablecompletion = 1 AND p.timecompleted != "NULL"
ORDER BY u.username
Returns (only a snip-it-not real names):
| lastname | firstname | shortname | completed |
|---|---|---|---|
| Here | Your Name | CASEMT101 | 2016-03-01 |
| DeBee | Aleta | SDHNDB101 | 2016-03-19 |
| Akers | Andrew | INTRCMPT101 | 2016-05-30 |
| Akers | Andrew | PUBSPK101 | 2016-05-29 |
| Akers | Andrew | PPT101 | 2016-05-29 |
| Akers | Andrew | CASEMT101 | 2016-05-21 |
| Akers | Andrew | KBBTT101 | 2016-05-29 |
| Akers | Andrew | ASTR101 | 2016-05-29 |
| Akers | Andrew | ASTR201 | 2016-08-15 |
| Akers | Andrew | OBPVOL101 | 2016-05-29 |
| Akers | Andrew | OBPFI201 | 2016-08-18 |
| Akers | Andrew | CONFPLN101 | 2016-05-29 |
I just can't figure out how to implement the COUNT function to give me a total rows for each user which would be equivalent to the total number of courses completed. I think my C programming is confusing my thoughts about how to go about it. This is a learning experience for me so I really appreciate your help.
I tried applying the COUNT function in several different ways but it keeps giving me the total for the whole table. I can't figure out how to break up the query to give me separate totals for each user.
Thanks again for your help in advance.
Ken,
First, for NULLs you use the syntax IS or IS NOT to check. NULLs aren't values but the presence or absence of a value, so you can't use evaluation operators like '=' on them.
See here for a nice sample: https://docs.moodle.org/31/en/ad-hoc_contributed_reports#Course_Completion_Report_with_custom_dates
Secondly, COUNT() with a GROUP BY will do the heavy lifting for you when checking the timecompleted column. You want a count for each user.
This is the heart of a basic working query that counts how many completions each user has:SELECT p.userid, COUNT(p.timecompleted) AS TotalCompletions
FROM prefix_course_completions AS p
GROUP BY p.userid
Then add a join to get more user info, and a where to limit those nulls if you want.
-Randy
Thanks for the education, Randy!
Thanks to you I was both able to get this query running and - most importantly - understand the issues I was having too. I spent days trying to come up to speed and was making very little progress. I can now take what I've learned and build out more projects that need to be completed.
Much Thanks!
Ken,
Great. Glad that is working.
Do consider sharing the code over in the ad-hoc reports area, since there isn't one just to do this count yet.
Regards,
Randy
Will Do, thanks.
Well here is the final code - I'm still trying to figure out how to get it over to the ad hoc area ....
Show a count of the number of completed courses by user;
SELECT u.lastname, u.firstname,
COUNT(p.timecompleted) AS TotalCompletions
FROM prefix_course_completions AS p
JOIN prefix_user AS u ON p.userid = u.id
GROUP BY p.userid
ORDER BY u.lastname