Report

The QFQ extension is activated through tt-content records. One or more tt-content records per page are necessary to render forms and reports.

QFQ content element

QFQ is used by configuring Typo3 content elements. Insert one or more QFQ content elements on a Typo3 page. Specify column and language per content record as wished.

The title of the QFQ content element will not be rendered. It’s only visible in the backend for orientation.

QFQ Keywords (Bodytext)

Name Explanation
form Formname defined in ttcontent record bodytext * Fix. E.g.: form = person * by SIP: form = {{form}} * by SQL: form = {{SELECT c.form FROM conference AS c WHERE c.id={{a:C}} }}
r <record id> The form will load the record with the specified id * Variants: r = 123, by SQL: r = {{SELECT ...}} * If not specified, the default is ‘0’
<level>.db Select a DB. Only necessary if a different than the standard DB should be used.
<level>.fbeg Start token for every field (=column)
<level>.fend End token for every field (=column)
<level>.head Start token for whole <level>
<level>.tail End token for whole <level>
<level>.rbeg Start token for row.
<level>.rbgd Alternating (per row) token
<level>.rend End token for row. Will be rendered before subsequent levels are processed
<level>.renr End token for row. Will be rendered after subsequent levels are processed
<level>.rsep Seperator token between rows
<level>.fsep Seperator token between fields (=columns)
<level>.sql SQL Query
<level>.althead If <level>.sql is empty, these token will be rendered
debugShowBodyText If =‘1’ and config.qfq.ini:showDebugInfo=yes - shows a tooltip with bodytext

General

To display a report on any given TYPO3 page, create a content element of type ‘QFQ Element’ (plugin) on that page.

A simple example

Assume that the database has a table person with columns firstName and lastName. To create a simple list of all persons, we can do the following:

10.sql = SELECT id AS pId, CONCAT(firstName, " ", lastName, " ") AS name FROM person

10 Stands for a root level of the report (see section Structure). 10.sql defines a SQL query for this specific level. When the query is executed it will return a result having one single column name containing first and last name separated by a space character.

The HTML output displayed on the page resulting from only this definition could look as follows:

John DoeJane MillerFrank Star

I.e., QFQ will simply output the content of the SQL result row after row for each single level.

However, we can modify (wrap) the output by setting the values of various keys for each level: 10.rsep=<br/> for example tells QFQ to seperate the rows of the result by a HTML-line break. The final result in this case is:

10.sql = SELECT id AS personId, CONCAT(firstName, " ", lastName, " ") AS name FROM person
10.sep = <br>

HTML output:

John Doe<br>Jane Miller<br>Frank Star

Syntax

All root level queries will be fired in the order specified by ‘level’ (Integer value).

For each row of a query (this means all queries), all subqueries will be fired once.

  • E.g. if the outer query selects 5 rows, and a nested query always select 3 rows, than the total number of rows are 5 x 3 = 15 rows.

There is a set of variables that will get replaced before the SQL-Query gets executed:

Column values of the recent rows: {{<level>.<columnname>}}

Global variables: {{global.<name>}}

Variables from specific stores: {{<name>[:<store/s>[:<sanitize class>]]}}

Current row index: {{<level>.line.count}}

Total rows (num_rows for SELECT and SHOW, affected_rows for UPDATE and INSERT): {{<level>.line.total}}

Last insert id for INSERT: {{<level>.line.insertId}}

See Variables for a full list of all available variables.

Be aware that line.count / line.total have to be known before the query is fired. E.g. 10.sql = SELECT {{10.line.count}}, ... WHERE {{10.line.count}} = ... won’t work as expected. {{10.line.count}} can’t be replaced before the query is fired, but will be replaced during processing the result!

Different types of SQL queries are possible: SELECT, INSERT, UPDATE, DELETE, SHOW

Only SELECT and SHOW queries will fire subqueries.

  • Processing of the resulting rows and columns:

  • In general, all columns of all rows will be printed out sequentially.

    On a per column base, printing of columns can be suppressed. This might be useful to select values which will be accessed later on in another query via the {{level.columnname}} variable. To suppress printing of a column, use a underscore as column name prefix.

    Reserved column names have a special meaning and will be processed in a special way. See Processing of columns in the SQL result for details.

    There are extensive ways to wrap columns and rows automatically. See Wrapping rows and columns: Level keys

