How to Build Excel Report Templates with Named Ranges

Overview

The Excel Reports module fills an Excel workbook you designed with live and historical Mango by Radix IoT data. You mark the cells with named ranges, then tell Mango which data point belongs in which named range. The name itself is yours to choose — Mango attaches no meaning to it, so the mapping is done explicitly in the report template configuration, not by encoding XIDs into range names.

Prerequisites

• The Excel Reports module installed and licensed
• Excel (or any editor that writes real named ranges) and the data points you want to report on
• Permission to create report templates, and the roles you want the report to run with

How Excel Reports Work

An Excel report is two halves that have to agree:

1. The workbook (.xlsx, .xlsm or .xls) with your layout, formatting, formulas and charts, plus named ranges marking every cell Mango should write to.
2. The report template configuration in Mango, which defines one or more time periods and, inside each, maps a data point to a named range.

When the report runs, Mango queries each point over its time period, writes the values into the cells behind the named ranges, resizes the multi-cell ranges to fit the data, recalculates, and stores a finished workbook you can download or email.

Naming Named Ranges

Pick a base name per point per time period and use it consistently. Mango treats it as nothing more than a variable name, so descriptive names such as last_month_voltage are for your benefit, not Mango's. Appending a dot suffix to that base name gives you a single cell of derived data:

last_month_voltage          the point's values (a column or row of cells)
last_month_voltage.average  the statistical average (one cell)
voltage.deviceName          the point's device name (one cell)

Single-Cell Suffixes

Note the exact spellings: it is .minimumValue and .maximumValue, not MIN and MAX, and .average, not AVG. A suffix Mango does not recognise is simply never filled, which looks identical to a mapping mistake.

Multi-Cell Ranges

A range spanning several cells in one column or one row receives a series rather than a single figure:

• Point values — the base name with no suffix, assigned to a point in a time period
• Timestamps — set once per time period in Location for time values, not per point
• Binary and multistate runtime tables — .starts, .runtime, .proportion and .startsAndRuntimeValue, one cell per state, laid out across a row or down a column

Behaviour That Decides Your Layout

• Ranges grow and shrink. Mango resizes a multi-cell range to the actual number of values.
• Existing cell contents survive unless there is data for that cell. A formula pre-placed in a range cell is kept when the report has nothing to put there, which is how a default or fallback formula is done.
• Formulas that reference assigned ranges expand too. A column of =last_month_voltage*last_month_current follows the data length without any configuration.
• Cell style is copied as Mango fills down a column or across a row, so format the first cell and the rest match.
• A values range need not start on the same row as its timestamps. The nth timestamp lines up with the nth cell of the range wherever it begins.

Step-by-Step: Build and Run a Template

Step 1: Design the Layout in Excel

• Add titles, headers, labels and units; format the cells that will receive data, including number and date formats
• Reserve a block of rows (or columns) for each time series, with nothing underneath it
• Add the formulas you want; those referencing named ranges will expand with the data

Step 2: Create the Named Ranges

1. Select the cell or the contiguous block of cells
2. Type the name into Excel's Name Box, at the left end of the formula bar (Excel's Formulas > Name Manager does the same job with more control)
3. Keep the scope at Workbook so Mango sees the name
4. Repeat for every value range, statistic cell, metadata cell and timestamp range

Step 3: Point Charts at the Named Ranges

Build charts on the named ranges rather than on raw cell addresses. A series bound to last_month_voltage follows the range as it expands; a series bound to a fixed address such as B11:B30 does not, and will plot only the rows the template happened to reserve.

Step 4: Create the Report Template in Mango

1. Go to Analytics > Excel reports and click New template
2. Name the template — the name is reused for every report it generates
3. Click Choose file and upload the workbook; it lands in the EXCEL_REPORT_TEMPLATES file store, and .xlsx, .xlsm and .xls are all accepted
4. Set Report execution roles, then the two permissions, Read generated report and Edit report template. Execution roles decide which points and events the report can actually see, so a report that runs with too few roles produces empty cells rather than an error
5. On the Time periods tab, click Add a time period and choose Relative or Specific (fixed From / To dates); Mango will not save a scheduled template while a period is Not specified. For Relative, the Date type is Previous (the last whole period), Past (the period ending now), Current (this period so far) or Ago (one whole period, N periods back)
6. Optionally set a Rollup type and Rollup period to condense samples into intervals, or tick Include bookend values — available only when the rollup is None — to place synthetic values at the exact period boundaries
7. Set Location for time values to the timestamp range for that period, if the layout has one
8. Open Select data points (N points currently selected) and pick each point, then set its Named range. Map the same point twice if you want both a values range and a short base name for its metadata
9. Optionally add a schedule, email recipients and a post-processing script, then save

Step 5: Run It

Use the Settings icon on the template row: Run queues it immediately with the saved settings, emailing every recipient if Email report is ticked, and Configure and run runs it once against dates you enter without changing the saved configuration. Finished runs appear under Finished / in-progress reports.

Verification

• Run the report with Configure and run over a period you know has data
• Download the output and confirm every named range filled: values, timestamps, statistics and metadata
• Check that formula columns expanded to the same length as the data, and that charts cover the whole series
• Confirm nothing below or beside a range was overwritten

Troubleshooting

Summary

Design the workbook, mark the cells with named ranges you choose, then map points to those ranges inside a time period in Analytics > Excel reports. Mango writes the data, resizes the ranges, expands referencing formulas and preserves your formatting; Excel does the rest. Keep the space below and beside every range clear, spell the dot suffixes exactly, and start from one of the shipped examples when a layout is new to you.