The Joule reports allow students, teachers, and administrators to view data about activity in a course or group of courses that is relevant to them. The Admin reports aggregate data site administrators and department heads on their courses and programs as a whole.
Access the Admin reports
Steps: Administration > Front page settings > Reports > Dashboard
The Admin reports allows an administrator to create a custom ad-hoc report based on an SQL query for a MySQL database.
- From the Front page settings > Reports > Dashboard.
- Create a query in the text box to create a new report.
All queries must be written for a MySQL database. See the MySQL documentation for syntax structure for a query. See the Query Overview for examples.
- Select Generate Report.
- Use any additional features to add to the report or save the query.
- If you have run previous reports in the same session, the Generate Report button may be hidden and replaces with the Filter button. Select Filter to display the report query text area and the Generate Report button.
- Select Reset to clear the query text area of any SQL report and start over or load a saved query.
- The Browse Schema button allows the administrator to view the database schema for their site as well as the structure of each table. The initial load of the schema can take a few seconds. To view a table's structure, select the table in the right-hand column and you view the table structure in the left-hand column.
- To save a query after you have generated a report from it, type the Name for the query and select Save Query. The report name now shows up in the saved query dialog when you select the Saved Queries button.
- To load a saved query, select Saved Queries. Select the saved query you want to load into the text area. The saved query overwrites any code that has been entered in the query text area. Select Generate Report to display the report.
- To delete a saved query, select Saved Queries and then select the Delete (X) icon. Select OK on the confirmation.
- Tabular data for all reports can be exported to a CSV file using the Export menu on the bottom of the page.
All field names that you want to display as columns in the report, must be specified and/or aliased. For example, the following query is not valid:
SELECT *<br />
You must specify field names such as id, username, firstname, lastname.
Table aliases may be used. For example, the following query is valid:
FROM mdl_user u
WHERE u.id = 1
Aggregate function fields must be aliased. For example, the following query is valid:
SELECT COUNT (id) as user_count
While the following query is not valid:
If a query has a colon in it then this must be concatenated with the rest of the string. This usually occurs when you are searching capabilities and plugins configuration variables. An example query is below:
(SELECT COUNT(id) FROM mdl_course) - 1 AS courses,
(SELECT COUNT(id) FROM mdl_user WHERE deleted = 0 AND confirmed = 1) AS users,
(SELECT COUNT (DISTINCT ra.userid)
FROM mdl_role_capabilities rc
JOIN mdl_role_assignments ra On ra.roleid - rc.roleid
WHERE rc.capability IN ('moodle/course:' || 'upd' || 'ate', 'moodle/site:' || 'doanything')) AS teachers,
(SELECT COUNT(id) FROM mdl_role_assignments) AS enrollments,
(SELECT COUNT(id) FROM mdl_forum_posts) AS forum_posts,
(SELECT COUNT(id) FROM mdl_resource) AS resources,
(SELECT COUNT(id) FROM mdl_question) AS questions
The tables that start with in the database schema are block collector tables and contain the collected data points as set in the Collect block's global settings.