ci-server-engineer/knowledge/manuals/IM36K10P20-01EN.md

---
manual: IM 36K10P20-01EN
title: Report Generation Language
edition: 3rd Edition, Sep. 2023
version: R1.03
status: SELECT/WHERE/ORDER/GROUP BY, COLUMN, HEADER/FOOTER, SET, IF, LOOP, cursors, SPOOL with @EXPORT, functions (GPV_*, CHAR, DECODE, SUBSTR, NUMBER, DRAW, LINE, LOGBOOK_TEXT, TO_GMT …) and dates verified in the interactive generator and in loaded reports on the test system 2026-10-01; SEND/RECEIVE, BREAK, INCLUDE, FLAG PAGE, SPV_* from docs
---

# Report Generation Language — RQL (R1.03)

RQL is the SQL-like language of REPORT Service. A report definition (`REPORT_DF.QUERY`) is a list
of RQL statements, each ending with `;`. Data comes from datasets (`IM36K10L50-01EN.md`).
REPORT Service setup: `IM36K10R40-01EN.md`; report definitions in the Engineering Module:
`IM36K10C30-01EN.md`. Section numbers refer to the manual.

## 1. Basics (§1, §2, §9.2–9.5)

- A report description: layout statements, calculations, queries, end. A **set-up description**
  (`tls\sup\rptge1.rpt` etc.) runs once when a generator starts and sets defaults for every report
  (layout, header/footer, dictionary, `LOGIN`). Output of a SELECT in it goes to the system log.
- Names: letters, digits, `_`; variables start with `@` (≤ 32 characters); report names ≤ 16.
  Reserved words (list with `SHOW RESERVED;`) can be used as field names in double quotes.
- Data types: string (`'text'`, `''` for an apostrophe, max 512 characters), integer, real
  (needs a decimal point: `12.`). **Dates are integers**: seconds since 1970 in **local time**
  (verified: `@DATE` was 10 h ahead of Unix time on a UTC+10 system; `TO_GMT()` gives Unix time).
  Date constants without quotes: `01-Oct-2026 15:00`, `12:00` (today), `12-Jan-90` (years < 70
  are 20xx).
- Dataset field types become: char → string; boolean/short/ushort/long/ulong/ubyte → integer;
  float/double → real. Hidden fields need `SET @CONCEAL 0;`.
- Errors in a statement stop that statement (some stop the report); they are written into the
  report (`RPT-E-…`, with a caret under the position in interactive mode). Fatal errors go to the
  system log.

## 2. Queries (§3, §9.12 SELECT)

```
SELECT [DISTINCT] <expr> [<alias>], … [FROM <dataset> [<alias>], …]
  [WHERE <conditions>] [ORDER BY <col|n> [ASC|DESC]] | [GROUP BY <n>… [HAVING <cond>]];
```

- Operators: `= != <> > >= < <= LIKE` (`%` any, `_` one character; change with `@WC_ANY`,
  `@WC_SINGLE`), `AND`, `OR`, parentheses. String values are case-sensitive.
- Joins: list several datasets; the first is primary; join conditions (`a.f = b.f`) come first in
  WHERE, joined by `AND`.
- `SELECT 'text', @var;` without FROM just prints (no headings).
- Group functions `AVG COUNT MAX MIN STDDEV SUM VARIANCE` (null values ignored, `DISTINCT`
  allowed) cannot be mixed with plain columns without GROUP BY and cannot be used in WHERE; put
  their result into a variable first (`SELECT SET @max MAX(item_value) FROM …;`).
- **Verified:** an alias may appear only once per SELECT (`RPT-E-ALIASSEL`); with no matching
  rows a group function prints the count 0 and blanks.
- Reading history: `FROM item_history WHERE item_name = '<item>' AND sample_time > @date - 3600`;
  add `group_name = '<group>'` to read one storage group (else rows of all groups the item is in);
  aggregates with `interpolation = '30Minutes'|'Hour'|…` and `attribute = 'Maximum'|…`
  (`IM36K10C40-01EN.md` §5). Do not use `OR` with `item_name` (DSS appendix).

## 3. Layout (§4)

| Statement / variable | Effect |
|---|---|
| `COLUMN <alias> [AT n] [AS '<format>'] [HEADING 'a|b'];` | Format of a column (`|` splits the heading) |
| `HEADER` / `FOOTER <expr> [AT n|LEFT|CENTER|RIGHT] [AS '<fmt>'] [SKIP n], …;` | Page header/footer; empty `HEADER '';` removes it |
| `BREAK <alias> [SKIP n|SKIP PAGE] [CALCULATE SUM|AVG|… <aliases>];` | Group breaks with subtotals |
| `FLAG PAGE '<file>';` | Text file printed before everything |
| `@PAGE_SIZE` (0 = no page breaks), `@WIDTH` (40–512 per R40; 40–5000 per §9.6), `@TOP_MARGIN`, `@BOTTOM_MARGIN`, `@LEFT_MARGIN`, `@RIGHT_MARGIN` | Page geometry |
| `@EXPORT 1` | Comma-separated output: only rows, strings quoted |
| `@DECIMAL 44` | Decimal comma |

