Configurable Reports is a popular plugin for Moodle that allows administrators to create custom reports for Moodle and make them available to their end users. In working with this plugin I have noticed that it can be difficult to track down all the variables and filters you can use in creating reports. Below are a list of all of the options.
Variables
%%CATEGORYID%%
%%COURSEID%%
%%CURRENTUSER%%
%%DEBUG%%
%%FILTER_VAR%%
%%STARTTIME%%','%%ENDTIME%%
%%USERID%%
%%WWWROOT%%
Filters
%%FILTER_CATEGORIES:
%%FILTER_COURSEMODULEID:
%%FILTER_COURSEMODULEFIELDS:
%%FILTER_COURSEMODULE:
%%FILTER_COURSES:
%%FILTER_COURSEENROLLEDSTUDENTS:
%%FILTER_USERS:
%%FILTER_ROLE:
%%FILTER_SEARCHTEXT:
%%FILTER_SEMESTER:
%%FILTER_STARTTIME:
%%FILTER_ENDTIME:
%%FILTER_SUBCATEGORIES:
%%FILTER_COURSEUSER:
%%FILTER_SYSTEMUSER:
%%FILTER_YEARHEBREW:
%%FILTER_YEARNUMERIC:
Using Variables
Variables can be used anywhere inside of your SQL query.
Using Filters
When using a filter you need at least one static WHERE condition then you list one or more filters. For example
WHERE 1
%%FILTER_COURSES:c.id%%
You can technically add multiple filters, but once a user applies one filter to a report all filters for report are applied. So it can be a little tricky to have multiple filters on the same report. For example if you add a course and date filter to a report, then if you just want to filter by course you have to make sure your date filters are set to date range that covers all courses.
Many of the filters are hard code to be drop down menus which are sorted by database id number instead of alphabetically. This can be problematic if you have a large number of users, courses, or categories. However, you can use one search text filter per report, which can be used to work around the issue in some cases.
Many filters require a logical operator to specify how the filter works. For example
%%FILTER_STARTTIME:l.TIME:>%% %%FILTER_ENDTIME:l.TIME:<%%
Fantastic resource, Jonathan! Thanks for taking the time to compile this list and share it! Many variables I did not know about.
ReplyDeleteBest regards,
Sarah.
Thanks Sarah. This should be comprehensive as of the date of posting. I put together by searching the code.
ReplyDeleteDo you know how to filter by a custom user field?
ReplyDeleteBachiller,
ReplyDeleteSee if the solution provided in this thread on the Moodle forums works https://moodle.org/mod/forum/discuss.php?d=226805
Hi Jonathan, This is a huge help.
ReplyDeleteI am trying to use the %%FILTER_VAR%% variable.
Using this code as a test:
SELECT *
FROM prefix_user AS u
WHERE u.id = %%FILTER_VAR%%
results in "Error reading from database"
Debug info: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 0, 5000' at line 3
SELECT *
FROM mdl_user AS u
WHERE u.id = LIMIT 0, 5000
[array (
)]
Error code: dmlreadexception
The %%FILTER_VAR%% is not being recognised and being inserted into the code. Same goes for %%CURRENTUSER%%.
Do you have any extra information on using this variable?
Kind regards
Mik
hi,
ReplyDeletethank you for your important plugin.
I'm trying to use the %%FILTER_VAR%% variable but i can't figure it out.
please can you help me
In the user report, do you perhaps know how to filter by dates for Acticity resources? e.g. a Quiz?
ReplyDeleteThanks a lot, you don't know how much it helps me.
ReplyDelete