Dashboard Configuration: Raw data (litteral) rendering

Back to index

Raw litteral output uses direct data from query output and display 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 colun from query are to be displayed in 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 dispalyed names by semi-column (;) and enter same number of names for each declaed column, in same order.

With 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 usefull 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 willuse this data during the TTL.

TTL (Time to live)

TTL is setup 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 same value than a superior cell will remain undisplayed, for better viewing confort.

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 resuting dataset will be displayed.

Filters are criteria definitions that will add "selectors" (select lists) above the output redenring. A filter usually is matching a single column of the initial query. All disticnt values in this column fromthe 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 mentionned as 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 correponding data.

Filtering will generate additional SQL WHERE clauses that need to be explicitely 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 flters 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, than adds what is required by filters.

What follows is NOT a valid lcoation:

  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 above example the correct expressionis "city as ct" and not "city" or "ct".

Filter names

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

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

Note : There is no provision for escaping litteral ";". 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 show for the first time in the browsing session. This setting allows to force chossing a value for filtering by default. 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 department is '*'(unfiltered)

Filter Options

Some options can drastically change the behaviour of the filter or affect several Dashboard Elements on a same page layout. This options are "single-letter markers" that can appear in any order:

• g : Enables global filtering. If other filter from other Dashboard Elements are using the same column definition on the page, than changing a filter value in one Dashboard will attempt to affect all other identical global filters.
• s : Force "Single" : When present, this will avoid to provide the "*" (unfiltered) option in fitler value set, and will force to choose a filtering value as default. This is usefull for "Big Size Results" that would not be worth trying to display in fill range. If no explicit setting for "Filter défaut" has been specified, then FIRST is forced.
• m : Allow "Multiple" : When present, this markr will allow choosing multiple valuesin 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" ar all.

Table type

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

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

(Original query for the sample)

• Tabular crosstable: Data is layout in 2 dimensions as a matrix crosstable. Some extra paramters are needed to control this output mode.

• Tree-shaped display: This mode is dependaing on some contraint on query itself. To use this mode, extracted data must have a hierarchical underlying organisation. Such an organisation 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 serie of value) for each node in the tree.

This mode needs additional parameters to be set.