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:

Afin de pouvoir afficher un graphe en barres sur une série de données par pays, il est nécessaire pour alimenter l'entrée du graphe, de produire une table transformée de la sortie directe de requête. La table à obtenir est :

qui nous permette de fournir des séries “par méthode d'authentification” sur les entrées pays.

Nous pouvons exprimer alors la méthode du pivot comme :

“ pivoter la colonne cn (country) autour du pivot “auth” pour re répartir les données de la colonne num ”.

Le pivot nécessite de retraiter les clef primaires pour qu'elles correspondent à la nouvelle organisation de la table de sortie.

• Valéry Frémaux (valery@activeprolearn.com)- Développements et documentation
• Florence Labord (florence@activeprolearn.com) - documentation

Revenir à l'index du guide de configuration - Retour à l'index du composant - Aller au catalogue de requêtes génériques - Revenir à l'index des plugins - Revenir au catalogue