Ad-hoc database queries

Reports ::: report_customsql
Maintained by Tim at Lone Pine Koala SanctuaryTim Hunt, at the OU (Perry building)Mahmoud Kassaei, Anupama Sarjoshi
This report plugin allows Administrators to set up arbitrary database queries to act as ad-hoc reports. Reports can be of two types, either run on demand, or scheduled to run automatically. Other users with the right capability can go in and see a list of queries that they have access to. Results can be viewed on-screen or downloaded as CSV.
Latest release:
3836 sites
989 downloads
148 fans
Current versions available: 10

This report, created by The Open University, lets Administrators set up arbitrary SQL select queries that anyone with the appropriate permissions can then run. Reports can be set to be runnable on-demand, or automatically run weekly or monthly.

The results are displayed as a fairly plain HTML table, and can also be downloaded as CSV.

The idea is that this lets you quicly set up ad-hoc reports, without having to create a whole new admin report plugin.

Screenshots

Screenshot #0

Contributors

Tim at Lone Pine Koala Sanctuary
Tim Hunt (Lead maintainer)
at the OU (Perry building)
Mahmoud Kassaei: Developer
Anupama Sarjoshi: Tester & Developer
Please login to view contributors details and/or to contact them

Comments RSS

Comments

  • Fri, Sep 24, 2021, 4:47 PM
    H I added the plugin and want to run the custom report (Assignments (and Quizzes) waiting to be graded). I tried to add the code mention below. But unable to run the report and save. Can anyone guide me how to do that and run this report.

    SELECT concat('',c.fullname,'') AS Course

    ,(SELECT CONCAT(u.firstname,' ', u.lastname) AS Teacher
    FROM prefix_role_assignments AS ra
    JOIN prefix_context AS ctx ON ra.contextid = ctx.id
    JOIN prefix_user AS u ON u.id = ra.userid
    WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS Teacher

    ,concat('מטלות') AS Assignments

    ,concat('בחנים') AS 'Quizzes'

    ,(SELECT COUNT(*)
    FROM prefix_course_modules cm
    JOIN prefix_modules as m ON m.id = cm.module
    WHERE m.name LIKE 'quiz' AND cm.course = c.id
    GROUP BY cm.course
    ) AS 'nQuizzes'

    ,(SELECT COUNT(*)
    FROM prefix_quiz_attempts AS qa
    JOIN prefix_quiz AS q ON q.id = qa.quiz
    WHERE q.course = c.id
    AND qa.timefinish = 0
    GROUP BY q.course) AS 'unFinished Quiz attempts'

    ,(SELECT COUNT(*)
    FROM prefix_quiz_attempts AS qa
    JOIN prefix_quiz AS q ON q.id = qa.quiz
    WHERE q.course = c.id
    AND qa.timefinish > 0
    GROUP BY q.course) AS 'finished quiz attempts'

    ,(SELECT Count( ra.userid ) AS Users
    FROM prefix_role_assignments AS ra
    JOIN prefix_context AS ctx ON ra.contextid = ctx.id
    WHERE ra.roleid = 5
    AND ctx.instanceid = c.id
    ) AS nStudents


    ,(
    SELECT count(a.id)
    FROM prefix_assignment AS a
    JOIN prefix_course_modules AS cm ON a.course = cm.course
    WHERE cm.instance = a.id AND cm.module = 1 AND a.course = c.id
    ) nAssignments

    ,(
    SELECT count(*)
    FROM prefix_assignment AS a
    WHERE a.course = c.id AND FROM_UNIXTIME(a.timedue) > NOW()
    GROUP BY a.course
    ) 'Open
    Assignments'

    , CONCAT(ROUND( (100 / iAssignments ) * iOpenAssignments ) ,'%') 'unFinished
    Assignments
    (percent)'

    ,(
    SELECT count(asb.id)
    FROM prefix_assignment_submissions AS asb
    JOIN prefix_assignment AS a ON a.id = asb.assignment
    JOIN prefix_course_modules AS cm ON a.course = cm.course
    WHERE asb.grade < 0 AND cm.instance = a.id AND cm.module = 1 AND a.course = c.id
    ) 'unChecked
    Submissions'

    ,(
    SELECT count(asb.id)
    FROM prefix_assignment_submissions AS asb
    JOIN prefix_assignment AS a ON a.id = asb.assignment
    JOIN prefix_course_modules AS cm ON a.course = cm.course
    WHERE cm.instance = a.id AND cm.module = 1 AND a.course = c.id
    ) 'Submitted
    Assignments'

    FROM prefix_course AS c
    LEFT JOIN (
    SELECT course, count(*) AS iAssignments
    FROM prefix_assignment AS a
    GROUP BY a.course
    ) AS tblAssignmentsCount ON tblAssignmentsCount.course = c.id

    LEFT JOIN (
    SELECT course, count(*) AS iOpenAssignments
    FROM prefix_assignment AS a
    WHERE FROM_UNIXTIME(a.timedue) > NOW()
    GROUP BY a.course
    ) AS tblOpenAssignmentsCount ON tblOpenAssignmentsCount.course = c.id

    WHERE 1=1
    #AND c.fullname LIKE '%תשעג%'
    %%FILTER_YEARS:c.fullname%%
    ## You can enable the SEMESTER filter as well,
    ## by uncommenting the following line:
    ## %%FILTER_SEMESTERS:c.fullname%%
    ORDER BY 'Open
    Assignments' DESC

    Thanks
  • Fri, Nov 19, 2021, 2:06 AM
    Question. In a "select count union" query, which is based on a value that can be entered via form/input, is it possible to use the same value recorded in the form/input on all the selections?
    I have tried declaring variables or setting a value associated with the input (e.g. :variable), but the code is not accepted.
    Is there any solution?
  • Tim at Lone Pine Koala Sanctuary
    Fri, Nov 19, 2021, 6:45 PM
    At the moment, there is not a good solution to this. Each placeholder being used exactly once is a limitation of the Moodle database system, which we just use as-is.

    Sometimes there are tricks you can do. For examle with a single query that joins serveral tables, you can include something like

    JOIN (SELECT :my_value AS my_value) input ON 1 = 1

    in the JOINs, then you can refer to input.my_value everywhere. I'm not sure if this trick can be adapted to work with UNIONs.

    I suppose that, at some point, we could consider adding a work-around to our plugin. Perhpas, if you have parameters called :my_value_copy1, :my_value_copy2, they could be automatically set to the same value as :my_value. Yes, that is a nice idea. we should do that. I created https://github.com/moodleou/moodle-report_customsql/issues/104.
  • Mon, Jan 24, 2022, 6:00 PM
    Hi, Tim, we have a problem.
    If I want my query to give me a report with more than 8.000 rows retuned, the query will not work. In the page where I'd see the results nothing happens and I can see just the description of the query.
    We have set in the plugin's settings, the maximum allowed limit rows returned as 50.000.
    Can you help us?
    The query has always worked without any problem since last week.
    Thanks
  • Tim at Lone Pine Koala Sanctuary
    Mon, Jan 24, 2022, 7:23 PM
    If you are seeing a blank page, then that means an error is happening. Turn on Debugging (https://docs.moodle.org/311/en/Debugging) so you can get the error message.
  • Mon, Feb 21, 2022, 7:04 PM
    Hi all,

    Tim and others who working on this plugin, thanks a lot, it is really helpful and powerful and we really appreciate your work.

    I am not sure if this is the right place to request improvement, but I think it would be great if we can add count() function to the calculation tab.

    Kind regards
    Motasem
  • Tim at Lone Pine Koala Sanctuary
    Thu, Feb 24, 2022, 1:43 AM
    I think you are posting under the wrong plugin. This report does not have that feature. Are you thinking of block_configurablereports?
  • Thu, Feb 24, 2022, 3:31 AM
    Thanks Tim (-:, you are right.
  • Fri, Feb 25, 2022, 9:14 PM
    Hi, is there a way to make a parameter optional? I currently let the user filter by country but when they dont enter sth in the input it gives back no data, instead it should give back the list of all users, independetly from the country. I use the parameter like this WHERE :country = u.country
  • Tim at Lone Pine Koala Sanctuary
    Fri, Feb 25, 2022, 10:26 PM
    The simplest work-around for now is to make two queries, one with the parameter and one without.

    in the description for each query, you can link to the other one.
  • Tue, Mar 1, 2022, 9:28 PM
    Hi all,

    I created a scheduled report from ad-hoc data base query and I did not receive the report to my email. I noticed that the record for that report in the database retrieve my userid correctly but has a comma before that, probably that what stop the plugin to send me the email.

    Regards
    Motasem
  • Fri, Apr 29, 2022, 1:07 AM
    Hi, we run daily, weekly and annual course completion reports. This gives us the completions during those time frames for all courses. I am looking for a way to configure the report so that only specific courses appear on the report, not all courses. Is there a way to accomplish this?
  • Tim at Lone Pine Koala Sanctuary
    Fri, Apr 29, 2022, 1:12 AM
    I am sure you can do that if you work out the right SQL. In the report, try adding something like

    WHERE course.shortname LIKE '%2022%'

    (to work out the exact details, you will meed to know SQL, and which courses you want the report to include.)
  • Fri, Apr 29, 2022, 1:26 AM
    Thank you, I’ve made a few attempts at the SQL and was able to get one course but not multiple. If I use WHERE course.shortname LIKE '%2022%' can I add multiple courses with a comma between? Or am I limited to one course?
  • Tue, May 10, 2022, 10:47 PM
    Hi Sue, if your course shortnames don't follow a patter that allows you to use %keyword% you could try (depending on the length of the list for manageability) WHERE course.shortname IN('courseName1', 'CourseName2') etc etc listing each course shortname in ' ' with a comma seperator. Alternatively, if your courses are organised in categories, you could create a report for a category or allowing the user running the report to enter a category name as a parameter.
Please login to post comments