General plugins

 
 
Tim at Lone Pine Koala Sanctuary
I just made a new admin report
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers
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)
Picture of sam marshall
Re: I just made a new admin report
Group DevelopersGroup Particularly helpful MoodlersGroup Testers
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. smile

--sam
 
Average of ratings: -
Ray Lawrence
Re: I just made a new admin report
Group Particularly helpful Moodlers
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: -
Ray Lawrence
Re: I just made a new admin report
Group Particularly helpful Moodlers
Just had it pointed out to me that this is an Admin report. blush

Installs as expected when dropped into admin/report. Will play later.
 
Average of ratings: -
Me, with my new 'slow explosion' hair!
Re: I just made a new admin report
Group DevelopersGroup Particularly helpful Moodlers
Thanks Tim, this is really useful and will drop my use of PHPMyAdmin down considerably.

Paul. smile
 
Average of ratings: -
Picture of Gavin Henrick
Re: I just made a new admin report
Group Moodle Course Creator Certificate holdersGroup Testers
I suspect this will produce a raft of sql queries being shared around to produce reports, really nicely done.
 
Average of ratings: -
Picture of Alex Büchner
Re: I just made a new admin report
 
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: -
Picture of Mark Fawcett
Re: I just made a new admin report
 

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: -
Tim at Lone Pine Koala Sanctuary
Re: I just made a new admin report
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers
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)
Picture of Rob Rashotte
Re: I just made a new admin report
 

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: -
Tim at Lone Pine Koala Sanctuary
Re: I just made a new admin report
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers
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: -
Picture of Rob Rashotte
Re: I just made a new admin report
 

Perfect. much better.

Thanks;

Rob

 
Average of ratings: -
Sketch...
Re: I just made a new admin report
Group Particularly helpful Moodlers

BRILLIANT! Just what I was looking for!

Thanks Tim.

 
Average of ratings: -
Picture of Nick Thompson
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: -
Tim at Lone Pine Koala Sanctuary
Re: I just made a new admin report
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers
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: -
Picture of elearning developer
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: -
Tim at Lone Pine Koala Sanctuary
Re: I just made a new admin report
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers
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: -
Picture of yonney atsu
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: -
Tim at Lone Pine Koala Sanctuary
Re: Help with scheduled reporting
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers

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: -
Picture of yonney atsu
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: -
Me at the Moodle Moot NZ11
Re: I just made a new admin report
Group Particularly helpful Moodlers
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 smile

Stu
 
Average of ratings: -
Tim at Lone Pine Koala Sanctuary
Re: I just made a new admin report
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers
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)
Sketch...
Re: I just made a new admin report
Group Particularly helpful Moodlers

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: -
Picture of Alex Büchner
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: -
Sketch...
Re: I just made a new admin report
Group Particularly helpful Moodlers

blush 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: -
Tim at Lone Pine Koala Sanctuary
Re: I just made a new admin report
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers
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)
Me at the Moodle Moot NZ11
Re: I just made a new admin report
Group Particularly helpful Moodlers
Thanks Tim - worked perfectly.
Although I'm not sure why! More reading required...
Thanks for setting up the 'shared queries' area too smile
Cheers, Stu.
 
Average of ratings: -
Picture of Paul Holden
Re: I just made a new admin report
Group Particularly helpful Moodlers
Cheers Tim, this is really useful approve

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: -
Tim at Lone Pine Koala Sanctuary
Re: I just made a new admin report
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers
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: -
Picture of Jeremy Menter
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: -
Tim at Lone Pine Koala Sanctuary
Re: I just made a new admin report
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers
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: -
Picture of Jeremy Menter
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: -
Tim at Lone Pine Koala Sanctuary
Re: I just made a new admin report
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers
I had a conference call I had to attend, and managed to implement this in parallel.
 
