Moodle ActiveProLearn Documentation

this panel allows you to enter the query body and make some global choices on the dashbaord behaviour.

Usually the data source is the moodle database itself. By the way, there is given provision to dashboard some external data coming from an extra PostGreSQL remote database.

You may display:

• Raw table form of the query output
• Graphic representation of the query output
• some summators calculated on output data.

In some circonstances you may choose to display additionnaly:

• The final form of the query
• The filters queries that will extract the filter modalities

Here you can enter the full body of the SQL query, according to some writing rules:

• Query MUST be a select. Any other SQL keywords will be blocked.
• All output fields must be aliased (e.g.: “username as un”) to allow correct output mapping on the displays and filters.
• UNION or INTERSECT will have big chances not to work.

• Moodle tables can be written according to the moodle development standards as {tablename} to allow moodle automatically add the prefix. If no braces are used, then write the complete name of the table as it is stored into the database schema.
• If you use filters, add the <FILTERS> tag:
  • as WHERE clause replacement if the original query has no WHERE
  • at the end of the WHERE statement in other cases.
• If you use dynamic user parameters, add the <PARAMS> tag in the WHERE clause of the query.

A SQL result cannot have a variable set of output columns, by construction of the SQL engine. Thus SQL is not a straight method to query matricial data. Data result rotation is a way to transform a flat SELECT output into a data matrix with two dynamic dimensions. this feature is usefull when getting query output that provides multiple data series in one result, and you'll need to separate each serie in a graph. Rotating results will use a defined “pivot” column for defining the dynamically generated output columns. The data will be rearranged to keep the other dimensions consistant, while distributing the actual data in the final matrix.

Say you are willing to extract the numer of users by authentication method AND by country:

 SELECT
    CONCAT(country, '-', auth) as pkey,
    country as cn,
    auth as auth,
    COUNT(*) as num
 FROM
    {user}
 GROUP BY
    CONCAT(country, '-', auth)

In standard SQL this query would provide you a flat output result with one primarykey, two dimensional attributes and one data column:

Say now we would like to feed a multiple series graph that will show the country comparison grpah for each auth method. To do that, we need to transform the linear initial result into a matrix that will feed the graph series. We need to get the following form of the same data:

This will be easily converted into json structures that feed multiple series graphs.

The result rotation can be written as:

“ Rotate the column cn (country) around the “auth” column to distribute the num column ”.

The rotation will have to process and compute a new form of results primary keys to cope the new table organisation.

• Valéry Frémaux (valery@activeprolearn.com)- Main design and development
• Florence Labord (florence@activeprolearn.com) - Documentation

Return to the configuraiton guide index - Return to the component index - Generic query catalog - Return to the plugins index - Home