open.DASH can generate downloadable **Word** and **Excel** reports from your live data.
Each report is built from a **template file** you design once in Microsoft Word or Excel:
you place *placeholders* where data should appear, upload the file to the server, and the
report generator fills it in every time someone downloads the report.
- **Word documents** (`.docx`) are produced with
[xdocreport](https://github.com/opensagres/xdocreport) using the **Apache Velocity**
template language.
- **Excel workbooks** (`.xlsx`) are produced with
[JXLS](https://jxls.sourceforge.net/) using a **Velocity + JEXL** expression evaluator.
:::tip In-app cheatsheet
The reporting page has a **?** button (bottom-right) that opens the same reference as
this page, split into **Word** / **Excel** / **Data & R Reference** tabs. Use it while
you edit a template.
:::
---
## The data your template receives
Every template runs with the **same three objects** in scope, plus a couple of extras
per format. Everything you can print comes from these.
| Placeholder | What it is |
|-------------|------------|
| `R` | The report itself — a helper object with methods for formatting dates, looking up master data, embedding images, etc. See [The `R` helper](#the-r-helper). |
| `data` | Your report data as a **map keyed by the `reportid`** you assign to each item, e.g. `data['temperature']`. |
| `dataArray` | The **same** items as a list, in configured order — iterate this when you don't know the ids up front. |
**Excel only** additionally provides `sheetNames` (one name per item, for the
"one sheet per item" pattern) and `image` (the current image bytes, for `jx:image`).
### How an item is shaped
`data` / `dataArray` hold **data items in the standard open.WARE format** — the very same
structure described in **[Basic Data Format](../openware/BasicDataFormat)**. It helps to
keep that page open while you write a template; the placeholders below map directly onto
it:
| Data-format field | In a template | Meaning |
|-------------------|---------------|---------|
| `name` | `item.getName()` / `item.name` | Display name of the series. |
| `id` | `item.getId()` / `item.id` | Sensor id. |
| `valueTypes[]` | `item.getValueTypes()` / `item.valueTypes` | The **dimensions** (columns): each has `name`, `unit`, `type`. |
| `values[]` | `item.value()` | The **measurements** (rows). Each measurement has a timestamp (`getDate()`) and one entry per dimension. |
| `values[].value[i]` | `item.value().get(row).get(i).value()` | The actual value in dimension `i`. |
> **Getter shorthand:** in both Velocity and JEXL you can drop `get` and the parentheses
> for zero-argument getters — `item.getName()`, `item.name` and `$item.name` are equivalent.
---
## Word (`.docx`) templates
> **Ready-made example:** the server ships `reports/generic-docx-example.docx`, a minimal
> template that prints the title and range, embeds an overview chart image (`reportid`
> `chart`), then loops over `dataArray` into a per-item timestamp/value table. Open it in
> Word to see everything below in context.
### Expressions live in merge fields
xdocreport reads Velocity from Word **merge fields**, *not* from typed `${...}` text
(Word silently splits typed text and breaks it). Insert a field with
**Insert → Quick Parts → Field… → `MergeField`** and type the Velocity expression as the
field *name*. It then shows in the document wrapped in guillemets, e.g. `«$R.getTitle()»`.
### Printing single values
```velocity
«$R.getTitle()»
«$R.getDescription()»
«$R.formatDate($R.getStart(), "dd.MM.yyyy")»
«$data['temperature'].getName()»
«$data['temperature'].value().get(0).get(0).value()»
```
- `$data['temperature']` → the item whose `reportid` is `temperature`.
- `.value().get(0)` → its first measurement; `.get(0).value()` → that measurement's first dimension value.
### Conditionals and simple loops
Put each Velocity directive in its own merge field:
```velocity
«#foreach($item in $dataArray)»
Series: «$item.getName()»
«#end»
«#if($item.getValueTypes().size() > 1)» … «#else» … «#end»
```
### Repeating table rows
To repeat a **table row** once per element, use xdocreport's `@before-row` / `@after-row`
markers. Put `@before-row#foreach(...)` in a merge field in the **first** cell of the row,
and `@after-row#end` in the **last** cell; the cells in between hold the per-row values:
```velocity
First cell: «@before-row#foreach($value in $item.value())» «$R.formatDate($value.getDate(),"dd.MM.yyyy")»
Middle cell: «$value.get(0).value()»
Last cell: «@after-row#end»
```
### Embedding chart images
Chart images configured on the report are exposed through `R.getImages()` (a map keyed by
the image's `reportid`). Placing one takes two steps:
1. **Load it** into the report with a `#set` merge field.
2. **Insert a placeholder picture** where the chart should go, then **wrap it in a
bookmark named `image`** (select the picture → Insert → Bookmark → name it `image`).
xdocreport swaps the bookmarked placeholder for the loaded image, keeping its position.
```velocity
«#set($chartImg = $R.useImage($R.getImages()['chart']))»
… then place a picture wrapped in a bookmark named: image
```
:::warning Merge-field quoting
An expression containing a **space** must be quoted in the field
(e.g. `"#foreach($item in $dataArray)"`). Avoid an expression with *both* a space and its
own `"` — Word nests the quotes and breaks the field. For date patterns, drop the space or
split it: `formatDate(…,"dd.MM.yyyy")` + `formatDate(…,"HH:mm:ss")`.
:::
---
## Excel (`.xlsx`) templates
An Excel template combines two kinds of markup:
1. **Expressions** written into cells with `{{ … }}` — replaced by values.
2. **Commands** written into **cell comments** (Review → New Comment) with the `jx:`
prefix — they drive layout (loops, areas, images).
### Expressions: `{{ … }}`
The evaluator runs Velocity first, then JEXL, so you can mix two styles:
```
{{$item.name}} ← Velocity property
{{$dim.value()}} ← Velocity method
{{R.getTitle()}} ← JEXL method on R (no $)
{{R.formatDate($value.date, "dd.MM.yyyy HH:mm")}} ← mixed: $value.date + R.formatDate
{{R.value('temperature','0','0')}} ← JEXL, string arguments
```
Use `$name.property` for plain property/variable access, and call helper methods on `R`
**without** a `
. Numeric results land in the cell as real numbers you can chart or sum.
### Commands: `jx:` in cell comments
Each command sits on the **top-left cell** of the range it controls and declares its
extent with `lastCell`.
| Command | Purpose |
|---------|---------|
| `jx:area(lastCell="…")` | Marks the root template area. Exactly one, on the very first cell. |
| `jx:each(items="…" var="…" lastCell="…")` | Repeat the range once per element. `direction="RIGHT"` grows sideways; `multisheet="sheetNames"` makes one worksheet per element. |
| `jx:if(condition="…" lastCell="…")` | Render the range only if the condition holds. |
| `jx:image(src="…" lastCell="…")` | Insert an image from a `byte[]` variable (e.g. `src="image"`). |
### Worked example (the shipped `export.xlsx`)
Produces **one sheet per item**, a **column per dimension**, a **row per timestamp** —
a 2×2 block (`A1:B2`) with four nested commands:
```
Cells:
A B
1 Datum {{$type.name}}
2 {{R.formatDate($value.date,"dd.MM.YYYY HH:mm:ss")}} {{$dim.value()}}
Comments (JXLS commands):
A1: jx:area(lastCell="B2")
jx:each(items="dataArray" var="item" multisheet="sheetNames" lastCell="B2")
B1: jx:each(items="item.valueTypes" var="type" direction="RIGHT" lastCell="B1")
A2: jx:each(items="item.value()" var="value" lastCell="B2")
B2: jx:each(items="value" var="dim" direction="RIGHT" lastCell="B2")
```
1. `jx:area` marks the template root.
2. `jx:each … multisheet="sheetNames"` clones the block onto one sheet per item.
3. `jx:each items="item.valueTypes" direction="RIGHT"` spreads the header across columns — one per dimension.
4. `jx:each items="item.value()"` repeats the data row downward — one per timestamp.
5. `jx:each items="value" direction="RIGHT"` fills that row's value cells, aligned under their headers.
### Embedding images
```
1. Load the bytes in a cell: {{R.useImage(R.getImages()['myChart'])}}
2. Comment on the target cell: jx:image(src="image" lastCell="D20" imageType="PNG")
```
---
## The `R` helper
`R` is the running report. Its methods work from both engines (`$R.method()` in Word,
`R.method()` in Excel).
| Method | Does |
|--------|------|
| `R.getTitle()` / `R.getDescription()` | Report name / description. |
| `R.getOptions()` | Raw report options (`R.getOptions().optString('key')`). |
| `R.jp(path)` | JSONPath query against the options/user, e.g. `R.jp('$.user.name')`. |
| `R.getDataEntries()` | The set of `reportid` keys in `data`. |
| `R.value(itemId, valueIndex, dimIndex)` | A single value — indexes are **strings**, e.g. `R.value('temperature','0','0')`. |
| `R.filterByTime(itemId, chronoField, value)` | Measurements whose timestamp matches, e.g. `chronoField="DAY_OF_WEEK"`, `value="1"` (Monday). |
| `R.sla(list, index, default)` | Safe list access — returns `default` instead of erroring when out of bounds. |
| `R.getMasterData(className, id)` | Looks up a related object (user, company, …) as JSON. |
| `R.formatDate(millisOrString, pattern)` | Formats a timestamp with a `SimpleDateFormat` pattern. |
| `R.localeNumber(number, langTag)` | *(Excel)* Locale-formatted number, e.g. `R.localeNumber(12345.6,'de-DE')`. |
| `R.getImages()` / `R.useImage(base64)` | The report images (base64, keyed by `reportid`) / set the current image. |
| `R.cell(ref)` | *(Excel)* Read an already-filled cell, e.g. `R.cell('Sheet1!B2')`. |
| `R.getStart()` / `R.getEnd()` / `R.getReference()` | The report's selected time range / reference. |
---
## Wiring a template to a report
The report **type** picks the generator (`docx` → Word, `xlsx` → Excel), and the
`template` option picks the file — resolved relative to the server's **`reports/`**
directory, or a full `http(s)://…` URL. Each configured item defines a data pipe (see
**[Data Transformation](../openware/DataTransformation)**) and a `reportid`:
```json
{
"name": "Monthly Temperature Report",
"template": "descriptives.docx",
"items": [
{ "reportid": "temperature", "stages": [ /* transformation pipe */ ] }
],
"images": [ /* optional: charts, keyed by reportid */ ]
}
```
- Each `items[].reportid` becomes the key in `data['…']` (and the sheet-name source) — keep it in sync with what your template references.
- `images[].reportid` are the keys of `R.getImages()`.
- The report name (spaces → `_`) becomes the download filename (`.docx` / `.xlsx`).
---
## Gotchas checklist
- **Word:** author expressions as **merge fields**, never as raw `${…}` text.
- **Word tables:** row loops need `@before-row#foreach(...)` in the first cell and `@after-row#end` in the last cell of the repeating row.
- **Word quoting:** an expression with a space must be quoted; avoid one with both a space and its own `"`.
- **Excel:** `jx:` commands go in **cell comments**, expressions in cells; every command needs a correct `lastCell`.
- **Excel:** call helper methods **without** `
(`{{R.getTitle()}}`); use `
only for property access (`{{$item.name}}`).
- **Keys must match:** `data['x']`, `items[].reportid = "x"`, and image `reportid`s all refer to the same strings.
- Indexes passed to `R.value(...)` are **strings**, not numbers.
## Related
- **[Basic Data Format](../openware/BasicDataFormat)** — the structure of the `data` / `dataArray` items your template iterates.
- **[Data Transformation](../openware/DataTransformation)** — how each report item's `stages` pipe fetches and aggregates its data.