## General plugins

The idea of this report is that administrators can set up pre-defined SQL queries that other people can then go in and execute.

This lets you easily create new reports where there is not already an admin report plugin that does what you want. Naturally formatting is a bit basic, but it is good enough for most things.

Anyway, the OU wanted it, I did it, and now you can have it: http://moodle.org/mod/data/view.php?d=13&rid=2884. Naturally, the download zip file will not be ready until tomorrow.

Average of ratings: Useful (9)
This feature is great. I've been wanting it for ages and we finally were able to get the right people to request it so that we were allocated a few days to develop it. Thanks Tim.

--sam

Average of ratings: -
I dropped this into .../quiz/report but it doesn't work (Report not known). Is there another step? When I logged in as admin and visited notifications there was no install message. 1.9.6.

Average of ratings: -
Just had it pointed out to me that this is an Admin report.

Installs as expected when dropped into admin/report. Will play later.

Average of ratings: -
Thanks Tim, this is really useful and will drop my use of PHPMyAdmin down considerably.

Paul.

Average of ratings: -
I suspect this will produce a raft of sql queries being shared around to produce reports, really nicely done.

Average of ratings: -

Tim,

this is excellent. This could form the basis for a little report generator that is long overdue in Moodle.

Alex - Synergy Learning

Average of ratings: -

Hi I am new to Moodle but I have to hopefully build a report generator in Moodle. Is it possible and you could point me in the right direction

It is a fairly basic Database

It requires approx 60 choosable 1 line fields (maybe a checkbox) from which they can only choose 25 and from that it will allow them to then print just those 25 single pages

Average of ratings: -
I just uploaded a new version. The new feature is that you can now set up scheduled queries that are automatically run on the first day of the week or month. First day of the week is defined as midnight between Friday/Saturday for internal OU reasons, but there is a fairly obvious constant at the top of locallib.php to hack if you want to change this.

There is also now documentation: Custom_SQL_queries_report, and a component in the tracker for bug reports: http://tracker.moodle.org/browse/CONTRIB/component/10540

Finally, standard disclaimer, the download package won't be ready until tomorrow, but it is in CVS now.

Average of ratings: Useful (1)

I tried to install this reporting module, but it looks like my moodle version is not quite new enough ...

Plugin "customsql" (2009102801) could not be installed. It requires a newer version of Moodle (currently you are using 2007101513, you need 2007101550).

Any idea what the differences are in between 513 and 550 that would restrict this from running? I am not in a position to upgrade the version of moodle that I am running due to customizations.

Rob

Average of ratings: -
Oh, I just copied and pasted the number from the top-level version.php into my plugin's version.php without thinking.

I suggest you try changing the

$plugin->requires = ... line in admin/report/customsql/version.php and see if it works. Average of ratings: - Re: I just made a new admin report Perfect. much better. Thanks; Rob Average of ratings: - Re: I just made a new admin report BRILLIANT! Just what I was looking for! Thanks Tim. Average of ratings: - Re: I just made a new admin report Great plugin. A great addition to it would be the ability to use cfg variables in the query so that you can create sql querries that are dynamic. Average of ratings: - Re: I just made a new admin report Patches welcome. Please attach to a MDLSITE- tracker issue. It's not a proper OU plugin until it is gold-plated, diamond studded, platinum encrusted, ... Average of ratings: - Re: I just made a new admin report Is there a central location that discusses and links to all the possible reporting options for Moodle - current, in development, and under discussion? I expect to be pointed to the forums, of course - my question stems from the desire for a more comprehensive view of the state of affairs. I am aware of the dev project for a custom reporting tool. Thank you! Average of ratings: - Re: I just made a new admin report I don't think anyone knows. To find out, you would need to search these forums and the Moodle docs wiki, and ask a bunch of people. If anyone did all that, then they could write an article on the state of play. Average of ratings: - Help with scheduled reporting Great reporting tool Tim. About the scheduled reporting, Wondering if Its possible to change the sceduled time to say lastday of the week (friday). Thanks Average of ratings: - Re: Help with scheduled reporting I think if you look in the code, there is a simple constant to set which day of the week is used. Average of ratings: - Re: Help with scheduled reporting Thanks for the feedback Tim. I 'm very grateful. One more question Tim. Could you advice on this sql query please. Basically it sought to find out the number of students who have completed and passed each module at a particular time. Its however relies on the certificate issued table. I donot want to rely on certificates issued because there are chances of a student completing a quiz but not accesss his certificate . Cant seems to figure it out using the quiz tables. SELECT c.name AS 'COURSE NAME', Count(c.name) AS 'NO OF COURSE COMPLETERS ' FROM user u, certificate_issues ci, certificate c where u.id = ci.userid AND ci.certificateid = c.id GROUP BY c.name Any help will be highly appreciated. Thanks Average of ratings: - Re: I just made a new admin report Yes, as everyone is saying, it's great to see this development - well done Tim! I just tried a very simple report: SELECT firstname,lastname,email FROM prefix_user What surprised me is that some records returned email addresses, and others did not!? Although all users do have an email, and I can see it's active in their profile. Is there something special about the email field I'm not aware of maybe? Any ideas... Thanks, Stu. I agree with the earlier comment, it will be awesome if we can have a location to share these Custom SQL queries - administrators sharing cool queries similar to the way teachers share Glossaries and Database Presets Stu Average of ratings: - Re: I just made a new admin report Try adding WHERE deleted = 0 to the end of the query. A place to share custom queries is a good idea. I added as section at the end of the docs page Custom_SQL_queries_report#Share_your_interesting_queries_here. Average of ratings: Useful (1) Re: I just made a new admin report Thanks Tim. I've installed the report and it works nicely for the custom queries that I wrote. I'm quite impressed! Then I tried to take it one step further by using the Quiz attempts in the last week/month report that is published on http://docs.moodle.org/en/Custom_SQL_queries_report#Quiz_attempts_in_the_last_week.2Fmonth I copied and pasted the sql: SELECT COUNT(*) FROM prefix_quiz_attempts WHERE timefinish > %%STARTTIME%% AND timefinish <= %%ENDTIME%% AND preview = 0 into the Query SQL textarea. When I click the Save Changes button I get a "Error when executing the query: Incorrect syntax near 'STARTTIME'." message. How do we get queries to work with variables like the above %%STARTTIME%% and %%ENDTIME%%? How and where do we define STARTTIME and ENDTIME? My database is MSSQL 2005 Thanks. Average of ratings: - Re: I just made a new admin report You have to replace %%STARTTIMME%% and %%ENDTIME%% with actual dates, for example, "1/1/2009" Average of ratings: - Re: I just made a new admin report Why didn't I think of that! But I have to use Unix dates instead of the "x/x/xxxx" format since Moodle stores dates in the Unix format. I would like to find a way to pass dates to the report without having to hardcode them into the sql. That way I wouldn't have to edit the report each time I want to run it... Average of ratings: - Re: I just made a new admin report No, that is wrong. You have to set the report type to one of the Scheduled options, rather than Manual, for the %%STARTTIMME%% and %%ENDTIME%% constants to work. (The run cron, to get some data.) I ought to add an interesting query that works in manual mode. OK, done. Average of ratings: Useful (1) Re: I just made a new admin report Thanks Tim - worked perfectly. Although I'm not sure why! More reading required... Thanks for setting up the 'shared queries' area too Cheers, Stu. Average of ratings: - Re: I just made a new admin report Cheers Tim, this is really useful It doesn't seem to let you specify a LIMIT clause on any of the queries though - would be useful, rather than it listing the first 5000 records (or whatever the default is). Average of ratings: - Re: I just made a new admin report Yes. I guess it would be better to have the limit in the query definition and another field, with a default of 5000. Reasonable idea, but I don't have time to implement it. Average of ratings: - Re: I just made a new admin report I'm trying to use this module to have supervisors see what certificates their employees has received. Is there any way to have the query "know" who is accessing the query and use that user id in the query? Similar to replacing %%WWWROOT%%with the domain? Maybe some way to define %%USERID%%or something along those lines. It would make it much easier than creating a different query for EVERY supervisor. Thanks. Average of ratings: - Re: I just made a new admin report That is a good idea, but it is not currently implemented. Please could you create a feature requests at http://tracker.moodle.org/. Put it in the CONTRIB project, in the appropriate component, and I will see what I can do. Average of ratings: - Re: I just made a new admin report I've added the request here. This is the first time I've added any requests or bugs so, if anything needs to be added/changed let me know and I can do it. Thanks, Jeremy Average of ratings: - Re: I just made a new admin report I had a conference call I had to attend, and managed to implement this in parallel. Average of ratings: - Re: I just made a new admin report This looks really useful - thanks! Started playing around with it but noticed that it was visible to Student. Not sure why. So I edited permissions/define roles to set this Report to "prevent" and then "prohibit" for Student role but it had no effect. I then cured it by adjusting the block Site Administration with an override permission to prevent a Student from seeing that block -which they would not have seen normally. So maybe I missed somethng really obvious. this on 1.9.5+ (Build: 20090930) Average of ratings: - Re: I just made a new admin report The problem is probably not with the student role, but with the Authenticated user role. Average of ratings: - Re: I just made a new admin report i noted that View for this new report was enabled in Authenticated User so I switched if off and problem solved - thanks again. Average of ratings: - Re: I just made a new admin report Hello, i have downloaded the package from modules and plugin database, unziped it installed well. When i tried to add a query, i always got the error ""Please use prefix_ in the SQL, not ." I tracked down the error to the validation function, that doeas a preg_match even when$CFG->prefix is empty. The patch below corrected the issue for me:

