Reports: Ad-hoc database queries

report_customsql
Maintained by Tim at Lone Pine Koala Sanctuary Tim Hunt, at the OU (Perry building) Mahmoud Kassaei
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.
318 sites
302 downloads
30 fans
2288 sites
1k downloads
93 fans

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
Please login to view contributors details and/or to contact them

Comments RSS

Show comments
  • Picture of Gregor McNish
    Thu, Feb 21, 2019, 7:57 AM
    We've been giving staff access to a system "Report Viewer" role so they can access the ad-hoc reports. Under 3.1, the link showed up under Site Administration for those users. When we upgraded to 3.5, the site admin link is no longer visible for these staff; though the direct url still works for them. How do we make the link to ad-hoc reports available to staff?
  • Tim at Lone Pine Koala Sanctuary
    Thu, Feb 21, 2019, 3:16 PM
    Gregor, that is not something we changed in this plugin. It is something that changed in Moodle core, or at least in the standard Moodle theme. You will need to raise it elsewhere. I don't know.
  • Picture of Olli Savolainen
    Thu, Feb 21, 2019, 4:18 PM
    Hi. Apparently this creates UTF8 csv files with the byte order mark, which is not recommended and creates compatibility issues. (I know, also *not* having it apparently does... :/ ). However, I didn't find adding it explicitly in the source code. Any way to remove it from csv output? Thanks.
  • Tim at Lone Pine Koala Sanctuary
    Thu, Feb 21, 2019, 5:06 PM
    I agree with your analysis. There is nothing in the code to write a byte-order mark. It just does fopen (https://github.com/moodleou/moodle-report_customsql/blob/master/locallib.php#L95) then (https://github.com/moodleou/moodle-report_customsql/blob/master/locallib.php#L458) it calls fwrite (https://github.com/moodleou/moodle-report_customsql/blob/master/locallib.php#L455).

    Sorry. I have no idea at the moment.
  • Tim at Lone Pine Koala Sanctuary
    Thu, Feb 21, 2019, 5:08 PM
    Oh! one thing to check: the report writes the files to moodledata/customsql (or something like that) then apache serves the file from there. Can you check the files in moodledata. That would tell us if the mark is being added by moodle writing the file, or by apache serving it.
  • Picture of Olli Savolainen
    Fri, Mar 1, 2019, 7:22 PM
    Thanks, Tim.
    Yes, the bom was produced elsewhere than moodle in the end.
  • Tim at Lone Pine Koala Sanctuary
    Fri, Mar 1, 2019, 7:24 PM
    Thanks for confirming that.
  • Picture of Nicholas Stefanski
    Wed, Mar 27, 2019, 6:18 AM
    Hi Tim, are you accepting contributions to this plugin? We had a need for hourly reports at my institution, so I made a few additions to add a category of reports that are generated every time the CRON task runs. Would this be something you're interested in adding to the master version?
  • Tim at Lone Pine Koala Sanctuary
    Wed, Mar 27, 2019, 7:03 PM
    Yes, contributions to this plugin are welcome.

    At least, that is what we hope to do. Sometimes I get too busy, and don't have time to review proposed changes as soon as they are permitted. You may have to be patient, in which case I apologies in advice. Still, do get your suggested changes onto github as a pull request.
  • Picture of sharika ha
    Fri, Apr 12, 2019, 5:15 PM
    why i always get this error :
    The type of the file you selected (application/msword) does not match the type expected by this import format (application/vnd.openxmlformats-officedocument.wordprocessingml.document).

    i use .doc file type.
    really need help sad
  • Picture of Brandon Jimenez
    Sat, Apr 13, 2019, 12:28 AM
    Hello Sharika,

    Without going into too much detail, the error you mentioned is probably caused because the Word version you're using doesn't use XML, so consider saving your Word file (.doc) as a more recent version (.docx) and try using it again.
  • Picture of Lisa T
    Sat, Apr 13, 2019, 2:53 AM
    Hello,
    Is it possible to execute a stored procedure and pass in a variable? I am trying to implement some custom reports by querying the database. We have moodle 3.6 and using the Adaptable Theme. I have a NavBar tool menu named Reports, and my plan is to put the custom reports in as drop down options for the Teachers. I am successful with putting simple queries in the menu but I was thinking it would be better to put complex reports in using stored procedures. I am very comfortable with writing SQL scripts but new to Moodle.

    I am using: Exec [dbo].[usp_MoodleTimeReport] @userid=13. I tried removing the variable and hard coding it in the report for testing but I always get the below error.

    Error when executing the query: Error reading from database SQLState: 42000
    Error Code: 156
    Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword 'ORDER'.
    SQLState: 42000
    Error Code: 153
    Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid usage of the option NEXT in the FETCH statement.
    Exec [dbo].[usp_MoodleTimeReport] ORDER BY 1 OFFSET 0 ROWS FETCH NEXT 2 ROWS ONLY [array egg]

    I don't see any way to attach a pic to this post.

    Any help would be appreciated!
    Thanks,
    Lisa
  • Picture of Mohamed Atia
    Wed, May 1, 2019, 3:27 PM
    Hi Tim and Mahmoud,
    Thank you for providing this useful plugin, I created the below query to get number of students enrollment per course but it looks like the multi-lang filter is not applied so I got the course name as {mlang en} Hyperledger Fabric Chaincode {mlang} {mlang ar} الشفرة المسلسلة للـهايبرليدجر فابريك {mlang}

    SELECT c.fullname AS 'Course Name',
    ,COUNT(u.username) AS 'Number of Students'
    FROM {role_assignments} AS r
    JOIN {user} AS u on r.userid = u.id
    JOIN {role} AS rn on r.roleid = rn.id
    JOIN {context} AS ctx on r.contextid = ctx.id
    JOIN {course} AS c on ctx.instanceid = c.id
    WHERE rn.shortname = 'student'
    GROUP BY c.fullname, rn.shortname


    So I created the below query which remove multilang tags and When I tried it in PHPMYADMIN and I got it work correctly but when I use it in the plugin I got a wrong course name as

    English:
    Hyperledger Fabric Chaincode {mlang} {mlang ar} الشفرة المسلسلة للـهايبرليدجر فابريك {mlang}

    Arabic:
    الشفرة المسلسلة للـهايبرليدجر فابريك {mlang}

    SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(c.fullname, '{mlang en}',-1),'{mlang}',1) as 'Course Name EN',
    SUBSTRING_INDEX(SUBSTRING_INDEX(c.fullname, '{mlang ar}', -1),'{mlang}',1) as 'Course Name AR',
    c.shortname as 'Short Name'
    ,COUNT(u.username) AS 'Number of Students'
    FROM {role_assignments} AS r
    JOIN {user} AS u on r.userid = u.id
    JOIN {role} AS rn on r.roleid = rn.id
    JOIN {context} AS ctx on r.contextid = ctx.id
    JOIN {course} AS c on ctx.instanceid = c.id
    WHERE rn.shortname = 'student'
    GROUP BY c.fullname, rn.shortname
  • Tim at Lone Pine Koala Sanctuary
    Wed, May 1, 2019, 8:22 PM
    It is intentional that the output is not passed through Moodle's text filters. Generally speaking, these are report for admins, to show the actual data in the database.

    So, doing what you tried: breaking up the string in the SQL query, is probably the best way to do this if that is what you want. I am surpised that the same SQL would work differently in PHPMyAdmin and in the report. The only think I can think of is that in the way that Moodle handles database queries, { and } are special characters. That is probably what is cusing the problem. Try replacing them with CHR(NN).
  • Picture of Tifano T
    Wed, May 15, 2019, 11:50 AM
    Hello,
    Thanks for this useful and great plugin, however, I have one issues if you could help.
    CSV file exported from this plugin (unlike from Configurable Reports ) contains double quotes " in every data in the colum (For example, "xy@yahoo.com", "Fred", "Smith"). Is there anywhere I can set it to omit that? The exported CSV wouldn't work with our SMS due to that.

    Thanks for your time responding to this.

    Kind Regards,
    Tifano
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
Please login to post comments