Moodle plugins directory: Ad-hoc database queries | Moodle.org
Ad-hoc database queries
Reports ::: report_customsql
Maintained by Tim Hunt, 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:
4497 sites
1k downloads
165 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 quickly set up ad-hoc reports, without having to create a whole new admin report plugin.
Contributors
Tim Hunt (Lead maintainer)
Mahmoud Kassaei: Developer
Anupama Sarjoshi: Tester & Developer
Please login to view contributors details and/or to contact them
However, there is a way to fake it, which is this.
Suppose you have variable cohort_ids with a value like 123,456,789. Then you can do
WHERE CONCAT(',', :cohort_ids, ',') LIKE CONCAT('%,', cohort.id, ',%')
This is not particularly efficient, but it works.
This plugin is a must be on the box. I wonder to know if is compatible with Moodle 4.1, and 4.2
Formally, my organization, demands that any software should be compatible to with the platform and products installed (we have Moodle 4.1).
Sincerely
Alfredo
Csaba
with 8.1.25 PHP version I have this alert "PHP Deprecated: Function strftime() is deprecated in /var/www/vhosts/elearning.formazionelavoratori.online/httpdocs/report/customsql/locallib.php on line 611"
I have been struggling to create the proper SQL statement to create a valid URL.
I have tried several variations and reached out to the Moodle community.
The statement below results in a plain text with [site www], course fullname
CONCAT('%%WWWROOT%%/course/view.php%%c%%id=',c.fullname) AS "Link",
Can someone please provide me with the proper SQL statement for a course URL?
Kind Regards
There may be a more succinct way but the following should work. You must create one column 'X' and then a second column with a title like 'X_link_url'. When the report is run it will combine both into one column.
Here is an example that will print the course title and the hyperlink will take you to the course:
SELECT
c.fullname as course_name,
CONCAT('%%WWWROOT%%/course/view.php',CHAR(63),'id=',c.id) as course_name_link_url
FROM prefix_course c
Hope this helps.
Thank you!!!!
While this solution is written in the Notes section of the plugin, my attempts did not correctly write the statement. I over thought the instruction.
Kind Regards,
Shirley
I don’t know anything about SQL but desperately need a report that collect data about both course completion and information from two feedback activities.
Can someone help me out?
/Niklas
When I've tried a complex query for just 1000 row, plugins works perfectly, but when I increased to 30K, I just got blank row.
But when I tried a simple query (only 3 field), got 30K perfectly.
What should I do ?
Regards,
I guess that if the query is too big, then the server will run out of memeory. (But, that is just a guess.)
"u.id=:userid" works for this report. There is a "More documentation on this plugin" link above. Also, when you are editing a query, there is a lot of information on the screen about what you can do.
Will it be?
Best,
Fabian