Average of ratings: -
Picture of Ebenezer Le Page
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: -
Tim at Lone Pine Koala Sanctuary
Re: I just made a new admin report
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers
The problem is probably not with the student role, but with the Authenticated user role.
 
Average of ratings: -
Picture of Ebenezer Le Page
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: -
Picture of Daniel Neis Araujo
Re: I just made a new admin report
Group DevelopersGroup Particularly helpful MoodlersGroup Translators
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: -
Tim at Lone Pine Koala Sanctuary
Re: I just made a new admin report
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers
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: -
Tim at Lone Pine Koala Sanctuary
Re: I just made a new admin report
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers
Fix now in CVS. Will be in the zip downloads from tomorrow.

Thanks for your help.
 
Average of ratings: -
Picture of Gaurav KondeDeshmukh
Re: I just made a new admin report
 
I have installed admin report correctly i.e admin/report/customsql
but i am not able to view cutomsql from administration block..

Version - 1.9

 
Average of ratings: -
Tim at Lone Pine Koala Sanctuary
Re: I just made a new admin report
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers
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: -
Picture of Gaurav KondeDeshmukh
Re: I just made a new admin report
 
Hi..
Thanks for replying.
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: -
Tim at Lone Pine Koala Sanctuary
Re: I just made a new admin report
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers
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: -
Picture of HJWUCGA INC.
Re: I just made a new admin report
 
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: -
Picture of HJWUCGA INC.
Re: I just made a new admin report
 
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: -
Alan Arnold photo
Re: I just made a new admin report
 
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 smile

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: -
Tim at Lone Pine Koala Sanctuary
Re: I just made a new admin report
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers
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: -
Picture of Esteban Naranjo
Re: I just made a new admin report
 

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?

Thanks is advance,

Esteban

 
Average of ratings: -
Tim at Lone Pine Koala Sanctuary
Re: I just made a new admin report
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers
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:

/// Check all admin report plugins and upgrade if necessary
upgrade_plugins('report', $CFG->admin.'/report', "$CFG->wwwroot/$CFG->admin/index.php");
 
Average of ratings: -
Picture of Esteban Naranjo
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: -
Tim at Lone Pine Koala Sanctuary
Re: I just made a new admin report
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers
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: -
Picture of Esteban Naranjo
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: -
Tim at Lone Pine Koala Sanctuary
Re: I just made a new admin report
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers
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: -
Picture of Esteban Naranjo
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: -
Tim at Lone Pine Koala Sanctuary
Re: I just made a new admin report
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers
Weird. So moodle is completely ignoring the new report and not installing it. I can't think why that might be.
 
Average of ratings: -
Picture of Esteban Naranjo
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: -
Tim at Lone Pine Koala Sanctuary
Re: News
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers
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: -
Picture of Esteban Naranjo
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: -
Picture of james mergenthaler
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(
        'riskbitmask' => RISK_PERSONAL,
        'captype' => 'read',
        '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(
        )
    )
);

Please enlighten and thanks.

 
Average of ratings: -
Tim at Lone Pine Koala Sanctuary
Re: I just made a new admin report
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers

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: -
Picture of james mergenthaler
Re: I just made a new admin report
 

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: -
Picture of Paul crookell
Re: I just made a new admin report
 
This sounds great, REALLY great! smile

however, after a seemingly smooth installation, I am faced with a blank page at
http://domain/admin/report/customsql/index.php

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

(am running version 1.9.7)

Paul C
 
Average of ratings: -
Tim at Lone Pine Koala Sanctuary
Re: I just made a new admin report
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers
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: -
Picture of Paul crookell
Re: I just made a new admin report
 
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: -
Tim at Lone Pine Koala Sanctuary
Re: I just made a new admin report
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers
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: -
Picture of Paul crookell
Re: I just made a new admin report
 
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: -
Picture of Tommaso Gri
Re: I just made a new admin report
 

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: -
Tim at Lone Pine Koala Sanctuary
Re: I just made a new admin report
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers
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: -
Picture of Tommaso Gri
Re: I just made a new admin report
 
