==== Dashboard Block / Dashboard Element ===
===== Query Description =====

This panel lets you enter the query and make certain global choices about the behaviour of the dashboard.

{{:blocks:dashboard:querydescription_fr.jpg|}}

==== Data source ====

While the usual data source is generally Moodle's own database, it is possible to use an additional source (PostgreSQL only) to display data coming from another application.

==== Display elements ====

You can choose to display or not to display: 

   * Query results formatted in tabular form
   * A graphical representation of the data
   * "Summers" on the result data

Optionally, you can choose to display, for debugging purposes:

   * The final query computed by the dashboard
   * The filter queries

==== Query ====

You will enter a query in the text editing area. You must comply with the following writing rules:

=== Mandatory rules ===

   * Output fields must all be aliased (e.g. "username as un") to guarantee the output data mapping.
   * Unions or intersections are not accepted

=== Optional rules ===

   * Moodle table names can be written {tablename} for automatic formatting of the table prefix. If curly braces are not used, the table name must be complete.
   * If you use filters, add the tag <%%FILTERS%%>:
     * To replace the whole WHERE clause if it is empty
     * At the end of the WHERE clause rules
   * If you use query parameters, add the tag <%%PARAMS%%> in the WHERE clause of the query.

==== Data pivoting ====

<html><!-- nomoodle --></html>
:!: This option is only available in the pro version
<html><!-- /nomoodle --></html>

{{:blocks:dashboard:queryrotate_fr.jpg|}}

Because an SQL query result cannot have a variable number of output columns, this data querying mode is not suited to querying matrix data. Data pivoting is a way of transforming a "flat" record output into a dynamically sized matrix. This technique is useful, for example, when you want to feed several distinct curve series into a graph from the results of a simple query. Pivoting uses a "dimension" column, called the "pivot" column, to arrange the data of the other columns in a grid that keeps the other dimensions, but whose other dimension is formed by the distinct values of the pivot.

=== Example ===

Consider a table listing the number of users per authentication method and per country, according to the query:

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

This query is supposed to output a linear table: 

^ 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 |

In order to display a bar graph on a data series per country, it is necessary, to feed the graph input, to produce a table transformed from the direct query output. The table to obtain is: 

^ pkey ^ auth ^ FR ^ EN ^ CH ^
| manual | manual | 45 | 22 | 12 |
| mnet | mnet | 4 | 23 | 5 |
| cas | cas | 1754 | 98 | 302 |

which allows us to provide series "per authentication method" on the country entries. 

We can then express the pivot method as: 

" pivot the column cn (country) around the pivot "auth" to distribute the data of the column num ". 

The pivot requires reprocessing the primary keys so that they match the new organisation of the output table. 


<html><!-- nomoodle --></html>
-----

==== Credits ====

  * Valéry Frémaux (valery@activeprolearn.com)- Development and documentation
  * Florence Labord (florence@activeprolearn.com) - documentation



[[:blocks:dashboard:userguide|Back to the configuration guide index]] - [[:Blocks:Dashboard|Back to the component index]] - [[:Blocks:Dashboard:QueryCatalogue|Go to the generic query catalog]] - [[:Plugins|Back to the plugin index]] - [[:start|Back to the catalog]]

<html><!-- /nomoodle --></html>