diff --git a/edit_form.php b/edit_form.php
index f7d6c37..a244a1d 100644
--- a/edit_form.php
+++ b/edit_form.php
@@ -88,7 +88,7 @@ class report_customsql_edit_form extends moodleform {
$errors['querysql'] = get_string('nosemicolon', 'report_customsql'); // Make sure prefix is prefix_, not explicit. - } else if (preg_match('/\b' .$CFG->prefix . '\w+/i', $sql)) { + } else if ($CFG->prefix != '' && preg_match('/\b' . $CFG->prefix . '\w+/i',$sql)) {
$errors['querysql'] = get_string('noexplicitprefix', 'report_customsql',$CFG->prefix);

// Now try running the SQL, and ensure it runs without errors.


Average of ratings: -
I did not consider that. Thank you for the bug report and fix.

I need to deal with this when I am at work, which probably won't be until Friday. So I created CONTRIB-1794 to make sure I don't forget about this. However, I will probably still forget, so don't be afraid to remind me if nothing happens. Thanks.

Average of ratings: -
Fix now in CVS. Will be in the zip downloads from tomorrow.

Average of ratings: -

but i am not able to view cutomsql from administration block..

Version - 1.9

Average of ratings: -
It should work. Do you mean 1.9.0? If so, there might be a bug. Can you try upgrading to 1.9.7?

Otherwise, you can still get to the report by manually entering the URL .../admin/report/customsql/index.php

Average of ratings: -

Hi..
But
1. I am using moodle 1.9.7
2. If i try to access it by entering url it gives Access Denied error.

Average of ratings: -
Ah, well that is at least consistent.

It should show up in the admin menu if, and only if, you are allowed to access it.

You need to work out why your current user account does not have the 'report/customsql:view' capability.

Average of ratings: -

HI Tim,

I too have the weird situation.. it's not displaying in the reports section and neither or no such entries in managing the roles...

1.9.6 November 24, 2009 weekly build

I installed it okay on another the exact same system (hardware, release, etc.) with no problem... weird!

Average of ratings: -

I figured it out ... it was a bad block we installed a few days ago.

turned on debugging on screen and that gave me the hint it never went to install even.

whew ...

Average of ratings: -

Thanks very much for this Tim. This is a particularly useful tool for moodle application admins who are not also skillled db admins. We are using it on our development instance until we're really, really sure that an aberrant query won't break the live production instance for 12,000 users. We're almost convinced

We've just run a report, like that on your blog, to see how soon before the deadline our students are submitting their quizzes. Answer: mostly at the last minute, interestingly, unlike OU.

We'd also like to collect the same type of information for assignment submissions. Any chance you could share with us the query for that? One more addition to the, hopefully, growing repository of queries? ... Alan

Average of ratings: -
If your quizzes have short time limits, like a few hours, rather than days, then it might be better to change the query so that is uses some time period other than hours for the analysis. You just need to change the 3600, and other numbers in the query, to something else.

I know almost nothing about how the assignment module works, but, guessing from the database table definitions, something like:

-- Number of prefix_assignment submissions by hour before deadline
SELECT
(a.timedue - as.timecreated) / 3600 AS hoursbefore,
COUNT(1)

FROM prefix_assignment_submissions as
JOIN prefix_assignment a ON a.id = as.assignment

WHERE
a.timedue <> 0

GROUP BY
(a.timedue - as.timecreated) / 3600

HAVING (a.timedue - as.timecreated) / 3600 < 24 * 7

ORDER BY
hoursbefore

-- Number of prefix_assignment submissions by hour of day
SELECT
DATE_PART('hour', TIMESTAMP WITH TIME ZONE 'epoch' + timecreated * INTERVAL '1 second') AS hour,
COUNT(1)

FROM prefix_assignment_submissions

GROUP BY
DATE_PART('hour', TIMESTAMP WITH TIME ZONE 'epoch' + timecreated * INTERVAL '1 second')

ORDER BY
hour

(Assumptions there are:
* timecreated is when the student last submitted their work. However, it might need to be timemodified instead
* there is only one assignment_submissions row for each student for each assignment. If not, a more complex query would be needed

By the way, I have not tested those queries, there may be silly errors there.)

Average of ratings: -

Hello Tim,

Thanks for create so useful plugin, I want to try it but I  haven't been able to install it yet, I unzipped and copied it to moodle/admin/reports, have gone to 'Notifications' but nothing happens, my moodle version is Moodle 1.9.5 (Build: 20090520), is this correct for the plugin? How can install it successfully?

Esteban

Average of ratings: -
This report does not have any database tables of its own... oh, but it does define a new capability, and so you should see something on the admin notifications page.

You do need a version of Moodle with MDL-17372 fixed, but 1.9.5 should be fine.

Sorry, I don't understand why it is not working. Can you verify that admin/index.php contains the line:

upgrade_plugins('report', $CFG->admin.'/report', "$CFG->wwwroot/$CFG->admin/index.php"); Average of ratings: - Re: I just made a new admin report Hi, The file already contains that line, and that should make the "installation" but nothing new appears at admin/report. What can be wrong? Average of ratings: - Re: I just made a new admin report Have you tried with Debugging turned on? Actually, the report does have database tables, to store the queries you have defined, so you really should see something when you install it. So, to recap, what you should have done is: 1. put the files into admin/report, so, for example, the files admin/report/customsql/db/install.xml and admin/report/customsql/version.php exist. 2. go to the Admin notifications page (top of the admin menu). At this point you should see the install happen. 3. Then the report should appear as 'Ad-hoc database queries' under Reports in the admin menu. Average of ratings: - Re: I just made a new admin report I did what you suggested, turned debugging on and notifications page showed this: Notice: Undefined index: QUERY_STRING in C:\Inetpub\wwwroot\moodle\auth\cas\CAS\CAS.php on line 10 that's the error, I don't know what CAS is. Average of ratings: - Re: I just made a new admin report That is one of the authentication plugins. Anyway, it is only a notice, which is the lowest grade of PHP debug output, so I don't think that will be causing any problems. What else to check? ... Have a look the the define role page for any role. Do you see a capability report/customsql:view there? Also, have a look in the mdl_config table in the database. Is there a row with name report_customsql_version there? (There should only be if the report has been succesfully installed, otherwise it should not be there.) For that matter, do you have a table called mdl_report_customsql_queries in your database? Average of ratings: - Re: I just made a new admin report Hi, I checked what you said and moodle don't have that capability, and the database don't have that table, and neither the row in the mdl_config table, it's liek nothing happened when copied the customsql folder, in fact, nothing happened, so I'm clueless. Average of ratings: - Re: I just made a new admin report Weird. So moodle is completely ignoring the new report and not installing it. I can't think why that might be. Average of ratings: - News Hi Tim, The news are that I made the report to work, BUT, not in the correct way, I typed the path in the browser, the path is this: http://root/moodle/admin/report/customsql/index.php and of course, the customsql folder is pasted into C:\Inetpub\wwwroot\moodle\admin\report\customsql, with this trick I made the report to work and already created one query, and "Ad-hoc database queries" item appears in the admin-report list, but when I navigate away from this page, this item disappears, and all the work and queries gets lost, but the table mdl_report_customsql_queries is created now and has this only record from the query I created, even the permissions report/customsql:definequeries and report/customsql:view now exist but when I go to Notifications, nothing happens and the 'Ad-hoc database queries' is not shown again. Summarizing, it only works when I make it work, working the path admin/report/customsql/index.php in the browser. How can I make this report to work in the appropriate way? I hope you understand my english and my explanation, thanks so much for your help Average of ratings: - Re: News Presumably, if you manually go to the URL http://root/moodle/admin/report/customsql/index.php again, the query you created before is still there. So, the only issue is, why does the link to the report not show up in the admin menu when you are not on the report page? I don't see how that can be a bug in the report code. The bit of the report that gets it to show up in the admin menu is the file admin/report/customsql/settings.php, which is very short. And it works when I try it. If you have the time, could you try doing an test install of the latest 1.9.x, and then install the report into that. That might help us work out why it does you work on your system. Average of ratings: - Re: News That's right, since the records are created in the database, when I go manually to that URL, the queries are there. I installed another moodle here (same version), with the same configurations and I installed the report and no errors were shown, now I think that it can be an incompatibility with some other module, can it be possible? Because I have reports data, have permissions, have almost all, it's only the item in the admin menu, and probably the language files but the report works, in its particular way. Thanks so much for helping Average of ratings: - Re: I just made a new admin report Tim, I am using moodle 2.2, installed, is working. My issue is trying to get the manager role to see it. I have been fiddling with the access.php file, capabilities array all day, my version # is up to 15 if thats a clue. I am back to the default and still trying to update to allow my managers to see this in their Admin. Reports menu. this is what the access.php file looks like right out of the zip file.$capabilities = array(

// People who can view the reports at all.
'report/customsql:view' => array(
'contextlevel' => CONTEXT_SYSTEM,
'legacy' => array(
)
),

// People who can define new queries.
'report/customsql:definequeries' => array(
'riskbitmask' => RISK_PERSONAL | RISK_DATALOSS | RISK_CONFIG,
'captype' => 'write',
'contextlevel' => CONTEXT_SYSTEM,
'legacy' => array(
)
)
);

