Configuration

Dashboard Block

the configuration of the query and the multiple output options is the most important activity related to tis block setup. It requires:

A good knowledge of the Moodle database structure

A good knowledge of SQL and limitations

A sufficiant knowledge of what the individual data fields represent and in what context and perimeter they can be used.

If you have those prerequisites, you are ready to do a lot of things with the Dashboard block.

Remind here that the Dashboard bloc can access to any data stored into Moodle and thus it's configuration may not be allowed in an uncontrolled way.

there are 6 main steps in setting up a dashboard query:

Carefully identify in which tables and which fields are the data that represents the query

Identify the dimensions of sorting and filtering

Write and test the base query form in a DB client

Copy the query in the dashboard

configure the output (data tables or graphs)

Setup accessories, (filters, parameters, colorizers, sub-totals, summators, etc).

Think about the problem (identify the query)

A Dashboard will NOT be able to answer to any demand. Usually, the addressed question must have an answer that can be synthesized within a single unique Query, giving a single aggregated composite output. As the block post-processes the given SQL to feed the query accessories (such as filters, or params), some complex query forms may be not supported.

In general, any results that comes out from simple JOINS (INNER, OUTER or LEFT) among a set of tables will work fine. Some queries using subqueries may work, Union or intersects may work on simple output, but some accessories may crash the query if activated.

Identify dimensions for sorting or filtering

the Dashboard block is designed for administrators being able to setup a query and additional dynamic tools addressed to the dashboard end users. Filters will provide multiple derivated partial views of the same output data. Parameters also will allow to change the initial perimeter of the extracted data, such as date ranges, ou value ranges, providing a lot of possible distinct reports with only one generic query.

Usually a data query will output a set of columns (data fields), which can be classed into the following categories :

The identifier(s)

The useful data (the really observed values)

The contextual dimensions (that split the data space into local contexts)

the technical data (that helps to calculate, format, or build other final results)

Filters usually will be defined upon the contextual dimensions, while parameters may usually affect usefull or technical values.

Write and test a base query

The best practice is to use your standard DB client software to write and debug the base query. Once you know the base query form outputs the data you expect in the arrangement you expect, it will be easier to copy the base query in the Dashboard block. Note that in general, your DB client software has more query coding useful tools such as syntax debugger or optimization helpers.

Several traps to avoid

Check carefully the first output field of your query. it MUST be a primary key for your result. This is a special attention to observe when f.e. using GROUP BY clauses or using time range extractions. Think that the query will give result through the standard DB middleware library of moodle, that uses the first column as the results associative table as php output, so it needs being a unique key of your result, or you will loose data.

Use some UNION or INTERSECT in which the Dashboard SQL post processing will NOT be able to find the clauses it needs rework.

Use complex subqueries that multiplies the ORDER BY or GROUP BY clauses.

Enter the query in the Dashboard configuration

copy the query in the query text area of the dashboard is the effective first thing to do.

Switch to the course editing mode

Edit the block's settings

Expand the Dashboard settings form section

Click on the Access to dashboard settings

The first panel shows the textarea where to put the query.

Dashboard configuration screen details

Query

User params

Data output

Data matrix additional params

Data tree view additional params

Graphic output

Additional GoogleMap parameters

Additional timeline parameters

Summators and filters

Data coloring

Data export to files

Data export sheduling

Back to the plugin index