Is it possible to modify this module for verion 1.8?
 
Average of ratings: -
Tim at Lone Pine Koala Sanctuary
Re: I just made a new admin report
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers
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: -
Picture of Barbara Lawrence
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: -
Tim at Lone Pine Koala Sanctuary
Re: Problem with Monthly Usage by role report
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers
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: -
Picture of Aliyu Dauda
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

Kindly help out, any helpful suggestion will be gladly accepted.

Thank you.

 
Average of ratings: -
Tim at Lone Pine Koala Sanctuary
Re: Problem with Monthly Usage by role report
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers
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 wink. If you are running a newer version, then I am confused.
 
Average of ratings: -
Picture of Aliyu Dauda
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 wide eyes)

 
Average of ratings: -
Tim at Lone Pine Koala Sanctuary
Re: Problem with Monthly Usage by role report
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers
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: -
Picture of Aliyu Dauda
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: -
Picture of Andrew Eigus
Re: I just made a new admin report
 
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: -
Tim at Lone Pine Koala Sanctuary
Re: I just made a new admin report
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers
The link in the admin block is controlled by the file /ou_head/admin/report/customsql/settings.php

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: -
Picture of Andrew Eigus
Re: I just made a new admin report
 
> 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: -
Tim at Lone Pine Koala Sanctuary
Re: I just made a new admin report
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers
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: -
Don
One of our custom reports returns errors
Group Testers
I would love some debug help if anyone has a moment.

User activity report.

SELECT FROM_UNIXTIME(prefix_log.time),
prefix_user.username,
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: -
Tim at Lone Pine Koala Sanctuary
Re: One of our custom reports returns errors
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers
The report automatically adds LIMIT 5000, so you can't add your own limit.

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)
Don
Re: One of our custom reports returns errors
Group Testers
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: -
Picture of nutan kale
Re: I just made a new admin report
 

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: -
Mt. Fuji
Re: I just made a new admin report
Group Particularly helpful Moodlers
Tim,

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

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",
ROUND(prefix_grade_grades.finalgrade, 2) AS "Final Grade",
prefix_user.firstname AS "First Name",
prefix_user.lastname AS "Last Name",
prefix_user.username AS "Username"
FROM
prefix_grade_grades
INNER JOIN prefix_user ON prefix_grade_grades.userid = prefix_user.id
INNER JOIN prefix_grade_items ON prefix_grade_grades.itemid = prefix_grade_items.id
INNER JOIN prefix_course ON prefix_grade_items.courseid = prefix_course.id
WHERE prefix_grade_items.id = prefix_grade_grades.itemid
AND (prefix_grade_items.courseid = prefix_course.id)
AND (prefix_grade_grades.finalgrade > 0)
AND (prefix_user.deleted = 0)
AND (prefix_grade_grades.timecreated IS NULL)
GROUP BY prefix_user.username
ORDER BY prefix_grade_grades.id

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 smile

Jason

P.S. EDIT - I just removed the following line as I realized it was problematic.
AND (prefix_grade_grades.usermodified IS NULL)
 
Average of ratings: -
Tim at Lone Pine Koala Sanctuary
Re: I just made a new admin report
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers
First an aside: Do you know about table aliases. If, in the FROM clause, you do

FROM
prefix_grade_grades gg
INNER JOIN prefix_user u ON prefix_grade_grades.userid = prefix_user.id
INNER JOIN prefix_grade_items gi ON prefix_grade_grades.itemid = prefix_grade_items.id
INNER JOIN prefix_course c ON prefix_grade_items.courseid = prefix_course.id

then everywhere else in the query, you can write gg instead of prefix_grade_grades. I think that helps readability.


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: -
Mt. Fuji
Re: I just made a new admin report
Group Particularly helpful Moodlers
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 smile

