THIS IS AN MUA PROJECT PROPOSAL THAT WILL BE OR HAS BEEN SUBMITTED FOR POSSIBLE MUA FUNDING.  ANY EXISTING TRACKER ITEMS THAT THIS MIGHT DUPLICATE SHOULD BE LINKED TO BELOW.  

Note: this project is being submitted by Ben Haensel and Gemma Lesterhuis. 

Overview

The Moodle Report Builder gives us user the oppertunity to select a report source and build based on this resource a report. 

The number of report resources are increasing, though as admins we can receive user request to build a report that cannot be created based on the available resources. 

Currently admins use different ways to achive these goals, by example: 

1. If you have access to the database directly from the database.
2. Webservices functions calls to an external platform, if they are available for what you want to achive
3. Plugins like  configurable report and SQL custom 

Why should we do this

As mentioned there are different ways in Moodle which admins can use to generated reports for their teachers. Though not all Moodle serviceproviders provided these options. MoodleCloud would be the best example, but there are many more. 

When it comes to plugins many of us rely on the community developers to maintain these plugins. As an example the configurable report block, maintained by 2 Moodle HQ employees has not been updated for a long time. Some serviceproviders will theirfor refuse to update the plugin because they are not sure if it's properly checked. As there is always a security risk in using plugins, especially a plugin that gives you peeks into the database, serviceproviders are very careful. 

When you use Webservices, the question then is if there is already a webservices available, and if not this should be build and as I stated above not all serviceprovider provide the option to install a 3th party plugin but also not all organizations that use Moodle have the budget to hire developers to do this. 

With launching the Report Builder in Moodle, alot of admins will now start using the provided templates. Even those who used the plugins before. But it is impossible to build a template for each userstory that an admin will encounter, look at the current list of contributed ad-hoc reports. 

Which will mean that admins that build reports for the teachers (or other users) that can use alternative solutions will end up with having reports on different places. Which will make this more difficult to manage (also security wise) and confuses teachers on where they have to be for which report. 

Taking these reasons in mind, I believe it would be great if we could use the Moodle Report Builder functionalities to accommodate these reports. So that users will have access to their reports from 1 source, instead of using different methodes. 

REAL LIVE STORIES FROM ..

Teacher  - without knowledge of Queries

As a teacher I got access to a report that my admin build with the Moodle Report Builder. The admin explain to me how it works and. I like this feature and asked my admin to build me another report. My admin informs me he cannot build this report in the Report Builder since there is no template for it. 

When I searched Moodle.org/plugins I found this 3th party plugins, and asked my admin if he can use this. The admin confirms that knowledge of writting queries are available in the organization, but they are not allowed to install plugins nor do they have access to the database to create an alternative. 

Supportdesk employee , role siteadmin -with knowledge of SQL queries 

In our organization we use the LTI Publish course option. In our supportdesk inbox I found an email from a student that access the course through their own LMS. The student claims that they finished a course and did not receive the grade. In the course I can see that the student has indeed completed the course and there is a grade in the gradebook. As a site admin I can check the LTI grade sync task log, and I found that the grade has succesfully been send. Earlier we experienced problems with a different student, and we found out with help of our hostingsprovider that they had migrated to a different lms and we where communicating to the wrong serviceurl because this has not been updated. TO get this information I had to wait 2 days, because they had to check the database. I found with help of the moodle documentation which table I have to check. My hosting party prevents me to install 3th party plugins, as well have informed me that the 3th party plugins on moodle.org to have my Moodle version. Our organization budget is not that big, and each time I have to contact my serviceprovider I have to pay their fee. I noticed that Moodle Report builder is installed on my installation, though there is no standard report to check. What do I do? 

Admin request to a serviceprovider
I received the request from the organization to build a report. The Report Builder does not support this kind of report, as in there is no template. I do have the knowledge to write SQL queries. I ask my serviceprovider to install AdHoc Query Plugin because this is closest to my moodle version. I receive an email back for the serviceprovider that they don't install 3th party plugins on the Moodle Site. 

Admin searching for alternative options

As an admin I use Moodle 4.1 and really like the report builder, but I want to use an query that I found on the ad-hoc contributed reports|https://docs.moodle.org/310/en/ad-hoc_contributed_reports]. I know that my serviceprovider does not allow me to install plugins from Moodle.org that don't support my Moodle 4.1 version. I don't have the budget to hire a developer to check the plugin, I want to use. They also denied the request for access to the database, so I can run the query myself. They replied they can run the query but this will cost me their consultancy fee. I know that I will use this query on regular bases and my organization cannot affored to pay for each request. I have been search the webservices but there is none that will help me along. 

Due to the restrictions of my servicesprovider I will have to discuss with management to migrate the site to a different provider, though I know that budget is an overal issue. 

note: certainly more stories can be found, these are just a few I have encountered of the years.

Summary

• Project size: Large
• Audience: primary schools, universities, work places
• Target users: administrators

Goals

1. Create a report source called "SQL" so that admins can write a Select SQL query in an easy to use editor. 
2. Create a premission so that Admins can grant the Report source SQL premission to users they trust and understand the functionality. 

User Stories 

As an admin, I should be able to to call a report source called SQL so that I can write an SQL select query in the editor screen. It should not be possible to write a SQL query that updates or removes data from the database. 

As an admin, I should be able to verify the query which will ensures a check to see if the query can be runned by the database, if a query cannot be runned the error will show between the editor en and button. 

As an admin, I should be able to view the result of the query by clicking on a view result button. The query will be then shown between the editor in a table view. 

As an admin, I should be able to grant access to users by using the Report Builder Audience functionality so I can easily decided and control access over the report. 

As an admin, I should be able to schedule the report using the Report Builder Schedules functionality so I can email the report to the selected Audience.

As an admin, I should be able to see an overview of those I granted access using the Report Builder Access tab so I have a clear overview of the users.

As an admin, I should be able to grant an user premission to use the report Source SQL by adding the premission to the users system rol. 

As an admin, I want to see in the Moodle Security Checks report a 'warning' when users have the premission to create an SQL report with the Report Builder in a similar way as with the XSS trusted users and Backup of user data. 

Links to existing tracker issues, forum discussions, contrib plugins

• https://tracker.moodle.org/browse/MDL-78537
• https://tracker.moodle.org/browse/MDL-63233
• https://moodle.org/plugins/report_customsql  
• https://moodle.org/plugins/block_configurable_reports

Requirements

Further reading

• https://docs.moodle.org/402/en/ad-hoc_contributed_reports

• https://docs.moodle.org/402/en/Custom_SQL_queries_report

• https://moodle.org/mod/forum/discuss.php?d=153059

• https://mahakala.lesc.uec.ac.jp/mahoodle/moodle/docs/33/en/Main_Page/Managing_a_Moodle_site/Site-wide_reports/ad-hoc_contributed_reports.html