Dashboard Configuration: Raw data (litteral) rendering

Raw literal output uses direct data from query output and displays those data in textual tables.

Depending on configuration settings, several display modes are possible. Some modes will need additional settings provided in accessory form sections.

Common settings for row data output

Choosing columns as data output

The first absolute choice to execute is selecting which column from the query are to be displayed in the table. Often the raw data will only display a part of the effective resulting dataset. You will select column in order by writing here a semi-column separated list of aliases. Of course aliases names should be present in the query!

example :

Say we enter such a query:

  SELECT
  firstname as fn,
  lastname as ln,
  username as login
  ...

Following names could figure in the column selection textfield: ln; fn; login

Output fields display name

Aliased token are seldom friendly names for end-users. The texfield for column names allows to convert column identities to friendly names in effective display. Just separate displayed names by semi-column (;) and enter the same number of names for each declared column, in the same order.

With the previous example, we could have setup:

Output fields : fn;ln;login
Output field names : Firstname;Lastname;UserID

Output fields format

Formatting data directly in the SQL syntax may not be friendly for SQL readability. This setting defines some post-formatting filters you can apply on data, based on the sprintf() syntax. Using formatting codes of sprintf makes data reformatting easy, f.e. for numeric output:

   %s : string
   %d : integer (floored)
   %03d : integer with 3 digits, 0-filled (001, 002, etc.)
   %.2f : 2 digit decimals
   etc. (check the PHP sprintf documentation for the complete formatting syntax).

Result Page Size

This setting sets up a page limitation for linear tables as output.

Override Big Results security

This checkbox disables for this block instance the “Big Result” security setting (global configuration) that forces data paging for big results. This is useful for graphed output displaying a lot of data.

Query caching

Enabling query cache

When query cache is enabled, the Dashboard Block saves in a cache the query results and will use this data during the TTL.

TTL (Time to live)

TTL is set up in minutes. Caching is effective when enabled AND a TTL is set higher than 0.

Note : Outputting results in files will require the cache is enabled.

Data Cleanup (linear table output only)

If data cleaning is enabled, cells containing the same value than a superior cell will remain undisplayed, for better viewing comfort.

Sortable Table (linear table only)

If table is set to sortable, column names will enable sorting links.

Sub-totals

If some summarizers are setup (See “summarizers”), than subtotals on the specified summarizers can be obtained on value change of a given column. Having the subtotals shown in output assumes also that table is sorted using the subtotal driving column.

Output Dynamic Filters

This important setting chapter tells how to configure dynamic filter the end user will use to select what part of the resulting dataset will be displayed.

Filters are criteria definitions that will add “selectors” (select lists) above the output rendering. A filter usually is matching a single column of the initial query. All distinct values in this column from the original result are used as filtering set.

Example :

Let’s take q query:

  SELECT
  firstname as fn,
  lastname as ln,
  city as ct

  ....

and users living in PARIS, SANTA CLARA (CA) and PRAGUE.

If a filter “city as ct” is mentioned as the filter, a select will be added to the display choosing in the set: “*, PARIS, SANTA CLARA, PRAGUE”. ‘*’ stands for “unfiltered”. Choosing any value at display time will only keep corresponding data.

Filtering will generate additional SQL WHERE clauses that need to be explicitly located in the initial query:

You will use a <%%FILTERS%%> placeholder for this:

  SELECT
  firstname as fn,
  lastname as ln,
  city as ct
  FROM
  mdl_user
  <%%FILTERS%%>

Filters stakeholder will, in this case, add the whole WHERE clause. In case your query already has WHERE statements, the filters stakeholder will only add additional filtering tests:

  SELECT
  firstname as fn,
  lastname as ln,
  city as ct
  FROM
  mdl_user
  WHERE
  country = 'AU'
  <%%FILTERS%%>

The above sample prefilters users from Australia, then add what is required by filters.

What follows is NOT a valid location:

  SELECT 
  data1,data2
  FROM
  table1 t1,
  table2 t2
  GROUP BY
  data2
  <%%FILTERS%%>
  ORDER BY
  data1

As a WHERE clause cannot be run after a GROUP BY.

Filters

Filter will be defined as a semi-column (;) separated list of SQL columns definitions.

Filter definition needs the entire SQL field clause including alias. Thus in the above example, the correct expression is “city as ct” and not “city” or “ct”.

Filter names

Right as for output columns, filter criteria will have comprehensive labels for display.

You may enter a semi-column (;) separated list of labels for naming each filter, in the same order and amount than the filter definition list.

Note: There is no provision for escaping literal “;”. Thus no semi-column should be used in names or labels…

Filter Defaults

Filter add some clauses in final WHERE statement. Without any default value, filtering is disabled when Dashboard element is shown for the first time in the browsing session. This setting allows forcing choosing a value for filtering by default. The possible choice is:

• FIRST: Chooses first filtering value found in filtering set
• LAST: Chooses last filtering value found in filtering set
• <empty>: No default value, using ‘*’.

All defaults will be set as a semi-column(;) separated list,in same order and amount than filter declarations.

Example :

Filter: department as dpt; city as ct
Filter defaults: FIRST

In this example, the default for the department is ‘*'(unfiltered)

Filter Options

Some options can drastically change the behavior of the filter or affect several Dashboard Elements on the same page layout. These options are “single-letter markers” that can appear in any order:

• g: Enables global filtering. If other filters from other Dashboard Elements are using the same column definition on the page, then changing a filter value in one Dashboard will attempt to affect all other identical global filters.
• s: Force “Single”: When present, this will avoid providing the “*” (unfiltered) option in filter value set, and will force to choose a filtering value as default. This is useful for “Big Size Results” that would not be worth trying to display in full range. If no explicit setting for “Filter défaut” has been specified, then FIRST is forced.
• m: Allow “Multiple”: When present, this marker will allow choosing multiple values in the filter value set, thus allowing to define ranges or subsets. Filter select turns to multiple selection widget.
• x: “Crossover”: Usually, filtering data adds additional clauses that may need complex query cleanup and processing. In some cases, those cleanup operations may fail to produce syntax safe query, (when using with unions, subqueries, multiple nested clauses ORDER BY, etc.). This marker will disable these cleanup operations, that may in certain cases, save some situations.

Using multiple filters

Beware : when using two or more filters, each of them provides its modality list independently. Thus some filtering combination could result in possible “no values” at all.

Table type

Raw data may be displayed using several table layout, depending on data organization:

• Linear display: Data are displayed as a simple table, each record as a line, developing output fields as cells.

 

• Tabular crosstable: Data is layout in 2 dimensions as a matrix crosstable. Some extra parameters are needed to control this output mode.
• Tree-shaped display: This mode is depending on some constraints on the query itself. To use this mode, extracted data must have a hierarchical underlying organization. Such an organization would provide:
  • A principle of filiation (a record has a parent mapped by an id)
  • A node identity (a node can be identified by a value as node name)
  • A node value (or series of value) for each node in the tree.

This mode needs additional parameters to be set.