I have a client that want all the reports of all participants to be shown in with all their courses, their level of completion of those courses and the time spent. And the report for every participant should appear on a single row.
To achieve this, I suggest using SQL queries and custom reports. Moodle provides a built-in feature called "Custom SQL Queries" where you can create and execute SQL queries directly on the Moodle database to retrieve the data you need.
Follow the steps below to create the custom report:
Access the Moodle database: You need access to the Moodle database to execute SQL queries. You can use tools like phpMyAdmin or MySQL Workbench to access the database.
Identify the required tables and columns: You need to identify the tables and columns in the Moodle database that store the relevant information, such as user data, course data, enrollment data, completion data, and time spent.
Write the SQL query: Write a SQL query that joins the required tables and retrieves the necessary data to generate the report. The query should include JOIN clauses to link the tables and WHERE clauses to filter the data as needed.
Create a custom report in Moodle: In Moodle, go to Site administration > Reports > Custom SQL queries. Click "Add a new SQL query" and enter a name for your report. Paste your SQL query into the SQL box. Configure other options like permissions and display options as needed.
Test and refine the report: Test your custom report to ensure it produces the desired results. If needed, refine your SQL query to adjust the data presentation or filter conditions.
Display the report on a page: You can choose to display the report on a specific page in Moodle. You may create a custom plugin or use an existing page to embed the report.
Note that writing custom SQL queries requires a good understanding of the Moodle database schema and SQL language. Be careful with your queries to avoid data corruption or security issues.
Additionally, before proceeding with custom SQL queries, make sure you have proper backups of your Moodle database to ensure you can restore the data in case anything goes wrong.
Alternatively, if you have the know-how and skills you could make a custom plugin that manages such tasks. This takes a lot of time and work to complete and security is also a risk when choosing this solution, especially with something that communicates with your DB.