Debug the bodytext

The parsed bodytext could be displayed by activating ‘showDebugInfo’ (Debug) and specifying

debugShowBodyText = 1

A small symbol with a tooltip will be shown, where the content record will be displayed on the webpage. Note: Debug information will only be shown with showDebugInfo=yes in config.ini .

Structure

A report can be divided into several levels. This can make report definitions more readable because it allows for splitting of otherwise excessively long SQL queries. For example, if your SQL query on the root level selects a number of person records from your person table, you can use the SQL query on the second level to look up the city where each person lives.

See the example below:

10.sql = SELECT id AS _pId, CONCAT(firstName, " ", lastName, " ") AS name FROM person
10.rsep = <br />

10.10.sql = SELECT CONCAT(postal_code, " ", city) FROM address WHERE pId = {{10.pId}}
10.10.rbeg = (
10.10.rend = )

This would result in

John Doe (3004 Bern)
Jane Miller (8008 Zürich)
Frank Star (3012 Bern)

Text across several lines

To make SQL queries, or QFQ records in general, more readable, it’s possible to split a line across several lines. Lines with keywords are on their own (QFQ Keywords (Bodytext) start a new line). If a line is not a ‘keyword’ line, it will be appended to the last keyword line. ‘Keyword’ lines are detected on:

  • <level>.<keyword> =
  • {
  • <level>[.<level] {

Example:

10.sql = SELECT 'hello world'
         FROM mastertable
10.tail = End

20.sql = SELECT 'a warm welcome'
           'some additional', 'columns'
           FROM smartTable
           WHERE id>100

20.head = <h3>
20.tail = </h3>

Nesting of levels

Levels can be nested. E.g.:

10 {
  sql = SELECT ...
  5 {
      sql = SELECT ...
      head = ...
  }
}

This is equal to:

10.sql = SELECT ...
10.5.sql = SELECT ...
10.5.head = ...

By default, curly braces ‘{}’ are used for nesting. Alternatively angle braces ‘<>’, round braces ‘()’ or square braces ‘[]’ are also possible. To define the braces to use, the first line of the bodytext has to be a comment line and the last character of that line must be one of ‘{}[]()<>’. The corresponding braces are used for that QFQ record. E.g.:

# Specific code. >
10 <
  sql = SELECT
  head = <script>
         data = [
           {
             10, 20
           }
         ]
         </script>
>

Per QFQ tt-content record, only one type of nesting braces can be used.

Be careful to:

  • write nothing else than whitespaces/newline behind an open brace

  • the closing brace has to be alone on a line.

    10.sql = SELECT 'hello world'

20 {
      sql = SELECT 'a new query'
      head = <h1>
      tail = </h1>
}

30 {
      sql = SELECT 'a third query'
      head = <h1>
      tail = </h1>
      40 {
            sql = SELECT 'a nested nested query'
      }
}

30.40.tail = End

50

{
       sql = SELECT 'A query with braces on their own'
}

Access to upper column values

Columns of the upper level result can be accessed via variables, eg. {{10.pId}} will be replaced by the value in the pId column.

Levels A report is divided into levels. Example 1 has 3 levels 10, 20.25, 20.25.10
Qualifier A level is divided into qualifiers 20.30.10 has 3 qualifiers 20, 30, 10
Root levels Is a level with one qualifier. E.g.: 10
Sub levels Is a level with more than one qualifier. E.g. levels 20.25 and 20.30.10
Child The level 20 has one child 20.25
Parent The level 20.25 has a parent 20
Example explanation 10 and 20 are root level and will be executed independently. 10 don’t have a sub level. 20.25 will be executed as many times as 20 has row numbers. 20.30.10 won’t be executed because there isn’t any 20.30 level

Report Example 1:

# Displays current date
10.sql = SELECT CURDATE()

# Show all students from the person table
20.sql = SELECT p.id AS pId, p.firstName, " - ", p.lastName FROM person AS p WHERE p.typ LIKE "student"

# Show all the marks from the current student ordered chronological
20.25.sql = SELECT e.mark FROM exam AS e WHERE e.pId={{20.pId}} ORDER BY e.date

# This query will never be fired, cause there is no direct parent called 20.30.
20.30.10.sql = SELECT 'never fired'

Wrapping rows and columns: Level keys

Order and nesting of queries, will be defined with a typoscript-like syntax: level.sublevel1.subsublevel2. ... Each ‘level’ directive needs a final key, e.g: 20.30.10. sql. A key sql is necessary in order to process a level. All QFQ Keywords (Bodytext).

Processing of columns in the SQL result

  • The content of all columns of all rows will be printed sequentially, without separator.
  • Rows with Special column names will be processed in a special way.

Special column names

  • Special column names always start with ‘_’.
  • Column names, which start with a ‘_’ and which are not reserved (=special column name), will not be printed. Nevertheless, access to it via the {{<level>.<column>}} variable (without ‘_’) are possible.
  • The input parameters for the processing function are stored as column values.
  • Single parameters are delimited by the ‘|’ character.
  • Parameters are identified by the function either
    • by their order
    • or by a one character qualifier followed by the ‘:’ character, placed in front of the actual parameter value.
Reserved column name Purpose
_link Easily create links with different features.
_mailto Quickly create email links. A click on the link will open the default mailer. The address is encrypted via JS against email bots.
_pageX or _PageX Shortcut version of the link interface for fast creation of internal links. The column name is composed of the string page/Page and a optional character to specify the type of the link.
_sendmail Send emails.
_exec Run batch files or executables on the webserver.
_vertical Render Text vertically. This is useful for tables with limited column width.
_img Display images.
_bullet Display a blue/gray/green/pink/red/yellow bullet. If none color specified, show nothing
_check Display a blue/gray/green/pink/red/yellow checked sign. If none color specified, show nothing
_<nonReservedName> Suppress output. Column names with leading underscore are used to select data from the database and make it available in other parts of the report without generating any output.

Render mode

Mode Both: url & text Only: url Only: text Description
0 (default) <a href=url>text</a> <a href=url>url</a>   text or image will be shown, only if there is a url, page or mailto
1 <a href=url>text</a> <a href=url>url</a> text Text or image will be shown, independet of there is a url
2 <a href=url>text</a>     no link if text is empty
3 text url text no link, only text or image
4 url url text no link, show text, if text is empty, show url
5       nothing at all

Question

Syntax

q[:<alert text>[:<level>[:<positive button text>[:<negative button text>[:<timeout>[:<flag modal>]]]]]]
  • If a user clicks on a link, an alert is shown. If the user answers the alert by clicking on the ‘positive button’, the browser opens the specified link. If the user click on the negative answer (or waits for timout), the alert is closed and the browser does nothing.
  • All parameter are optional.
  • Parameter are seperated by ‘:’
  • To use ‘:’ inside the text, the colon has to be escaped by ‘\’. E.g. ‘ok\: I understand’.
Parameter Description
Text The text shown by the alert. HTML is allowed to format the text. Any ‘:’ needs to be escaped. Default: ‘Please confirm’.
Level success, info, warning, danger
Positive button text Default: ‘Ok’
Negative button text Default: ‘Cancel’
Timeout in seconds 0: no timeout, >0: after the specified time in seconds, the alert will dissapear and behaves like ‘negative answer’
Flag modal 0: Alert behaves not modal. 1: (default) Alert behaves modal.

Examples:

SQL-Query Result
SELECT “p:form_person|q:Edit Person:warn” AS _link Shows alert with level ‘warn’
SELECT “p:form_person|q:Edit Person::I do:No way” AS _link Instead of ‘Ok’ and ‘Cancel’, the button text will be ‘I do’ and ‘No way’
SELECT “p:form_person|q:Edit Person:::10” AS _link The Alert will be shown 10 seconds
SELECT “p:form_person|q:Edit Person:::10:0” AS _link The Alert will be shown 10 seconds and is not modal.

Columns: _page[X]

The colum name is composed of the string page and a trailing character to specify the type of the link.

Syntax

SELECT "[options]" AS _page[<link type>]

with: [options] = [p:<page & param>]|[t:<text>]|[o:<tooltip>]|[q:<question parameter>]|[c:<class>]|[g:<target>]|[r:<render mode>]

<link type> = c,d,e,h,i,n,s
column name Purpose default value of question parameter Mandatory parameters
_page Internal link without a grafic empty p:<pageId>[&param]
_pagec Internal link without a grafic, with question Please confirm! p:<pageId>[&param]
_paged Internal link with delete icon (trash) Delete record ? U:form=<formname>&r=<record id> or U:table=<tablename>&r=<record id>
_pagee Internal link with edit icon (pencil) empty p:<pageId>[&param]
_pageh Internal link with help icon (question mark) empty p:<pageId>[&param]
_pagei Internal link with information icon (i) empty p:<pageId>[&param]
_pagen Internal link with new icon (sheet) empty p:<pageId>[&param]
_pages Internal link with how icon (magnifier) empty p:<pageId>[&param]
  • All parameter are optional.
  • Optional set of predefined icons.
  • Optional set of dialog boxes.
Parameter Description Default value Example
<page> TYPO3 page id or page alias. The current page: {{pageId}} 45 application application&N_param1=1045
<text> Text, wrapped by the link. If there is an icon, text will be displayed to the right of it. empty string  
<tooltip> Text to appear as a ToolTip empty string  
<question> If there is a question text given, an alert will be opened. Only if the user clicks on ‘ok’, the link will be called Expected “=” to follow “see”  
<class> CSS Class for the <a> tag The default class defined for internal links in ext_localconf.php (see ...)  
<target> Parameter for HTML ‘target=’. F.e.: Opens a new window empty P
<rendermode> Show/render a link at all or not. See render-mode 0-5    
<create sip> s   ‘s’: create a SIP

Column: _paged

These column offers a link, with a confirmation question, to delete one record (mode ‘table’) or a bunch of records (mode ‘form’). After deleting the record(s), the current page will be reloaded in the browser.

Syntax

SELECT "U:table=<tablename>&r=<record id>|q:<question>|..." AS _paged
SELECT "U:form=<formname>&r=<record id>|q:<question>|..." AS _paged

If the record to delete contains column(s), whose columnname match on %pathFileName% and such a column points to a real existing file, such a file will be deleted too. If the table contains records where the specific file is multiple times referenced, than the file is not deleted (it would break the still existing references). Multiple references are not found, if they use different colummnnames or tablenames.

Mode: table

  • table=<table name>
  • r=<record id>

Deletes the record with id ‘<record id>’ from table ‘<table name>’.

Mode: form

  • form=<form name>
  • r=<record id>

Deletes the record with id ‘<record id>’ from the table specified in form ‘<form name>’ as primary table. Additional action FormElement of type beforeDelete or afterDelete will be fired too.

Examples:

SELECT 'U:table=Person&r=123|q:Do you want delete John Doe?' AS _paged
SELECT 'U:form=person-main&r=123|q:Do you want delete John Doe?' AS _paged

Columns: _Page[X]

  • Similar to _page[X]
  • Parameter are position dependent and therefore without a qualifier!
"[<page id|alias>[&param=value&...]] | [text] | [tooltip] | [question parameter] | [class] | [target] | [render mode]" as _Pagee.

Column: _Paged

  • Similar to _paged
  • Parameter are position dependent and therefore without a qualifier!
"[table=<table name>&r-<record id>[&param=value&...] | [text] | [tooltip] | [question parameter] | [class] | [render mode]" as _Paged.
"[form=<form name>&r-<record id>[&param=value&...] | [text] | [tooltip] | [question parameter] | [class] | [render mode]" as _Paged.

Column: _vertical

Render text vertically. This is useful for tables with limited column width. The vertical rendering is achieved via CSS tranformations (rotation) defined in the style attribute of the wrapping tag. You can optionally specify the rotation angle.

Syntax

SELECT "<text>|[<angle>]|[<width>]|[<height>]|[<wrap tag>]" AS _vertical
Parameter Description Default value
<text> The string that should be rendered vertically. none
<angle> How many degrees should the text be rotated? The angle is measured clockwise from baseline of the text. 270
<width> Width (of what?). Needs to have a CSS_unit (e.g. px, em) specified. (Implemented?) 1em
<height> Height (of what?). Needs to have a CSS-unit (e.g. px, em) specified. (Implemented?) none
<wraptag> What tag should be used to wrap the vertical text? Possible options are div, span, etc. div

Minimal Example

10.sql = SELECT "Hallo" AS _vertical

Advanced Examples

10.sql = SELECT "Hallo|90" AS _vertical
20.sql = SELECT "Hallo|90|3em|7em|span" AS _vertical

Column: _mailto

Easily create Email links.

Syntax

SELECT "<email address>|[<link text>]" AS _mailto
Parameter Description Default value
<emailaddress> The email address where the link should point to. none
<linktext> The text that should be displayed on the website and be linked to the email address. This will typically be the name of the recipient. If this parameter is omitted, the email address will be displayed as link text. none

Minimal Example

10.sql = SELECT "john.doe@example.com" AS _mailto

Advanced Example

10.sql = SELECT "john.doe@example.com|John Doe" AS _mailto

Column: _sendmail

<TO:email[,email]>|<FROM:email>|<subject>|<body>|[<REPLY-TO:email>]|[<flag autosubmit: on /off>]|[<grid>]|[xId]|<CC:email[,email]>|<BCC:email[,email]>

Send text emails. Every mail will be logged in the table mailLog.

Syntax

SELECT "john@doe.com|jane@doe.com|Reminder tomorrow|Please dont miss the meeting tomorrow" AS _sendmail
Parameter Description Required
TO:email[,email] Comma-separated list of receiver email addresses. Optional: realname <john@doe.com> yes
FROM:email Sender of the email. Optional: ‘realname <john@doe.com>’ yes
subject Subject of the email yes
body Message yes
REPLY-TO:email Email address to reply to (if different from sender)  
flagAutoSubmit ‘on’ / ‘off’ If ‘on’ (default), add mail header ‘Auto-Submitted: auto-send’ - suppress OoO replies  
grId Will be copied to the mailLog record. Helps to setup specific logfile queries  
xId Will be copied to the mailLog record. Helps to setup specific logfile queries  
CC:email[,email] Comma-separated list of receiver email addresses. Optional: ‘realname <john@doe.com>’  
BCC:email[,email] Comma-separated list of receiver email addresses. Optional: ‘realname <john@doe.com>’  

Minimal Example

10.sql = SELECT "john.doe@example.com|company@example.com|Latest News|The new version is now available." AS _sendmail

This will send an email with subject Latest News from company@example.com to john.doe@example.com.

Advanced Examples

10.sql = SELECT "customer1@example.com,Firstname Lastname <customer2@example.com>, Firstname Lastname <customer3@example.com>|company@example.com|Latest News|The new version is now available.|sales@example.com|on|101|222|ceo@example.com|backup@example.com" AS _sendmail

This will send an email with subject Latest News from company@example.com to customer1, customer2 and customer3 by using a realname for customer2 and customer3 and suppress generating of OoO answer if any receiver is on vacation. Additional the CEO as well as backup will receive the mail via CC and BCC.

Column: _img

Renders images. Allows to define an alternative text and a title attribute for the image. Alternative text and title text are optional.

  • If no alternative text is defined, an empty alt attribute is rendered in the img tag (since this attribute is mandatory in HTML).
  • If no title text is defined, the title attribute will not be rendered at all.

Syntax

SELECT "<path to image>|[<alt text>]|[<title text>]" AS _img
Parameter Description Default value/behaviour
<pathtoimage> The path to the image file. none
<alttext> Alternative text. Will be displayed if image can’t be loaded (alt attribute of img tag). empty string
<titletext> Text that will be set as image title in the title attribute of the img tag. no title attribute rendered

Minimal Example

10.sql = SELECT "fileadmin/img/img.jpg" AS _img

Advanced Examples

10.sql = SELECT "fileadmin/img/img.jpg|Aternative Text" AS _img            # alt="Alternative Text, no title
20.sql = SELECT "fileadmin/img/img.jpg|Aternative Text|" AS _img           # alt="Alternative Text, no title
30.sql = SELECT "fileadmin/img/img.jpg|Aternative Text|Title Text" AS _img # alt="Alternative Text, title="Title Text"
40.sql = SELECT "fileadmin/img/img.jpg|Alternative Text" AS _img           # alt="Alternative Text", no title
50.sql = SELECT "fileadmin/img/img.jpg" AS _img                            # empty alt, no title
60.sql = SELECT "fileadmin/img/img.jpg|" AS _img                           # empty alt, no title
70.sql = SELECT "fileadmin/img/img.jpg||Title Text" AS _img                # empty alt, title="Title Text"
80.sql = SELECT "fileadmin/img/img.jpg||" AS _img                          # empty alt, no title

Column: _exec

Runs batch files or executables on the webserver. In case of an error, returncode and errormessage will be returned.

Syntax

<command>
Parameter Description Default value
<command> The command that should be executed on the server. none

Minimal Examples

10.sql = SELECT "ls -s" AS _exec
20.sql = SELECT "./batchfile.sh" AS _exec

Column: _F

Challenge 1

Due to the limitations of MySQL, reserved column names can’t be further concatenated. Assume you want to display an image:

# This is valid:
10.sql = SELECT concat("/static/directory/", p.foto) AS _img FROM person AS p WHERE ...

# Returns:
<img src=...>

Now assume you want to wrap the image in a div tag:

# This is valid:
10.sql = SELECT "<div>", CONCAT("/static/directory/", p.foto) AS _img, "</div>" FROM person AS p WHERE ...

# Returns:
<div><img src=...></div>

The example above works fine - however, as soon as you want to use field wrappers, things get messy:

# This is valid:
10.sql = SELECT "<div>", CONCAT("/static/directory/", p.foto) AS _img, "</div>" FROM person AS p WHERE ...
10.fbeg = <td>
10.fend = </td>

# Returns:
<td><div></td><td><img src=...></td><td></div></td>

To achieve the desired result, one might want to try something like this:

# This is NOT valid:
10.sql = SELECT CONCAT("<div>", concat("/static/directory/", p.foto) AS _img, "</div>") FROM person AS p WHERE ...
10.fbeg = <td>
10.fend = </td>

# Returns a MySQL error because nesting concat() -functions is not allowed

Challenge 2

Assume you have multiple columns with reserved names in the same query and want to use one of them in a later query:

10.sql = SELECT CONCAT("/static/directory/", g.picture) AS _img, CONCAT("/static/preview/", g.thumbnail) AS _img FROM gallery AS g WHERE ...

20.sql = SELECT "{{10.img}}", d.text FROM description AS d ...

The example above will fail because there are two img columns which can not be distinguished.

Solution

The reserved column ‘F’(=Format) can be used to

  • further wrap columns with a reserved name
  • assign an arbitrary name to a column built through a reserved name to make it accessible in later queries.

Solution for #Challenge_1:

10.sql = SELECT CONCAT("Q:img|T:div") AS wrappedImg FROM person AS p WHERE ...
10.fbeg = <td>
10.fend = </td>

# Returns:
<td><div><img src=...></div></td>

Solution for #Challenge_2:

10.sql = SELECT CONCAT("Q:img|V:mypic") AS wrappedImg FROM person AS p WHERE ...

20.sql = SELECT "{{10.mypic}}" ...
Parameter Description Required
Q Any of the reserved column names  
Z Process the column but don’t display it  
X Strip tags / Remove all tags  
T Wrap the column with the defined tag. F.e.: T:tdcolspan=”2”  
V Define an unambiguous variable name for this colum. F.e.: V:someName  
Add all the parameters required for the column defined with Q:  

QFQ CSS Classes

  • qfq-table-50, qfq-table-80 - release the default width of 100% and specify minwidth=50% resp. 80%.
  • Background Color: qfq-color-grey-1, qfq-color-grey-2 (table, row, cell)
  • Table: table
  • Table > hover: table-hover
  • Table > condensed: table-condensed

E.g.:

10.sql = SELECT id, name, firstName, ...
10.head = <table class='table table-condensed qfq-table-50'>

Examples

The following section gives some examples of typical reports

Basic Queries

  • One simple query
10.sql = SELECT "Hello World"

Result:

Hello World
Two simple queries
10.sql = SELECT "Hello World"
20.sql = SELECT "Say hello"
Result:
Hello WorldSay hello
Two simple queries, with break
10.sql = SELECT "Hello World<br />"
20.sql = SELECT "Say hello"
Result:
Hello World
Say hello

Accessing the database

Real data, one single column
10.sql = SELECT p.firstName FROM exp_person AS p
Result:
BillieElvisLouisDiana
Real data, two columns
10.sql = SELECT p.firstName, p.lastName FROM exp_person AS p
Result:
BillieHolidayElvisPresleyLouisArmstrongDianaRoss

The result of the SQL query is output row by row and column by column without adding any formatting information. See Formatting Examples for examples of how the output can be formatted.

Formatting Examples

Formating (i.e. wrapping of data with HTML tags etc.) can be achieved in two different ways:

One can add formatting output directly into the SQL by either putting it in a separate column of the output or by using concat to concatenate data and formatting output in a single column.

One can use ?level keys to define formatting information that will be put before/after/between all rows/columns of the actual levels result.

Two columns

# Add the formating information as a coloum
10.sql = SELECT p.firstName, " " , p.lastName, "'<br /'>" FROM exp_person AS p

Result:

Billie Holiday
Elvis Presley
Louis Armstrong
Diana Ross

One column ‘rend’

10.sql = SELECT p.name FROM exp_person AS p
10.rend = <br />

Result:

Billie Holiday
Elvis Presley
Louis Armstrong
Diana Ross

More HTML

10.sql = SELECT p.name FROM exp_person AS p
10.head = <ul>
10.tail = </ul>
10.rbeg = <li>
10.rend = </li>

Result:

o Billie Holiday
o Elvis Presley
o Louis Armstrong
o Diana Ross

The same as above, but with braces:

10 {
  sql = SELECT p.name FROM exp_person AS p
  head = <ul>
  tail = </ul>
  rbeg = <li>
  rend = </li>
}

Two queries:

10.sql = SELECT p.name FROM exp_person AS p
10.rend = <br />
20.sql = SELECT a.street FROM exp_address AS a
20.rend = <br />

Two queries: nested

# outer query
10.sql = SELECT p.name FROM exp_person AS p
10.rend = <br />

# inner query
10.10.sql = SELECT a.street FROM exp_address AS a
10.10.rend = <br />
  • For every record of ‘10’, all records of 10.10 will be printed.

Two queries: nested with variables

# outer query
10.sql = SELECT p.id, p.name FROM exp_person AS p
10.rend = <br />

# inner query
10.10.sql = SELECT a.street FROM exp_address AS a WHERE a.pId='{{10.id}}'
10.10.rend = <br />
  • For every record of ‘10’, all assigned records of 10.10 will be printed.

Two queries: nested with hidden variables in a table

10.sql = SELECT p.id AS _pId, p.name FROM exp_person AS p
10.rend = <br />

# inner query
10.10.sql = SELECT a.street FROM exp_address AS a WHERE a.pId='{{10.pId}}'
10.10.rend = <br />

Same as above, but written in the nested notation

10 {
  sql = SELECT p.id AS _pId, p.name FROM exp_person AS p
  rend = <br />

  10 {
  # inner query
    sql = SELECT a.street FROM exp_address AS a WHERE a.pId='{{10.pId}}'
    rend = <br />
  }
}
  • Columns starting with a ‘_’ won’t be printed but can be accessed as regular columns.