Jason

 
Average of ratings: -
Tim at Lone Pine Koala Sanctuary
Re: I just made a new admin report
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers
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

ORDER BY prefix_user.id. prefix_course.id, prefix_grade_item.id

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: -
Mt. Fuji
Re: I just made a new admin report
Group Particularly helpful Moodlers
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",
ROUND(prefix_grade_grades.finalgrade, 2) AS "Final Grade",
prefix_user.firstname AS "First Name",
prefix_user.lastname AS "Last Name",
prefix_user.username AS "Username"
FROM
prefix_grade_grades
INNER JOIN prefix_user ON prefix_grade_grades.userid = prefix_user.id
INNER JOIN prefix_grade_items ON prefix_grade_grades.itemid = prefix_grade_items.id
INNER JOIN prefix_course ON prefix_grade_items.courseid = prefix_course.id
WHERE prefix_grade_items.id = prefix_grade_grades.itemid
AND (prefix_grade_items.courseid = prefix_course.id)
AND (prefix_grade_grades.finalgrade > 0)
AND (prefix_user.deleted = 0)
AND (prefix_grade_grades.timecreated IS NULL)
AND prefix_grade_items.itemtype = 'course'
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: -
Mt. Fuji
Re: I just made a new admin report
Group Particularly helpful Moodlers
Tim or anyone else willing to help smile,

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
GROUP BY username
ORDER BY username

Jason
 
Average of ratings: -
Tim at Lone Pine Koala Sanctuary
Re: I just made a new admin report
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers
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
mdl_user.username, mdl_user.firstname, mdl_user.lastname, frog_
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
GROUP BY username
ORDER BY username
 
Average of ratings: -
Picture of Jeff Snyder
Re: I just made a new admin report
 

Tim,  it seems the link to download is broken.  I'd love to have this capability!  Is there another site for download?

Thank you

--Jeff Snyder

 
Average of ratings: -
Tim at Lone Pine Koala Sanctuary
Re: I just made a new admin report
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers
There was a problem with the Moodle download server today. It is working again now.
 
Average of ratings: -
Mt. Fuji
Re: I just made a new admin report
Group Particularly helpful Moodlers
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: -
Tim at Lone Pine Koala Sanctuary
Re: I just made a new admin report
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers
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: -
Picture of Gaurang Raval
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",
ROUND(prefix_grade_grades.finalgrade, 2) AS "Two Class Test Total"
FROM
prefix_grade_grades
INNER JOIN prefix_user ON prefix_grade_grades.userid = prefix_user.id
INNER JOIN prefix_grade_items ON prefix_grade_grades.itemid = prefix_grade_items.id
INNER JOIN prefix_course ON prefix_grade_items.courseid = prefix_course.id
INNER JOIN prefix_course_categories pcc ON prefix_course.category = pcc.id
WHERE prefix_grade_items.id = prefix_grade_grades.itemid
AND (prefix_grade_items.courseid = prefix_course.id)
AND (prefix_grade_grades.finalgrade < 75)
AND (prefix_grade_grades.finalgrade > 0)
AND (prefix_user.deleted = 0)
AND (prefix_grade_grades.timecreated IS NULL)
AND prefix_grade_items.itemtype = 'category'
AND prefix_grade_items.itemname = 'TATOTAL'
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: -
Me at the Moodle Moot NZ11
Re: Retrieving Custom grade category for all the course in a course category
Group Particularly helpful Moodlers

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 smile
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 smile

 
Average of ratings: -
Picture of Kevin Freeborn
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
timeadded

I would like the report to show:

groupid
groupname
user.firstname
user.lastname
date added

I look forward to any help you can provide.

