Ad-hoc database queries
Maintained by
Tim Hunt, Mahmoud Kassaei, Hieu Vu Van
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.
Comments
Comments are no longer open for new posts. Existing comments remain available to read.
Hi - my Moodle system runs on a windows platform with MSSQL. Date fields are displaying as text fields when downloaded as Excel after an ad-hoc report runs and I am trying to work out how to ensure date fields are recognised by excel as dates so my end users can easily filter by year and month without having to first convert the text field to a date (many users are not excel savvy).
I am sure this was working earlier in the year and the only thing I can think of that has changed was a Windows Server 2008 to Windows Server 2019 upgrade.
The SQl I am using to output a date (course completion date in this case) that works fine when displayed as a HTML table is:
CONVERT(VARCHAR(10), DATEADD(second, prefix_course_completions.timecompleted-DATEDIFF(second,GETDATE(),GETUTCDATE()), CONVERT(DATETIME, '1970-01-01', 103)), 103) AS Completed
My SQL is very basic -would a different way of converting/formatting the date in the SQL produce a field excel displays as a date when exported or is this an issues with the download process?
Also - is it possible to do any of the following:
- limit or paginate displayed rows while allowing download of all rows (some of our reports can be large)
- use the same parameter field in multiple places in the where clause (e.g. where fielda = :parameter1 or fieldb= :parameter1) - I get an error: Incorrect number of query parameters. Expected 3, got 2.
- or the above with LIKE and the % in the SQL instead of the end user entering it in the parameter field?
Thank you
Phil
1) Moodle 3.11 (Build: 20210517), running on a Linux Server.
2) Ad-hoc database queries, report_customsql4.0 for Moodle 3.9+, 20201218
Any suggestions on how to fix this are appreciated.
Thank you
Thankyou very much for creating such a helpful plugin. I was using this first time in my moodle instance and wanted the query results to go to my email. I created a scheduled export and set up my email addresses but I did not get any email in my mail box ( I setup 2 emails ).. and also I setup the path for export in. "Export csv report to path / directory" but there is no file coming there for scheduled export. here is my cron job details:
Execute scheduled task: Ad-hoc database queries: run scheduled reports task (report_customsql\task\run_reports)
... started 12:10:02. Current memory use 19.9MB.
... Looking for old temp CSV files to delete.
... used 3 dbqueries
... used 0.0043940544128418 seconds
Scheduled task complete: Ad-hoc database queries: run scheduled reports task (report_customsql\task\run_reports)
I am not sure what I am doing wrong.. Can you please guide?
Thanks
I scheduled a report for 8 pm after I made the change in the database ('2 to 2). It looks like the report has not been picked up after all.
Execute scheduled task: Ad-hoc database queries: run scheduled reports task (report_customsql \ task \ run_reports)
... started 20:10:01. Current memory use 4.5MB.
... Looking for old temp CSV files to delete.
... used 3 dbqueries
... used 0.0085630416870117 seconds
Scheduled task complete: Ad-hoc database queries: run scheduled reports task (report_customsql \ task \ run_reports)
Does the plugin by any chance check whether there has already been a run on this day?
Regards,
Peter
Ultimately, I need to push a bug fix for this to the plugins DB.
Thanks for your reply.
email to column: ',2'. 2 is the userid of this user
in the logs there is 1 error:
Execute scheduled task: Ad-hoc database queries: run scheduled reports task (report_customsql\task\run_reports)
... started 11:10:01. Current memory use 4.5MB.
... Looking for old temp CSV files to delete.
... Running report tessie
... REPORT FAILED Invalid user
... used 8 dbqueries
... used 0.063519954681396 seconds
Scheduled task complete: Ad-hoc database queries: run scheduled reports task (report_customsql\task\run_reports)
Kind regards,
Peter
Also, look under Admin -> Server -> Tasks -> Scheduled tasks, and look at the logs for this report's task. Are there any errors being reported there.
How was this problem resolved in the end? I am using Moodle 3.10.3+ with report_customsql 4.0 for Moodle 3.9+. Here the situation is the same as the one Patrick describes. I can make reports on demand, the plugin has an entry in the table mdl_message_providers and the notification settings and preferences are fine.
Still, the reports are not created according to the loggings.