Formats: numbers `9` digit, `0` leading zero, `+` sign, `.` decimal point, `EEEE` exponent;
dates `D DD DDD M MM MMM Y YY YYYY hh mm ss` (e.g. `'DD-MMM-YYYY hh:mm:ss'`); strings `A…`, `R`
right, `C` centre, `A20`; `\` copies the next character; C formats (`%5d`, `%.1f`, `%s`). An integer
with a real format prints oddly (e.g. `+99.9` on 12 → `+12.12`); formats are minimum widths.

## 4. Flow control and cursors (§5)

`IF <cond> THEN … [ELSE …] ENDIF;` (no field names in the condition), `LOOP … ENDLOOP;` with
`EXIT;` — loops only work in stored reports (verified: typed or piped into the interactive
generator they give `RPT-E-LPNOTAL, LOOP only allowed in report definition FILES and DIRECT
mode`). Cursors:

```
SET @n ''; SET @v 0.;
DECLARE @c IS SELECT name, item_value FROM item_val WHERE name LIKE 'CI-Eng-Sample.TANK1.%';
OPEN @c;
LOOP FETCH @c INTO @n, @v; IF @rqlcount = 0 THEN EXIT; ENDIF; SELECT @n, @v; ENDLOOP;
CLOSE @c; DECLARE;   /* DECLARE without arguments removes all cursors - always end with it */
```

Variables used in `FETCH … INTO` must exist first (`SET`). `@RQLCOUNT` = rows of the last
SELECT/FETCH; `@RQLERROR` counts errors. Verified in `CIENG_HTML`.

## 5. Data manipulation (§6, §9.11)

Arithmetic `+ - * /` (integer arithmetic until a real appears: `12/5+10.0` = 12, `12/5.0+10.0` =
12.4, verified), `||` concatenation, variables (`SET @v <expr>;`, type follows the value; some
system variables are read-only). Functions verified on R1.03: `GPV_VALUE(<item>, 0)` (second
argument non-zero **resets the item to 0** after reading — use 0), `GPV_STATUS` (status number,
e.g. 9), `GPV_QUALITY`, `CHAR(<n>[, '<fmt>'])`, `NUMBER('<text or date>')`, `DECODE`, `SUBSTR`,
`UPPER`, `LOWER`, `LENGTH`, `LINE(n)`, `DRAW(n, '*')`, `MONTH_DAYS`, `TO_GMT`, `LOGBOOK_TEXT('<report>')`.
Also documented: `ABS`, `ADD_MONTHS`, `ASCII_FILE` (≤ 511 characters), `BAND/BOR/BXOR/BNOT/BSHIFT`,
`CHR`, `TO_LCT`, `TO_INTEGER`, `TO_REAL`, `SPV_VALUE/SPV_STATUS/SPV_QUALITY` (**write** an item —
only with approval).

System variables: `@DATE`/`@TIME` (now), `@GEN_TIME` (scheduled time of this generation),
`@PAGE`, `@LINE`, `@CUSTOMER`, `@VERSION` (`'1.03.00'`), `@NAME`, `@DESCRIPTION`, `@P0`–`@P7`
(ad-hoc parameters, strings, not available in the interactive generator), `@NEXT` (1 = pause each
page in interactive mode), `@WORKSPACE`, `@SORTSPACE`, `@FORMFEED`.

## 6. Output to files (§6, §9.12 SPOOL)

```
SPOOL 'C:\Users\Public\Yokogawa\tls\lst\cieng_levels.csv';
SET @EXPORT 1;
SELECT name, item_value, status FROM item_val WHERE name LIKE 'CI-Eng-Sample.TANK1.%';
SPOOL;
SET @EXPORT 0;
```

Verified: the file gets `"name",value,"status"` lines; the output also still appears in the
report. File name ≤ 79 characters, overwritten if it exists, never deleted by CI Server.
Environment variables are not expanded.

## 7. Communication and miscellaneous (§7, §8)

`SEND [<node>:]<process> [REPLY] [CODE n] [NO] PACKING <expr> AS 'I1|I2|I4|F4|F8|Ann', …;` and
`RECEIVE [FROM …] [CODE n] [NO] PACKING [TIMEOUT s] @v AS '…', …;` exchange BUS Service messages
with applications (system integrators). `DICTIONARY rpidis.'tlsdic';` (default),
`INCLUDE '<report>' | FILE '<file>';`, `LOGIN rptman[, <prio>];` (set-up files only, last
statement), `QUIT;`, `SHOW [FULL] VARS|COLUMN|CURSOR|HEADER|FOOTER|BREAKS|RESERVED|STAT|WORKSPACE|*;`.

## 8. Building queries (Appendix 1)

Field names: `dssqld -f -d <DATASET>`, the `.dss` files in `tls\src`, or *Ctrl+Space* in the
Engineering Module query editor (keywords, datasets, fields). Example in the appendix: current
alarms from `alarm_current` with a header.