Excel templates
An Excel report is an ordinary Excel workbook that you design, with special cells, called macros, that Velum Edge fills with live data when the report is generated. Everything else in the workbook (text, styles, charts, formulas, other sheets) is copied into the report as it is.
The quickest way to start is to download the tenant's system template, Asset discovery report, from Reports → Manage templates. It uses every macro described here. For how reports are created and collected, see Reports.
Template rules
A template is checked when you upload it, and refused unless:
- it is an Excel workbook in
.xlsxformat. Older.xlsfiles are refused; - it contains no VBA. A macro-enabled workbook (
.xlsm) is refused: save it as.xlsxinstead. (Velum Edge macros are plain cell text, not VBA.) - it is no larger than 5 MB by default; your deployment can change this limit. A template holds layout and macros, not data, so it is normally far smaller;
- every macro cell can be read: it names a macro that exists, and its arguments have the right names and types, with every required one present.
The upload check does not look up field keys, date patterns or time zones. Mistakes in those only show when a report is generated; see Troubleshooting.
Macro cell syntax
A macro is a cell whose text starts with <MACROS>, followed immediately by a
JSON object with two members:
<MACROS>{"name": "<MACRO NAME>", "args": { ... }}
nameis one ofMACROS_DATE,MACROS_ASSETS_DATAorMACROS_ASSETS_AGGREGATION_DATA, in capitals.argsholds the macro's arguments, described below. Argument names are case-sensitive and must be spelled exactly as listed; an unknown argument makes the template invalid.
When the report is generated, the macro cell is replaced by the macro's output. Macros that write more than one value fill the cells to the right of and below the macro cell. Macros are processed sheet by sheet, row by row, and the report's Progress counts them.
Excel can replace straight quotes (") with typographic ones as you type,
which is not valid JSON and makes the upload fail. Type the macro with
AutoCorrect's smart quotes turned off, or paste it in.
MACROS_DATE
Writes the date and time at which the report is generated, as text, into the macro cell.
| Argument | Required | Meaning |
|---|---|---|
format | Yes | The output pattern, in Java date-time pattern letters: yyyy year, MM month, dd day, HH hour (00-23), mm minute, ss second, MMM short month name, EEEE day name |
timezone | No | A time zone name such as UTC, Europe/London or Asia/Singapore, or an offset such as +04:00. Without it, the server's own local time zone is used |
Example:
<MACROS>{"name": "MACROS_DATE", "args": {"format": "dd MMM yyyy HH:mm", "timezone": "UTC"}}
produces text such as 16 Nov 2026 09:30.
Always set timezone. Without it the time depends on how the server running
your deployment is configured.
MACROS_ASSETS_DATA
Writes a table of assets: a header row of field names, then one row per asset with one column per field. The header lands in the macro cell's row, starting at the macro cell; asset rows follow directly below.
| Argument | Required | Meaning |
|---|---|---|
assetType | Yes | Which asset list to read: ENDPOINTS, SUBNETS, APPLICATIONS, USERS or LICENSES |
query | Yes | Which assets to include, written as you would type it into the asset list's search box (see Assets). "" includes every asset of the type |
columns | Yes | The fields to write, as a list of field keys in column order, for example ["names", "os", "private_ip"]. The header shows each field's display name |
dateFormat | No | Accepted, but currently has no effect |
timezone | No | Accepted, but currently has no effect |
Field keys are shown on Settings → Fields (see Fields). Every key must exist in the tenant.
How values are written:
- Every value is written as text, including numbers and dates. A multi-value field puts each value on its own line within the cell. An integration field shows the integration instance's name.
- Rows are written for as many assets as match; there is no row limit or sort argument.
- A cell that already exists in the template (for example because you formatted it) keeps its own formatting and receives the value. A cell that does not exist yet copies the formatting of the cell directly above it.
Lay a table out like this:
- Put the macro in the first cell of the header row.
- Format every header cell the table will use, across all its columns.
- Format the row below the header the way data rows should look. Each further row copies that formatting.
On the macro's first row, a cell that does not exist is copied from the cell above it. If that cell does not exist either (for example the macro is in row 1), generation fails. Give every header cell some formatting or text before you upload.
Example: every Windows endpoint, with five columns, on a sheet of its own:
<MACROS>{"name": "MACROS_ASSETS_DATA", "args": {"query": "os:*Windows*", "assetType": "ENDPOINTS", "columns": ["names", "os", "os_version", "private_ip", "last_seen"]}}
Build and check the query in the Endpoints list first, then copy it into the
macro. Inside the JSON, a double quote in the query must be written as \":
the search os:"Windows 11" becomes "query": "os:\"Windows 11\"".
MACROS_ASSETS_AGGREGATION_DATA
Writes the distinct values of one field, one per row, starting in the macro
cell and going down. It writes values only, not counts: add the counts with
formulas in the next columns, which the macro copies down for you (see
cloneAdditionalColumns).
| Argument | Required | Default | Meaning |
|---|---|---|---|
assetType | Yes | ENDPOINTS, SUBNETS, APPLICATIONS, USERS or LICENSES | |
query | Yes | Which assets to aggregate, as typed into the asset list's search box. "" for all assets of the type | |
aggregationBy | Yes | The field key whose values are listed, for example os_vendor | |
maxNumberOfValuer | Yes | How many values to list: the most common ones are kept. 0 or less means 20; the maximum is 500 by default (your deployment can change it). Note the spelling of the name | |
sortBy | No | BY_COUNT | BY_COUNT keeps the values in order of frequency, most common first; BY_NAME sorts them alphabetically (capital letters before lower-case ones) |
sortOrder | No | ASC | ASC keeps the order sortBy gives; DESC reverses it. So BY_COUNT with DESC lists the least common first |
cloneAdditionalColumns | No | 0 | How many columns to the right of the value column are filled down alongside each value, see below |
ignoreEmptyValues | No | false | true leaves out the empty value. It is applied after the most common values are chosen, so you may get fewer than maxNumberOfValuer rows |
How values are written:
- An integration field lists integration instance names.
- If there are no values at all, the macro cell is left empty.
- New rows copy the formatting of the row above, as for
MACROS_ASSETS_DATA.
Counting with cloneAdditionalColumns
Put a formula next to the macro, in the same row, that refers to the macro
cell. With cloneAdditionalColumns set to the number of such columns, each new
value row gets those formulas copied down from the row above, with relative
references moved down one row, as Excel's fill-down does. The formulas in the
macro's own row are kept.
For this to work, leave the cells below the macro row empty: cells that already exist there are overwritten with blanks instead of receiving the copied formula.
Example: operating system vendors with their counts. Sheet Assets holds a
MACROS_ASSETS_DATA table of endpoints whose column C is OS vendor. On a
summary sheet:
| Cell | Content |
|---|---|
| A1 | Vendor (heading) |
| B1 | Endpoints (heading) |
| A2 | <MACROS>{"name": "MACROS_ASSETS_AGGREGATION_DATA", "args": {"query": "", "assetType": "ENDPOINTS", "aggregationBy": "os_vendor", "maxNumberOfValuer": 10, "sortBy": "BY_COUNT", "cloneAdditionalColumns": 1, "ignoreEmptyValues": true}} |
| B2 | =COUNTIF(Assets!C:C, A2) |
The report lists up to ten vendors, most common first, in A2, A3, A4 and so on,
with =COUNTIF(Assets!C:C, A3), =COUNTIF(Assets!C:C, A4) and so on filled in
beside them. Because the data sheet's values are written as text, COUNTIF
compares them as text. A chart that reads A1:B11 picks up the result.
Formulas in the generated workbook are recalculated when the file is opened.
Uploading a template
On Reports, open Manage templates and select Create template:
| Field | Notes |
|---|---|
| Report type | Choose Excel report. It cannot be changed later |
| Name | Required. Characters that cannot be used in a file name are removed |
| Description | Optional |
| Upload template file | The .xlsx workbook |
Select Create. If the workbook breaks one of the template rules, the dialog shows why and nothing is saved.
You can also start from Create report: when the tenant has no Excel template to choose, the dialog offers Upload template, and returns to the report once the template is saved.
Replacing or editing a template
In the template's row menu, select Edit template. You can change the name and description, and drop a new workbook on Replace template file. Leave the file empty to keep the current one. A replacement file is checked like a new upload; if it is refused, the template stays exactly as it was. Save changes applies the edit to future reports; reports already generated are not affected.
Download template gets the current workbook back, for example to edit it in Excel. Delete template removes it permanently. The system template can only be downloaded.
Troubleshooting
| Symptom | Likely cause |
|---|---|
| Upload refused: "must be an Excel workbook (.xlsx)" | The file is .xls, .csv or not a workbook at all |
| Upload refused: "cannot contain VBA macros" | The workbook is macro-enabled; save it as .xlsx |
| Upload refused: "too large" | Over the size limit (5 MB by default) |
| Upload refused: "Cannot instantiate macros for template" | A macro cell is not valid: smart quotes, a misspelt macro or argument name, a missing required argument, or a value of the wrong type (for example "20" instead of 20) |
The report contains the <MACROS> text instead of data | A field key in columns or aggregationBy does not exist in the tenant; the macro is skipped and the rest of the report is still generated |
| The report fails | Often an invalid format pattern or timezone in MACROS_DATE, or an unformatted header row (see the warning under MACROS_ASSETS_DATA) |
| Counts next to an aggregation are blank below the first row | The cells under the macro row already existed in the template; clear them |