Ad-hoc reports not working?

Ad-hoc reports not working?

by Otaku Dude -
Number of replies: 8

Hi all,

A number of the ad-hoc contributed content queries don't seem to work. Has there been a change in the way Moodle deals with reporting? For example the 'Most Popular Activity' report returns nothing. Others throw up syntax errors.

I want to dig into Moodle a bit to see what our users are actually using it for and what type of content they engage with the most. I'd also like to find out what the most created activities by staff are too. Is this all possible? I'm afraid I know nothing about creating queries.

Thank you,

Nigel.

Average of ratings: -
In reply to Otaku Dude

Re: Ad-hoc reports not working?

by Elizabeth Dalton -

Nigel,

What version of Moodle are you using, and how are you using the reports from the had-hoc page? (I.e. are you using Ad-Hoc Queries report, Configurable Reports block, direct access to the SQL database, etc.?)

Best regards,

Elizabeth

Average of ratings: -
In reply to Elizabeth Dalton

Re: Ad-hoc reports not working?

by Otaku Dude -

Hiya,

Thank you for the response. We are using Moodle 3.6.2 and the Ad-Hoc plugin. I  copy and paste the scripts from the Ad-Hoc contributed scripts page (3.6 versions). I understand that there is an issue with the older 'log' table name and I use the Logstore-standard_log.

Thank you,

Nigel.

Average of ratings: -
In reply to Otaku Dude

Re: Ad-hoc reports not working?

by Séverin Terrier -
Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Testers Picture of Translators

Hi,

mdl_log and mdl_logstore_standard_log tables are different, and have different column names. So, queries using mdl_log will have to be adapted to use mdl_logstore_standard_log!

If people encounter problems with some queries, please provide direct links to queries involved. That will make things clear, and ease the process for other people to verify queries, and perhaps correct them if needed.

HTH,
Séverin

Average of ratings: -
In reply to Séverin Terrier

Re: Ad-hoc reports not working?

by Otaku Dude -

Hello,

Thank you for your response. As an example I have tried to use:

'Module activity (Instances and Hits) for each academic year'

When I run this I get:

Please do to include the table name prefix mdl_ in the SQL. Instead, put the un-prefixed table name inside {} characters.

Thank you

Nigel


Average of ratings: -
In reply to Otaku Dude

Re: Ad-hoc reports not working?

by Séverin Terrier -
Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Testers Picture of Translators
Hi Nigel,

In fact, some queries are very old, perhaps based on Moodle before 2.7 (and the mdl_logstore_standard_log table), and even sometimes from Moodle 1.9...

As stated in the sentence you copied, when using this plugin (and not a direct query in the database), technically you should replace all mdl_log occurrences by {log}. But this query will only work if you use legacy logs.

If you use standard logs, you'll really have to replace all mdl_log occurrences by {logstore_standard_log} but also all occurrences of l.`time` by l.`timecreated` (to adapt the query to the new table).

HTH,
Séverin
Average of ratings: -
In reply to Séverin Terrier

Re: Ad-hoc reports not working?

by Séverin Terrier -
Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Testers Picture of Translators

Hi Nigel,

I've adapted the query you pointed, so it should now work.

Certainly lots of other queries should be adapted also.

The documentation is a wiki, so anyone can update it wink

Séverin

Average of ratings: Useful (2)
In reply to Séverin Terrier

Re: Ad-hoc reports not working?

by Otaku Dude -

That's awesome Séverin, thanks for doing that.

Nige.

Average of ratings: -