===== Query ===== ===== Dashboard block ===== this panel allows you to enter the query body and make some global choices on the dashbaord behaviour. {{:blocks:dashboard:querydescription_en.jpg|}} ==== Data source ==== 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. ----- ====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]]