Kevin

 
Average of ratings: -
Picture of yonney atsu
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: -
C'est moi :-)
Re: new admin report CustomSQL queries
Group Documentation writersGroup Particularly helpful MoodlersGroup TestersGroup Translators

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

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 wink

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 tongueout

 
Average of ratings: -
Tim at Lone Pine Koala Sanctuary
Re: new admin report CustomSQL queries
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers

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: -
Picture of dfx dfx
Re: I just made a new admin report
 

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 qcount
from
prefix_course a

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

Thank you in advance.

 
Average of ratings: -
Tim at Lone Pine Koala Sanctuary
Re: I just made a new admin report
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers

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: -
Picture of Graham Clark
Re: I just made a new admin report
 

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: -
Tim at Lone Pine Koala Sanctuary
Re: I just made a new admin report
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers

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: -
Sketch...
Re: I just made a new admin report
Group Particularly helpful Moodlers

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

 
Average of ratings: -
Tim at Lone Pine Koala Sanctuary
Re: I just made a new admin report
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers

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)
Picture of Juan Leyva
Re: I just made a new admin report
Group DevelopersGroup Moodle HQGroup Particularly helpful Moodlers
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)
Picture of Susan Mangan
Re: I just made a new admin report
Group Particularly helpful Moodlers

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: -
Tim at Lone Pine Koala Sanctuary
Re: I just made a new admin report
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers

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: -
Picture of Susan Mangan
Re: I just made a new admin report
Group Particularly helpful Moodlers

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 smile

 
Average of ratings: -
Picture of Dmitry Utkin
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?

Thanxsmile

 

 
Average of ratings: -
Picture of ivan espitia
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: -
Picture of Dmitry Utkin
Re: I just made a new admin report
 

Could you please describe it i details.

Thanxsmile

 
Average of ratings: -
Sketch...
Re: I just made a new admin report
Group Particularly helpful Moodlers

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: -
Tim at Lone Pine Koala Sanctuary
Re: I just made a new admin report
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers

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: -
Picture of Elizabeth Dalton
Re: I just made a new admin report
Group Particularly helpful Moodlers

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

 
Average of ratings: -
Tim at Lone Pine Koala Sanctuary
Re: I just made a new admin report
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers

Don't contact anyone. We eventually got around to doing it. smile Just download the latest version.

 
Average of ratings: -
Picture of Elizabeth Dalton
Re: I just made a new admin report
Group Particularly helpful Moodlers

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

 
Average of ratings: -
Picture of Elizabeth Dalton
Re: I just made a new admin report
Group Particularly helpful Moodlers

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 Course
FROM prefix_course AS c
WHERE 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. sad 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: -
Tim at Lone Pine Koala Sanctuary
Re: I just made a new admin report
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers

That sounds basically right.

 
Average of ratings: -
Pedro Crispim
Re: I just made a new admin report
 

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: -
Tim at Lone Pine Koala Sanctuary
Re: I just made a new admin report
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers

That looks plausible.

 
Average of ratings: -
Pedro Crispim
Re: I just made a new admin report
 

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: -
Tim at Lone Pine Koala Sanctuary
Re: I just made a new admin report
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers

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

 
Average of ratings: -
Picture of William Mair
Re: I just made a new admin report
 

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: -
Tim at Lone Pine Koala Sanctuary
Re: I just made a new admin report
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers

Yes, it will sad

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: -
Picture of William Mair
Re: I just made a new admin report
 

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

 
Average of ratings: -
Tim at Lone Pine Koala Sanctuary
Re: I just made a new admin report
 
Average of ratings: -
Picture of james mergenthaler
Re: I just made a new admin report
 

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!mixed

 
Average of ratings: -
Picture of David Richter
Re: I just made a new admin report
 

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

+ link username to a user activity graph report

