Differences

This shows you the differences between two versions of the page.

--- blockdashboardconfigquery [2015/11/19 10:33]
admin [Introduction des filtres]
+++ blockdashboardconfigquery [2024/04/04 15:50] (current)
@@ Line 1: / Line 1: @@
-===== block Dashboard : Elément de tableau de bord - configuration de la requête =====
+===== Query =====
+===== Dashboard block =====
-La requête :
+this panel allows you to enter the query body and make some global choices on the dashbaord behaviour.
-   * doit présenter des champs de sortie nommés par des clauses AS (même si les noms de colonnes sont triviaux)
+{{:blocks:dashboard:querydescription_en.jpg|}}
-   * peut présenter des jointures
-   * peut utiliser des fonctions d\'aggrégation et des clauses GROUP BY
-   * ne doit pas présenter de clauses ORDER BY si les données sont destinées à un affichage en table de données
-==== Introduction des filtres ====
+==== Data source ====
-Si des filtres doivent être définis sur les données, alors il est nécessaire d'insérer une balise <%%%%FILTERS%%%%> dans la requête à la place ou pour compléter une clause WHERE.
+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.
+==== Display configuration ====
+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
+==== Query input ====
+Here you can enter the full body of the SQL query, according to some writing rules:
+=== Mandatory 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.
+=== Additional rules ===
+   * 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.
+==== Output result rotation (Pro option only) ====
+{{:blocks:dashboard:queryrotate_en.jpg|}}
+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.
+=== Example ===
+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:
+^ pkey ^ cn ^ auth ^ num ^
+| FR-manual | FR | manual | 45 |
+| FR-mnet | FR | mnet | 4 |
+| FR-cas | FR | cas | 1754 |
+| EN-manual | EN | manual | 22 |
+| EN-mnet | EN | mnet | 23 |
+| EN-cas | EN | cas | 98 |
+| CH-manual | CH | manual | 12 |
+| CH-mnet | CH | mnet | 5 |
+| CH-cas | CH | cas | 302 |
+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:
+^ pkey ^ auth ^ FR ^ EN ^ CH ^
+| manual | manual | 45 | 22 | 12 |
+| mnet | mnet | 4 | 23 | 5 |
+| cas | cas | 1754 | 98 | 302 |
+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.
+<html><!-- nomoodle --></html>
+-----
+====Credits====
+  * Valéry Frémaux (valery@activeprolearn.com)- Main design and development
+  * Florence Labord (florence@activeprolearn.com) - Documentation
+[[:blocks:dashboard:userguide|Return to the configuraiton guide index]] - [[:Blocks:Dashboard|Return to the component index]] - [[:Blocks:Dashboard:QueryCatalogue|Generic query catalog]] - [[:Plugins|Return to the plugins index]] - [[:start|Home]]
+<html><!-- /nomoodle --></html>
-[[BlockDashboardUse|Revenir à l'index du guide d'utilisation]]

Moodle ActiveProLearn Documentation

User Tools

Site Tools

Differences

Page Tools