Average of ratings: -

You should not fiddle with the access.php file to change what users can do on your site. Instead:

1. Install the plugin (I guess you have already done this.)
2. Go to Admin -> Users -> Permissions -> Define roles.
3. Choose to edit the Manager role.
4. Type 'customsql' into the filter box.
5. Tick the Allow box for one or both of the capabilities.
6. Save changes.

Average of ratings: -

Tim, is there some site wide setting I have to enable to allow me to edit the role?  I do not seem to have option to edit/see detail of the role.  I cannot perform step 4 (to see the detail of the manager role), so cannot tick options.  I even tried turning off my theme and applying the standard, thinking something was getting wacked.

thanks.

screen grab, if it helps

Average of ratings: -

This sounds great, REALLY great!

however, after a seemingly smooth installation, I am faced with a blank page at

just nothing appearing at all...am i missing some settings here?

(am running version 1.9.7)

Paul C

Average of ratings: -
Whenever you get a blank page, you should turn on Debugging to get an error message. Then we have a chance to diagnose the problem.

Average of ratings: -

ah, okay my apologies....

having turned ON debugging, i get this error;

Parse error: syntax error, unexpected T_NEW in /admin/report/customsql/locallib.php on line 59

Average of ratings: -
Ah, I would guess you are using PHP 4, and this report is written assuming you are using PHP 5, which you really should be in 2010.

Average of ratings: -

my mistake.

thanks for the reply, ive just upgraded our php language to PHP5 (we are using godaddy) it says it will take 24hours to work, mind...

paulc

Average of ratings: -

Hi all

I have version 1.8 but i need to use this module...can't upgrade to 1.9 cause my courses are to difficult to upgrade (i made too many changes).Plz need help

Average of ratings: -
If you cannot upgrade from Moodle 1.8 to 1.9.x, then yes, indeed, you need help.

I strongly suggest that you solve that problem before installing any more plugins.

Average of ratings: -

Is it possible to modify this module for verion 1.8?

Average of ratings: -
I'm sure it is if you know what you are doing. It is a fairly simple report. However, I have neither the time nor inclination to do so, so you are on your own.

Average of ratings: -
Problem with Monthly Usage by role report

Tim, thank you for all your work on this feature. I have installed it ok and have added and run the Usage summary report. However I'm having a problem with the Monthly Usage by Role report - I get an error:

ERROR: syntax error at or near "." LINE 2: month(from_unixtime(m_stats_user_monthly.timeend)) AS ca... ^

Would this be down to the fact we have a PostgreSQL database?

Average of ratings: -
Re: Problem with Monthly Usage by role report
Whoever added that Monthly Usage by Role report to Custom_SQL_queries_report#Share_your_interesting_queries_here has written it in a way that only works on MySQL. If you want it to work on Postgres, you will have to re-work it.

Average of ratings: -
Re: Problem with Monthly Usage by role report

Hi Tim, thanks so much for this great plugin. I have been able to install successfully, however, when I add new queries, i only get a blank page, the data do not appear at all but the query names appear. I have checked the following:

1. I have table 'mdl_report_customsql_queries' in my database

2. I also have 'report_customsql_version' as column in the table

3. I do not have 'report/customsql:view' in my roles page.

5. When i turned on debugging, i got this : Fatal error: Call to undefined function html_is_blank() in ...admin\report\customsql\view.php on line 64

Thank you.

Average of ratings: -
Re: Problem with Monthly Usage by role report
Hmm. That function was added to lib/moodlelib.php somewhere between Moodle 1.9.2 and 1.9.3. If you are still running a version that old, read http://moodle.org/security/ and then plan an upgrade . If you are running a newer version, then I am confused.

Average of ratings: -
Re: Problem with Monthly Usage by role report

Thanks Tim, you have been helpful. Upgrading will take some time and I do not have the manpower to do this now. I wish I could get a solution for this without upgrading. I will appreciate any help from anybody (including you, Tim )