SELECT concat('<a target="_new" href="%%WWWROOT%%/course/user.php?id=1&user=',u.id,'&mode=alllogs">',u.firstname ,' ',u.lastname,'</a>') AS Username
,count(*) AS logins
,(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?

Thanks in advance!

 
Average of ratings: -
Picture of Peter Roberts
Re: I just made a new admin report
Group Testers

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: -
Picture of Peter Roberts
Re: I just made a new admin report
Group Testers

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: -
Picture of Steve Bond
Re: I just made a new admin report
 

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 (2)
Picture of Susan Mangan
Re: I just made a new admin report
Group Particularly helpful Moodlers

Brilliant, thank you!!!

 
Average of ratings: -
Picture of roc mehra
Re: I just made a new admin report
 

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.
Thanks in advance.

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: -
Picture of Michael McCoy
Re: I just made a new admin report
 

Awesome! Been looking for this fix forever!!

 
Average of ratings: -
Picture of Jay Hickman
Re: I just made a new admin report
 

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
mdl_grade_categories

 

Field
fullname

 

Thanks in advance, any help would be greatly appreciated.

 

Jay Hickman

 

Agape Christian Academy


 
Average of ratings: -
Oam
Re: I just made a new admin report
Group Particularly helpful Moodlers

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: -
Tim at Lone Pine Koala Sanctuary
Re: I just made a new admin report
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers

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.
  3. Then go to admin notifications to run the upgrade (or, better, use the command-line upgrade script).

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: -
Oam
Re: I just made a new admin report
Group Particularly helpful Moodlers

Hello !

Thanks for the answer.

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: -
Picture of Tyler Gilpin
Re: I just made a new admin report
 

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: -
Picture of Jesus Ramirez
Re: I just made a new admin report
 

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: -
Picture of roc mehra
Re: I just made a new admin report
 

Hi , I have download plugin. But when i drop into admin/report.

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

Please help me how to use and install. I am new to moodle

 
Average of ratings: -
Picture of Rajiv Agarwal
Re: I just made a new admin report
 

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',

ROUND(gg.finalgrade,2) AS Grade,

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: -
Tim at Lone Pine Koala Sanctuary
Re: I just made a new admin report
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers

"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: -
Picture of Rajiv Agarwal
Re: I just made a new admin report
 

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

 
Average of ratings: -
Tim at Lone Pine Koala Sanctuary
Re: I just made a new admin report
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers

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)
Picture of Derek Runions
Re: I just made a new admin report
 

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",
ROUND(prefix_grade_grades.finalgrade, 2) AS "Final Grade",
prefix_user.firstname AS "First Name",
prefix_user.lastname AS "Last Name",
prefix_user.username AS "Username"
FROM
prefix_grade_grades
INNER JOIN prefix_user ON prefix_grade_grades.userid = prefix_user.id
INNER JOIN prefix_grade_items ON prefix_grade_grades.itemid = prefix_grade_items.id
INNER JOIN prefix_course ON prefix_grade_items.courseid = prefix_course.id
WHERE prefix_grade_items.id = prefix_grade_grades.itemid
AND (prefix_grade_items.courseid = prefix_course.id)
AND (prefix_grade_grades.finalgrade > 0)
AND (prefix_user.deleted = 0)
AND (prefix_grade_grades.timecreated IS NULL)
AND prefix_grade_items.itemtype = 'course'
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: -
Picture of Ajeet Kumar
How to get report of course completion
 

I need sql query to list all user who completed course


 
Average of ratings: -
Me at the Moodle Moot NZ11
Re: How to get report of course completion
Group Particularly helpful Moodlers

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

 
Average of ratings: -
Womble
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: -
Richard
Re: I just made a new admin report
 

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
END AS 'Item Name', ROUND(gg.finalgrade,2) AS Score,ROUND(gg.rawgrademax,2) AS MAX, ROUND(gg.finalgrade / gg.rawgrademax * 100 ,2) AS Percentage,

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_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 AND gi.itemname != 'Attendance'
ORDER BY 'Name' ASC

 

 
Average of ratings: -
Picture of Damien Wendlinger
Re: I just made a new admin report
 

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: -
Picture of Jack Challenger
Re: I just made a new admin report
 

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: -