Friday, August 14, 2015

Moodle Configurable Reports Plugin All Variables and Filters

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:<%%

8 comments:

  1. Fantastic resource, Jonathan! Thanks for taking the time to compile this list and share it! Many variables I did not know about.
    Best regards,
    Sarah.

    ReplyDelete
  2. Thanks Sarah. This should be comprehensive as of the date of posting. I put together by searching the code.

    ReplyDelete
  3. Do you know how to filter by a custom user field?

    ReplyDelete
  4. Bachiller,
    See if the solution provided in this thread on the Moodle forums works https://moodle.org/mod/forum/discuss.php?d=226805

    ReplyDelete
  5. Hi Jonathan, This is a huge help.
    I 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

    ReplyDelete
  6. hi,
    thank 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

    ReplyDelete
  7. In the user report, do you perhaps know how to filter by dates for Acticity resources? e.g. a Quiz?

    ReplyDelete
  8. Thanks a lot, you don't know how much it helps me.

    ReplyDelete