Average of ratings: -
Re: Problem with Monthly Usage by role report
Well, you could just get the definition of that function (for example from http://cvs.moodle.org/moodle/lib/moodlelib.php?view=markup&pathrev=MOODLE_19_STABLE) and copy-and-paste it into your copy of moodlelib.php.

Of course, that just makes a mess when you update later, unless you know what you are doing.

Average of ratings: -
Re: Problem with Monthly Usage by role report

It worked perfectly. As a 'safety' measure, i have saved an original copy of my lib/moodlelib.php somewhere. Thanks Tim, you have been very helpful.

Average of ratings: -

I am having a problem with security for this module. I would like to prevent students from viewing custom reports completely, but the menu item Site Administration :: Reports :: Ad-hoc database queries" is still visible for all students, even though no items are accessible underneath.

Is it possible to completely hide "Ad-hoc database queries" if there are no custom queries underneath that the current user has access to? I tried to edit Student role and set custom sql prevent flags, but it didn't work.

Any ideas?

Andrew

Average of ratings: -

In particular, it should only be visible to users with the report/customsql:view capability.

Oh, by default any logged in user has that capability. (That is a weird design decision that only makes sense based on how things are set up at the OU.)

You just need to go into Define roles, and remove that capability from the Authenticated user role.

Average of ratings: -

> You just need to go into Define roles, and remove that capability > from the Authenticated user role.

Thanks, and it worked!

I've also made custom_sql PHP 4 compatible, if you are interested I can send you the source.

Andrew

Average of ratings: -
Great! (and Great!)

What would be best is if you can send me a patch (See Development:How_to_create_a_patch), and the best way to get it to me would be to create a tracker issue (http://tracker.moodle.org/browse/CONTRIB/component/10540) and attach the patch there.

If you can't do a patch, the modified files will be fine. Thanks.

Average of ratings: -
One of our custom reports returns errors
I would love some debug help if anyone has a moment.

User activity report.

SELECT FROM_UNIXTIME(prefix_log.time),
prefix_user.firstname,
prefix_user.lastname,
prefix_user.email,
prefix_course.shortname,
prefix_resource.name
FROM prefix_log
INNER JOIN prefix_user ON prefix_log.userid=prefix_user.id
INNER JOIN prefix_course ON prefix_log.course=prefix_course.id
INNER JOIN prefix_resource ON prefix_log.info=prefix_resource.id
ORDER by FROM_UNIXTIME(prefix_log.time)DESC
#LIMIT 1000 #LIMIT no worky

Average of ratings: -
Re: One of our custom reports returns errors

I suppose you could add a feature to the code, so that the 5000 limit can be controlled by an option on the editing form, rather than assumed.

Alternatively, you could just find where the constant is set to 5000 in the code, and change the value, but that will then affect all reports.

Average of ratings: Useful (1)
Re: One of our custom reports returns errors
Thanks Tim. Good to know about the built-in limit.

Could you try our query to see how it is returning resource views from the front page of our site, for resources that do not exist on the front page and I personally have not viewed where they do exist.

Average of ratings: -

I get this error when i copy customsql module in report and run notification from admin

This script generated 1 warning - Scroll to the first warning

Plugin "customsql" (2009103000) could not be installed. It requires a newer version of Moodle (currently you are using 2007101542, you need 2007101550).Scroll to continue button

Average of ratings: -

Tim,

This is GREAT! Installed the query admin report without any issues and have been working on my SQL!

I have a question about a query I've put together (pasted below). I'd like to create a query that pulls final grades from courses but in the midst of writing the query I discovered that the "finalgrade" field in the mdl_grade_grades table isn't really a course final grade. It is apparently more of a category final grade, I guess.

I managed to work something out here that works for my current setup but I don't think it's going to hold water in the face of some careful scrutiny. It was designed on the assumption (in the ORDER BY and GROUP BY statements) that the ID for the final grade record in mdl_grade_grades is going to be a lower integer than the other items but I'm not sure if that will always be an absolute truth....or will it?

Anyway, I wanted to get some feedback on it here before posting it in the documentation area.

Here it is

SELECT
prefix_course.fullname AS "Course",
prefix_user.firstname AS "First Name",
prefix_user.lastname AS "Last Name",
FROM
INNER JOIN prefix_course ON prefix_grade_items.courseid = prefix_course.id
AND (prefix_user.deleted = 0)

Also, I'm pretty green when it comes to SQL so if I've made some glaring mistake here in terms of efficiency etc. feel free to educate me

Jason

P.S. EDIT - I just removed the following line as I realized it was problematic.

Average of ratings: -
First an aside: Do you know about table aliases. If, in the FROM clause, you do

FROM
INNER JOIN prefix_course c ON prefix_grade_items.courseid = prefix_course.id

Anyway, the problem you have is that your query is returning the data from all the columns in the gradebook for each user and each course. Change the ORDER BY to

ORDER BY u.id. c.id, gi.id

and add gi.itemtype to the FROM to make that clear.

I think you need to add

AND gi.itemtype = 'course'

to the WHERE clause.

Average of ratings: -

Tim,

Thanks. To the aside, yes I am aware of aliases but since I'm still very new to SQL I'm opting to write everything out to help me get the logic in my head. Sorry for the cumbersome appearance of it all. I'll work on getting up to speed and aliasing asap.

As for the query, actually it is currently returning the appropriate data for me. The GROUP BY and ORDER BY resulted in one record being displayed for each user and it being the record with the lowest ID in grade_grades. I've been trying to figure out if there is a case where the lowest value for the ID in grade_grades would not be the actual final grade item.... I think that's the only case where this would return erroneous data...but I'm not sure.

I just poked around in the database and I see the connection between gi.itemtype='course' and the final item. I think the way I am getting at the data I want isn't foolproof but I can't find the holes in it at the moment. I'll work on rewriting this to hopefully make it solid.

I'm not sure that I understand the ORDER BY that you have listed here.... u.id (user.id), c.id (course.id) and gi.id (grade_items.id) right?

Here's a copy of the results I get from it as is...which are exactly what I want. Isn't the ORDER BY you've suggested going to give me different results....? Sorry, don't answer that, I'll play with it a little before I ask questions

Jason

Average of ratings: -
Oops. I had not noticed the GROUP BY. That makes a difference.

Actually, your GROUP BY does not make any sense, and the only reason you don't get an error message is because MySQL is happy to do garbage-in, garbage-out, rather than giving you a proper error message.

With group by, column you list in the from clause must either be listed in the GROUP BY clause, or it must have an aggregate function applied in the SELECT clause.

So you can do

SELECT a, b, MAX(c) FROM table GROUP BY a, b

or you can do

SELECT a, MAX(b), MAX(c) FROM table GROUP BY a

but you can't do

SELECT a, b, MAX(c) FROM table GROUP BY a

(even if a is a primary key.)

I don't think your query is doing what you want. Have you created a second course? I thought you wanted to list the grade for each user in each course.

Try this. With your query, get rid of the group-by completely, and put in the ORDER BY I suggested, which, written out if full, is

and add prefix_grade_item.itemtype to the SELECT clause so you can see what is going on.

That should return multiple rows for each user in each course. Check that there only one row for each user + course where prefix_grade_item.itemtype = 'course'. If there is, then add the AND prefix_grade_item.itemtype = 'course' to the WHERE clause, and remove prefix_grade_item.id from the ORDER BY, and prefix_grade_item.itemtype from the SELECT again.

Then feel free to adjust the ORDER BY to whatever you really want.

Average of ratings: -

Tim,

Thanks again. I don't quite have my head around the rules governing GROUP BY but mine was definitely not correct. I added some grades in another course (on a test site) and sure enough, the query I had did not return anything from the other course.

I did the check you proposed and managed to get it all worked out. I was kind of trying to reverse engineer the query while looking at the tables and the 'course' maker completely evaded me. Here's what I ended up with:

SELECT
prefix_course.fullname AS "Course",
prefix_user.firstname AS "First Name",
prefix_user.lastname AS "Last Name",
FROM
INNER JOIN prefix_course ON prefix_grade_items.courseid = prefix_course.id
AND (prefix_user.deleted = 0)
ORDER BY prefix_course.id, prefix_user.id

I'll work on making it a bit more efficient in terms of aesthetics and then add it to the docs page.

Thanks again.

Jason

P.S. I guess the timecreated IS NULL check really isn't needed here because the 'course' test will ensure that the final score is retrieved. It was just that I noticed, while looking at the tables, that the final grade always has NULL for the timecreated field....or at least that is what I witnessed. If there is an instance where the final grade does get a timecreated value then this query will falter, right?

Average of ratings: -

Tim or anyone else willing to help ,

I'm not sure if this is the right place for this but....

I'm trying to (or wondering if it is possible to) write an SQL query that combines fields from mdl_user with mdl_user_info_data and mdl_user_info_field

such that the mdl_user_info_field information is used as the heading for mdl_user_info_data which is what it is.

This is what Moodle does via Bulk user actions and in other places but I want to SELECT based upon criteria such as "if a value in a profile field is greater than a certain amount".

I've poked around for quite a while now trying to figure out if SQL will allow me to use field values from one table as headings for columns from another but haven't had much luck.

Not sure if that makes any sense at all.....

Graphically, I'm looking to get the following type of results

 username firstname lastname Test Score Program User 1 Jim Smith 54 Law User 2 John Doe 78 Business User 3 Larry Wright 82 Medicine

Where the Test Score and Program titles come from the mdl_user_info_field table and the contents come from mdl_user_info_data.

I have the following query that returns all the information but not formatted this way.

SELECT mdl_user.username, mdl_user.firstname, mdl_user.lastname, GROUP_CONCAT( mdl_user_info_data.data ) AS
DATA , GROUP_CONCAT( mdl_user_info_field.name ) AS Field
FROM mdl_user
INNER JOIN mdl_user_info_data ON mdl_user.id = mdl_user_info_data.userid
INNER JOIN mdl_user_info_field ON mdl_user_info_data.fieldid = mdl_user_info_field.id

Jason

Average of ratings: -
You can only do it for specific fields, and it is easiest if you look up the mdl_user_info_field.id of the fields you are interested in first.

Let us suppose you are interested in the 'frog' field, it has field id 123. Then you can do

SELECT
data.data AS frog
FROM mdl_user
LEFT JOIN mdl_user_info_data frog_data ON mdl_user.id = mdl_user_info_data.userid AND mdl_user_info_data.fieldid = 123

Average of ratings: -

Thank you

--Jeff Snyder

Average of ratings: -
There was a problem with the Moodle download server today. It is working again now.

Average of ratings: -

Tim,

Thanks, that is what I was afraid of and slowly realizing. I wanted to return all of the profile fields associated with each user but I guess the design of that table makes that impossible to do with SQL statements alone. I have performed the task that you outlined (return one specific field) but was/am wanting to retrieve all...guess I'll go a different route.

Thanks again.

Jason

Average of ratings: -
The only way to retrieve all is to return them as separate rows.

I don't actually see the problem with that.

Average of ratings: -
Retrieving Custom grade category for all the course in a course category

The following query helps in retriving a custom grade category TATOTAL in all the courses in a particular course category, it may help some members.

SELECT
prefix_user.firstname AS "Roll No",
prefix_user.lastname AS "Last Name",
prefix_course.idnumber AS "Course_Code",
prefix_course.fullname AS "Course",
FROM
INNER JOIN prefix_course ON prefix_grade_items.courseid = prefix_course.id
INNER JOIN prefix_course_categories pcc ON prefix_course.category = pcc.id
AND (prefix_user.deleted = 0)
AND pcc.name = 'Semester VII'
and pcc.parent =
(SELECT mcc2.id from prefix_course_categories mcc2 where mcc2.parent =
( SELECT mcc3.id from prefix_course_categories mcc3 where mcc3.parent =
( SELECT mcc4.id from prefix_course_categories mcc4 where mcc4.parent = 0 and mcc4.name = 'CSE Dept' ) and mcc3.name = 'BTech') and mcc2.name = 'IT')
ORDER BY prefix_course.id, prefix_user.id

Average of ratings: -
Re: Retrieving Custom grade category for all the course in a course category

I'm not an SQL expert, still learning!
It's great that people like Gaurang and others are sharing queries like this - it gives Admins something useful, and also helps us learn more
Can I ask, is it possible to annotate (in a simple way) how this query can be edited to work on other sites - which are the main values to change and why (e.g. where the changes should be made for a Category... so users can more easily adapt these queries to their own Moodle site context?)
Stu

Average of ratings: -
Create a Group Report

I do not know how to write a query for mysql using moodle databases.  I appreciate your patience and any support you can give.

I would like a report that shows who has enrolled in groups set up on the moodle site.

There is a table called mdl_enrol_groups with:

groupid
userid

I would like the report to show:

groupid
groupname
user.firstname
user.lastname

Kevin

Average of ratings: -
Help with custom sql query

I do want to be able to generate a report on how many people have actually completed and passed each course in my moodle site. I have written the query below but that seems to rely on certificate issues.

SELECT c.name AS 'COURSE NAME', Count(c.name) AS 'NO OF STUDENTS COMPLETED COURSE'
FROM   user u, certificate_issues ci, certificate c

where u.id = ci.userid AND
ci.certificateid = c.id

GROUP BY c.name

The trouble with this is that a student might be missed out on my report if he has completed and passed a course but not yet access his certificate.

I want to be able to based my report on quizes completed and crucially passed NOT certificate issued. Any help will be highly appreciated.

Average of ratings: -
Re: new admin report CustomSQL queries

Thanks Tim for this great admin report, it's very useful

I've made a french translation that you can integrate.

And i also have made some queries that i'll certainly share, if it can help other people. Would also be glad to know is some of them can be optimized

It would also be very cool to be able to sort results ascending/descending by clicking on columns, your tool regenerating the query but just changing sort order

Average of ratings: -
Re: new admin report CustomSQL queries

Thank you for the translation. It is in CVS now, so it should be in the download zip tomorrow.

I'm afraid I don't have any time to work further on this report at the moment, but if anyone makes an enhancement as a patch and attaches it to a tracker issue, I will try to make time to review it and commit it.

Average of ratings: -

Thank you for the very useful plugin!

May anyone help me to rewrite query for speedup?

select  a.fullname,   (select       count(1)    from        prefix_question b    where        b.parent=0 and b.hidden=0        and b.category in        (select            c.id         from            prefix_question_categories c         where            c.contextid=(select d.id from prefix_context d where d.contextlevel=50 and d.instanceid=a.id)        )   ) as qcountfrom  prefix_course a

Aim - get the list of courses with corresponding number of questions in it.

Average of ratings: -

Generally, joins are much faster than subqueries, so:

SELECT c.fullname, count(1) AS num_questions
FROM prefix_course c
JOIN prefix_context ctx ON ctx.contextlevel = 50 AND ctx.instanceid = c.id
JOIN prefix_question_categories qc where qc.contextid = ctx.id
JOIN prefix_question q ON q.category = qc.id
WHERE q.parent=0 AND q.hidden=0
GROUP BY c.fullname

also, I strongly recommend using mnemonic table aliases. They make your query much more readable. I just typed the above straight into this forum post, so there are probably errors you will need to fix.

Average of ratings: -

I’m using Moodle 2.0 (Build: 20101130) and have extracted the customsql.zip file associated with this plugin to moodle/admin/report. However, I'm unable to complete the installation as I’m getting a message that reads “Plugin is not compatible with Moodle 2.x or later”. Are you intending to release a Moodle 2.0 version of this plugin? Thanks.

Average of ratings: -

I would love to see a Moodle 2.0 version of this, but I currently don't have time to even think about this.

This is on one of my colleague's todo lists, but only underneath a whole lot of other things that are much more critical.

So, really, if you want this soon, you are going to have to do this yourself, or bribe someone to do it.

If someone does update this plugin (it is not actually very much code), it will make me very happy, and will be a nice demonstaration to some of the cynics round here that the OU does get benefit from being part of an open source community.

Average of ratings: -

Do you know how what needs to be updated? Is it a big job?

Average of ratings: -

Well, just all the standard things you have to do to convert a plugin to Moodle 2.0. It should not be a very big job.

I suppose the only potenital stumbling block is that you have user-entered DB queries, and the way Moodle 2.0 handles SQL has changed a bit. So it might make sense to tweak how the user-entered SQL is handled, so you can use placeholders where values are substituted and {table_name} instead of prefix_table_name.

What docs there are about converting code to Moodle 2.0 is at Moodle_2.0_release_notes#For_developers:_API_changes

Average of ratings: Useful (1)
II have finished the upgrading of my plugin Configurable Reports to Moodle 2.0 today.
IIt uses a few lines of Custom SQL reports plugin for the SQL reports.
Regarding the user-entered DB queries it works without any modification (except the prefix $DB...) See: http://cvs.moodle.org/contrib/plugins/blocks/configurable_reports/reports/sql/report.class.php?revision=1.2&view=markup Average of ratings: Useful (1) Re: I just made a new admin report Tim, Firstly I love your report! We have been using it for about a year now and it's very useful! Thanks for this! However, I have just enountered something that has never happened before and am perplexed! Hoping you or someone else might be able to shed some light? It seems as though, attempting to run a query keeps bringing our DB down! And has caused the following error message to appear: Error: Database connection failed. It is possible that the database is overloaded or otherwise not running properly. Here is the query: (syntax is fine as we have run it on our development server ... however our development server obviously does not have any connections!) SELECT DISTINCT prefix_course_categories.name, Count(prefix_log.action) AS CountOfaction, Min(prefix_log.time) AS MinOftime FROM (prefix_log INNER JOIN prefix_course ON prefix_log.course = prefix_course.id) INNER JOIN prefix_course_categories ON prefix_course.category = prefix_course_categories.id GROUP BY prefix_course_categories.name HAVING (((prefix_course_categories.name)="CUP")) One very recent change that has occurred is a migration of web servers, so my thought was that there might be a setting on the new web server somewhere that is different than it's predecessor. Any ideas? Thanks!!!! Moodle v. 1.9.8 MYSQL Average of ratings: - Re: I just made a new admin report Well, how many rows in the mdl_log table on your development server, and on your live server? The way you have bracketed the join statements probably does not help the query optimiser. Similarly, using HAVING rather than where is probably a really bad idea. DISTINCT is also unnecessary here (the query will only return one row anyway) and also tends to lead to slow queries. Have you tried doing an EXPLAIN on that query, to find out how the database is executing it. Plus, if you are using MySQL, with MyISAM tables, then it probably ends up locking the whole log table for however long that inefficient query takes to run, and just about anything you do in Moodle wants to write a row to the log table - hence the symptoms you see. I suspect that the following query will be a lot faster. Although, if your log table is very large, it will still be slow. SELECT prefix_course_categories.name, COUNT(prefix_log.action) AS CountOfaction, MIN(prefix_log.time) AS MinOftime FROM prefix_log JOIN prefix_course ON prefix_log.course = prefix_course.id JOIN prefix_course_categories ON prefix_course.category = prefix_course_categories.id WHERE prefix_course_categories.name = 'CUP' GROUP BY prefix_course_categories.name You could make it faster still by finding out the category id for the CUP category, and then you could get rid of the JOIN on the category table. Just change the WHERE to prefix_course.category = {whatever}. Average of ratings: - Re: I just made a new admin report Thanks for the feedback, it's very appreciated. I don't write my own SQL, I use a GUI. The suggestions you indicate make sense, I should probably start learning more about how to optimize and do them myself. I thought perhaps we were simply missing some sort of setting on the web server after we migrated. Turns out we did have max connections too low on both the db and web server which helped with the error message but not necessarily the slowness of the query. Thanks again Average of ratings: - Re: I just made a new admin report Dear colleagues, please help! I'm new to sql and moodle DB. and i need to make a report like that:  prefix_user.lastname ??? prefix_grade_items.itemname prefix_grade_items.grademax prefix_grade_grades.finalgrade prefix_grade_grades_timecreated user lastname courses user enrolled in quizes inside courses max grade user final grade date grade recived (in datetime format) u1 c1 q1 100 95 22-01-2011 u1 c1 q2 100 100 23-01-2011 u1 c2 q3 100 50 22-10-2011 u2 c1 q1 100 NULL 25-01-2011 So as a result I want to see a list of all users with courses they enrolled. Then quizes inside courses (so i wanna see grades fox quiz only - not for the whole course) with max possible grade and with the grade user received (NULL if he do not have mark for this quiz). Also I wonder how to make date look likу normal date - not epoch. Is it possible? Thanx Average of ratings: - Re: I just made a new admin report of course it's possible, just use$DB->get_records_sql("sentencia",array(param=>value));

just try first doing that on sql before tryin in moodle

Average of ratings: -

Could you please describe it i details.

Thanx

Average of ratings: -

Tim, is there a way to create a report that first prompts the user for a value and then uses that value as a filter condition in the SQL statement?

Example: the report prompts the user for a "department" and then displays all the records in the database containing the specified value in the department field on mdl_user.

SELECT firstname, lastname, idnumber
FROM prefix_user u
WHERE u.department = %%DEPARTMENT%%

"%%DEPARTMENT%%" being whatever value the user is prompted for.

Average of ratings: -

This was on our todo list at one time, but sadly it was one of the features that fell off the end due to lack of time before it got implemented.

If anyone wants to try to implement it, that would be great.

Average of ratings: -

We may be in a position to fund this development. Do you have a suggestion of whom we might contact?

Average of ratings: -

Average of ratings: -

Well, that's great news! (Is it documented somewhere? What tokens are available?)

Average of ratings: -

After rummaging around a bit, I think I have answered my question.

Any text that begins with a colon will be treated as a placeholder. However, if that text is inside quotes (a string literal), it may not be interpreted correctly according to CONTRIB-3968. Each placeholder will generate a form element that will need to be filled in by the user who runs the report.

Example:

SELECT c.fullname AS CourseFROM prefix_course AS cWHERE c.category = :category_number

Here, ":category_number" is the placeholder, and a form field is created with that label. One can then enter the category number and it will be used as part of the WHERE clause.

Unfortunately, I don't see a way to populate the form with a list of values, for example the category names, and have the category numbers fill in. But this is still quite helpful.

I'm quite willing to add this to the plugin documentation page, if someone can verify that I've got the essentials correct....

Average of ratings: -

That sounds basically right.

Average of ratings: -

Tim:

Needind a little help regarding "Custom SQL queries" (Ad-ho database queries).

I have 7 courses, each with severall groups and hundreds of students.

The groups represent severall school/class names. In a specific course, there may exist groups with names equal to groups in other specific course (meaning that a school/class has students in both these specific courses).

I need several reports:

1 - Total number of (unique) users that are enrolled as students at least in a course. I tried using:

SELECT r.name AS 'Papel',l.action AS 'Ação', count(DISTINCT l.userid ) AS 'Nº logins'
FROM prefix_user AS u
JOIN prefix_log AS l ON u.id = l.userid
JOIN prefix_role_assignments AS ra ON l.userid = ra.userid
JOIN prefix_role AS r ON ra.roleid = r.id
WHERE ra.roleid = 5 AND time >= unix_timestamp('2011-10-01 00:00:00') AND time <= unix_timestamp('2011-10-31 23:59:59') AND l.action='login'

2 - Number of (unique) "students" (users that are enrolled as students at least in a course), that made at least ONE login during a specific period of time (in the following example, the month of October):

SELECT r.name AS 'Papel',l.action AS 'Ação', count(DISTINCT l.userid ) AS 'Nº logins'
FROM prefix_user AS u
JOIN prefix_log AS l ON u.id = l.userid
JOIN prefix_role_assignments AS ra ON l.userid = ra.userid
JOIN prefix_role AS r ON ra.roleid = r.id
WHERE ra.roleid = 5 AND time >= unix_timestamp('2011-10-01 00:00:00') AND time <= unix_timestamp('2011-10-31 23:59:59') AND l.action='login'

3 - Total number of student logins (not unique) during a specific period of time (in the following example, the month of October):

SELECT r.name AS 'Papel',l.action AS 'Ação', count(l.userid ) AS 'Nº logins'
FROM prefix_user AS u
JOIN prefix_log AS l ON u.id = l.userid
JOIN prefix_role_assignments AS ra ON l.userid = ra.userid
JOIN prefix_role AS r ON ra.roleid = r.id
WHERE ra.roleid = 5 AND time >= unix_timestamp('2011-10-01 00:00:00') AND time <= unix_timestamp('2011-10-31 23:59:59') AND l.action='login'

4 -Total number of quiz attempts, by students, completed in a specific period of time (in the following example, the month of October):

SELECT r.name AS 'Papel',COUNT(*)
FROM prefix_quiz_attempts AS qa
JOIN prefix_user AS u ON qa.userid = u.id
JOIN prefix_role_assignments AS ra ON u.id = ra.userid
JOIN prefix_role AS r ON ra.roleid = r.id
WHERE ra.roleid = 5 AND timefinish > unix_timestamp('2011-10-01 00:00:00')
AND timefinish <= unix_timestamp('2011-10-31 23:59:59')
AND preview = 0

5 - Average time used by students to complete a quiz that was finished in a specific period of time (in the following example, the month of October). Note that I do not consider a period of over a week (7 days) between starting and ending the quiz:

SELECT FROM_UNIXTIME(AVG(TIMEDIFF(FROM_UNIXTIME(q.timefinish),FROM_UNIXTIME(q.timestart))))
FROM prefix_quiz_attempts AS q
WHERE q.timefinish > unix_timestamp('2011-10-01 00:00:00')
AND q.timefinish <= unix_timestamp('2011-10-31 23:59:59')
AND TIMEDIFF(FROM_UNIXTIME(q.timefinish),FROM_UNIXTIME(q.timestart)) > 0 AND TIMEDIFF(FROM_UNIXTIME(q.timefinish),FROM_UNIXTIME(q.timestart)) < 604800

Then, I need to answer this same queries, but separating the output by group.

What I did was (for example, for the "1 - Total number of (unique) users that are enrolled as students at least in a course"):

SELECT r.name,l.action,g.name,count(DISTINCT l.userid ) AS counter
FROM prefix_log AS l
JOIN prefix_role_assignments AS ra ON l.userid = ra.userid
JOIN prefix_role AS r ON ra.roleid = r.id
JOIN prefix_user AS u ON l.userid = u.id
JOIN prefix_groups_members AS gm ON u.id = gm.userid
JOIN prefix_groups AS g ON gm.groupid = g.id
JOIN prefix_course AS c ON c.id = g.courseid
WHERE ra.roleid = 5 AND time >= unix_timestamp('2011-10-01 00:00:00') AND time <= unix_timestamp('2011-10-31 23:59:59') AND l.action='login'
GROUP BY g.name
ORDER BY g.name

Could you tell me if this is the right way of doing it?

Average of ratings: -

That looks plausible.

Average of ratings: -

Tim:

Sorry, but what do you mean with plausible? I'm portuguese, and for me the word "plausible" is associated with "possible".

Do you mean that I can get what I want with the queries I built?

Average of ratings: -

I mean that I cannot see any mistakes (but you may have made a mistake that I did not notice ).

Average of ratings: -

Tim,

Just a heads up that as far as I can see, this will need to be updated for version 2.2, as the reports are no longer in the /admin/report folder and are now stored in /report instead.

Thanks

Average of ratings: -

Yes, it will

OU is moving to 2.2 in developme before Christmas, with a view to unleashing it on students and staff in March, so this will have to get resolved quite soon.

Average of ratings: -

That's fine. Good to know it will be avaialble before my upgrade next summer

Average of ratings: -

Average of ratings: -

I noticed this on only my second try,.....report\ not admin\report, I mean.

So, copy the resultant folder from the unzip, customsql, into root of \report folder, not root of admin\report folder.  Never a dull moment!

Average of ratings: -

Thanks to all for the great SQL contributions, they are really helpful to someone who is not that strong in SQL (like me)!

I am running Moodle 2.2.1 and have installed the ad-hoc database query plugin. I was looking over the contributed SQL queries and found one that I was particularly interested in:

### How many LOGINs per user and user's Activity

SELECT concat('<a target="_new" href="%%WWWROOT%%/course/user.php?id=1&user=',u.id,'&mode=alllogs">',u.firstname ,' ',u.lastname,'</a>') AS Username
,(SELECT count(*) FROM prefix_log WHERE userid = l.userid GROUP BY userid) AS Activity
FROM prefix_log AS l JOIN prefix_user AS u ON l.userid = u.id
WHERE action LIKE '%login%' GROUP BY userid
ORDER BY Activity DESC

When I run the query, I receive the error:

ERROR: Incorrect number of query parameters. Expected 1, got 0.

Does someone know the source of the error?

Average of ratings: -

I've just started using the custom sql plugin and it's really handy. Thank you Tim and to all who have posted stuff here.

I have hit a slight snag, can anyone shed any light on using tokens in the queries?

I can use the USERID token fine - SELECT  %%USERID%% as uid for example, but I can't get the WWWROOT token to work at all. For example - SELECT %%WWWROOT%% AS w - I just get error messages.

I wanted to use it with CONCAT to create a hyperlink in the results as per the examples. I'm on Moodle 2.2.2 and slightly baffled!

Any suggestions welcome.

Average of ratings: -

I've just noticed another oddity - I can't include a ? in string concats - thus making it unlikely I can create a useful hyperlink in the results.

So, for example

SELECT concat('I can do these ','<>/\=! ') as ans

will work - but if I add a question mark in there I get an error - Error when executing the query: ERROR: Incorrect number of query parameters. Expected 1, got 0.

Average of ratings: -

Hi Peter,

Thanks for this, I was wondering why my reports had all stopped working.

I managed to get around this by replacing any '?' in my query by CHAR(63)

Hope that helps someone

Steve

Average of ratings: Useful (3)

Brilliant, thank you!!!

Average of ratings: -

Hi All,

I want to generate report of total hits per course from last one month.This query gives me total hits since course generated. i want to generate hits by time.

SELECT COUNT(l.id) hits, l.course courseId, c.fullname coursename
FROM mdl_log l INNER JOIN mdl_course c ON l.course = c.id
GROUP BY courseId
ORDER BY hits DESC

Average of ratings: -

Awesome! Been looking for this fix forever!!

Average of ratings: -

Isn't there any configuration setting that will allow having a '?' in concatenated string on the query? It's a little bit annoying to replace any ? with CHAR(63)

But anyways...thanks for this. It was driving me mad.

Average of ratings: -

I want to thank the contributors of this plug-in for great work.  It really fills a gap from a report/grade book stand point.



I am not sure if this is the correct location for an enquiry of this nature, if not please point me in the right direction.  I am trying to produce a report that will allow a merge to produce summarized quarterly report cards with all courses on an individual basis for print out.



In looking through the custom sql I found two reports that get me close. Unfortunately my sql query skills are lacking.



The  Learner Report  and the Site Wide report with Course Totals . The values I am looking for are the "category totals" .  The learner report returns these but with no category labels, so cannot delineate between first and second quarter categories.  Also on Site wide reports it will produce the grades I am needing if you substitute "category" for "course" in the line below, but unfortunately it also does not produce labels for the grade categories:



WHERE  gi.courseid = c.id AND gi.itemtype = 'course'



The table that contains the category labels is:



Table



Field
fullname



Thanks in advance, any help would be greatly appreciated.



Jay Hickman



Average of ratings: -

Hi !

I'm wondering how to uninstall an "admin report" from Moodle ?
There is no interface that lists the admin reports installed, isn't it ?

I guess I have only to remove the admin/report/folder and to delete the table in the DB. Can you confirm that ?

I'm glad to find the version for Moodle 2. If I migrate my site from 1.9 to 2.2 and I replace the admin/report/folder by the new version, my queries will work on the new site ?

Pascal

Average of ratings: -

What you say about manually uninstalling a plugin is approximately right. Just doing those two steps will leave a small amount of data in the database, but that won't do any real harm. If you want to know how to completely clean up after removing a plugin, see the uninstall_plugin funciton in https://github.com/moodle/moodle/blob/master/lib/adminlib.php#L123

The best way to upgrade a site that uses custom plugins is this:

1. Upload all the new core Moodle code.
2. Then add in the new version of all the contrib modules you use.

That way, the contrib plugins should be able to update all its data as part of the upgrade, and you should not lose anything.

For admin reports, you need to be aware that in Moodle 2.2, they no longer live inside admin/report, but instead go in the top-level report folder.

Average of ratings: -

Hello !

As I migrate from 1.9 to 2.2, I want to clean everything before. For example, if I uninstall a plugin, what about the "moddata files" in the moodledata directory ? I found nothing about that in the uninstall_plugin function. I guess that the developer of the plugin must program the cleaning of the module's files ? (in the db/uninstall.php ??)

What is the best way to proceed: reset all courses before the migration (to clean everything including files) or migrate then reset each course as usual ? Moreover, is it possible to reset all courses automatically?

( but maybe my (last) question is off topic .. ? )

Average of ratings: -

Hello,

Does anyone have or know how I can get a report to show courses that have been created within a specific school year? I think I modified a query to start something, but I don't think the "where" clause will work. I'm running Moodle on a windows server with apache.

SELECT COUNT(id) AS Courses FROM mdl_course
WHERE DATEDIFF( NOW(),FROM_UNIXTIME(lastlogin) ) < 120

Thanks,
Tyler

Average of ratings: -

Good day, I also I have some questions about this PLUGIN, I'm looking for ways to make a report of all course grades of a student, where I agree with the role of father "PARENTS" and can be viewed on a single outcome as a report card, it would be like this.

in 01 row the user name
in 01 column the name of the course
in column 01 average final course grade.

Thank you.

Average of ratings: -

My notification page shows nothing. I am using moodle 1.9.

Average of ratings: -

Tim,

Great report plugin, thank you. Just a small request for help, since i am SQL challenged.

I have run the Sitewide grade report as below, and it works fine, but is there some way, that i can get the total scores per student, as a row, with subsquent columns being the total marks across the various courses that the student has taken? (sort of an overview report combined with the site wide user report)

Since we need to evaluate the students across all the courses this would give a birds eye view for the student. i think that this code can be modified to group per student, across courses, for a predefined category.

Wish list would also include:

1. Failed marks would underlined,

2. Each total would be a link which would open up to that courses detailed view.

The student could have this view also, restricted to only his marks.

thank you for your help in this,

regards and warm wishes for the new year!

SELECT u.firstname AS 'First' , u.lastname AS 'Last', u.firstname + ' ' + u.lastname AS 'Display Name',

c.fullname AS 'Course',

cc.name AS 'Category',

CASE

WHEN gi.itemtype = 'course'

THEN c.fullname + ' Course Total'

ELSE gi.itemname

END AS 'Item Name',

DATE_ADD('1970-01-01', INTERVAL gi.timemodified SECOND) AS Time

FROM prefix_course AS c JOIN prefix_context AS ctx ON c.id = ctx.instanceid

JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id JOIN prefix_user AS u ON u.id = ra.userid JOIN prefix_grade_grades AS gg ON gg.userid = u.id JOIN prefix_grade_items AS gi ON gi.id = gg.itemid JOIN prefix_course_categories AS cc ON cc.id = c.category

WHERE gi.courseid = c.id

ORDER BY lastname

Average of ratings: -

"is there some way, that i can get the total scores per student, as a row, with subsquent columns being the total marks across the various courses"

Actually, you can't do that in SQL, because SQL is based on something called relational algebra, which, among other things, is based on rows having fixed columns. So you can easily do a result set which is a list of rows (studentid, courseid, grade), but you cannot easily do a two-dimensional result set.

Bear in mind that this report is a quick hack to let you easily generate reports that can be expressed as one SQL query.

If you want something else, particularly if you want custom display, then your best bet would be to write a new report plugin that does exactly what you want. Of course, that requires some PHP development skill.

Average of ratings: -

Thank you for the fast reply, any idea how to proceed in this direction for a report plugin /PHP development?

Average of ratings: -

The basic idea is quite simple: http://docs.moodle.org/dev/Reports

Once you have the basica outline in place, the best thing is probably to copy bits of the code of other reports that do something similar to what you want to do.

Average of ratings: Useful (1)

Hi guys,

I've been looking for something like this for a while as I am needing to make a "Report Card" section for my site.

Using Jason Hollowel's code I've been able to generate a list that shows EVERY students final grades, but this leads to a glaring privacy concern.

This is Jason's code that I've been using, I'm wondering if we can change it to suit my needs.

SELECT
prefix_course.fullname AS "Course",
prefix_user.firstname AS "First Name",
prefix_user.lastname AS "Last Name",
FROM
INNER JOIN prefix_course ON prefix_grade_items.courseid = prefix_course.id
AND (prefix_user.deleted = 0)
ORDER BY prefix_course.id, prefix_user.id

I need to have something that a student can click on "Report Card" and they will be taken to a list that shows them all of their final grades in all of the courses they are enrolled in (but only their own grades).  This also needs to be organized by Course Category (Each year/cohort has three terms so it looks like "Year 1"(category) -> "Term 1"(sub-category) -> "Science"(course)

I'd love it to look like this (or at least have this information) when a student clicks on Report Card.  I dont know or understand SQL at all so any help would be wonderful!

 Joe Student Term 1 English 90% Joe Student Term 1 Math 90% Joe Student Term 1 Science 90% Joe Student Term 2 English 80% Joe Student Term 2 Math 80% Joe Student Term 2 Science 80%

Thanks so much for helping out!!!

Average of ratings: -
How to get report of course completion

I need sql query to list all user who completed course

Average of ratings: -
Re: How to get report of course completion

You will need to find the column in the database table that holds the final course completion value.

Average of ratings: -
Re: How to get report of course completion

I would like a report that lets me see who has done what on a course and when but will also let me pull the department field from the user account.  I think we may use the Town field for this rather than create our own fields.

Is this possible?

Thanks

Michelle

Average of ratings: -

Firstly, thanks for this very useful plugin which I've been trying out along with an ad-hoc contributed report to show a learner report, by Learner with grades.

The learner report works OK but I would like to know if it's possible to show the grades scales that are used in my courses instead of the percentage based grades? I am using the code below and any help would be much appreciated.

Thanks

Richard

SQL code:

SELECT u.firstname AS 'Name' , u.lastname AS 'Surname', c.fullname AS 'Course', cc.name AS 'Category',
CASE WHEN gi.itemtype = 'Course'
THEN c.fullname + ' Course Total'
ELSE gi.itemname

IF (ROUND(gg.finalgrade / gg.rawgrademax * 100 ,2) > 79,'Yes' , 'No') AS Pass

FROM prefix_course AS c
JOIN prefix_context AS ctx ON c.id = ctx.instanceid
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
JOIN prefix_grade_items AS gi ON gi.id = gg.itemid
JOIN prefix_course_categories AS cc ON cc.id = c.category
WHERE gi.courseid = c.id AND gi.itemname != 'Attendance'
ORDER BY 'Name' ASC

Average of ratings: -

Hi, Moodlers !

I just installed this plugin, and started to query.

my first attempt was :

SELECT u.username AS 'matricule', u.lastname AS 'Nom', u.firstname as 'Prenom', DATE_FORMAT(FROM_UNIXTIME(uid.data), '%d-%m-%Y') AS 'dt_entree'
FROM prefix_user AS u
JOIN prefix_user_info_data AS uid ON uid.userid = u.id
WHERE uid.data = 1383519600
ORDER BY u.lastname ASC

uid.data is a custom profile field with a date (without time)

I noticed 2 'odd' things :

when running this query as it is upthere, no problem.

If I change the 'dt_entree' with something that has space (like 'dt entree'), the query does not run anymore.

If I want to display the uid.data as '%d/%m/%Y', the query doesn't run either, but changing it to '%d-%m-%Y' make it run correctly again...

Is it mySQL related, or has to do with the plugin ? (I use to query MS Sql databases, and never noticed that kind of thing....)

My Moodle is 2.5.1+ and everything (server, platform, browser, etc.) is full-french-featured system... Regional settings also use "/" as date delimiter.

Any idea ? Thanks for every contribution !

Average of ratings: -

Hi, was wondering if anyone could maybe get me started in listing a users activities and also wether those activities are completed or not.

Also have another question - I see that all the ad-hoc reports are for site-wide users, how can i target only the signed in, current user?

Thanks!

Average of ratings: -

Hi Tim, i'm a developer who is new to Moodle, i recently installed your plugin (which works great)
and tried to do an UPDATE query. I got the error saying i can't perform such queries (UPDATE, DELETE etc...) and was wondering if this is blocked by the plugin itself or is it something the Moodle system just does not allow?

Average of ratings: -

This is a reporting tool.  You should never be able to update or delete from that.  If you really need to mess with the database from moodle, try the adminer plugin.

Average of ratings: Useful (2)

Or, as a developer, should probably install something like mysql workbench and avoid that kind of direct write access to the database from within the moodle UI entirely.

Average of ratings: -