Charts and Pivot Tables in Custom Reports

Custom Report templates can visualise the data on your sheets with charts and pivot tables. Both are built from the spreadsheet already open in the template editor — the same data you import from data sources or files.

If you are new to Custom Reports, start with the Custom Reports guide for creating templates, importing data, and saving. This page covers charts and pivots only.

Who Can Use Charts and Pivots

Access to the Reports area requires the Reports permission (finance.reports). Anyone who can open and edit a Custom Report template can:

• Create, edit, and remove charts linked to that template
• Build live pivot sheets from a source range on the workbook
• Open the resizable Charts pane while editing

Charts and pivots respect the same private vs. team report visibility as the template itself.

Charts

Charts visualise a sheet (or a selected range on a sheet). They are stored with the report template and update live while you edit — including unsaved changes — when the Charts pane is open.

Create a Chart

1. Open a Custom Report template from Reports → Templates.
2. Select the sheet that holds the data you want to chart. Optionally select a multi-cell range (for example A1:E50); otherwise the whole used range is offered.
3. In the ribbon, click Create chart (or use the Charts entry in the Custom Reports toolbar group).
4. In the Create chart from this sheet dialog:
   • Confirm or edit the source range (A1 notation)
   • Set a name
   • Choose a chart type (see list below)
   • Pick the category (x-axis / grouping) column
   • Pick a value (measure) column and aggregation (Sum, Average, or Count), unless you use Count alone
   • For stacked bar, also pick a series column
   • For scatter, pick an X axis (numeric) and Y axis (value)
   • Optionally set a colour for single-series chart types
5. Review the live preview on the right, then click Create chart.

The chart is saved for this template and appears in the Charts pane for that sheet.

Chart Types

Table excerpts (dashboard-ready)

A table chart is not a plot — it is a standalone, read-only excerpt of the source range:

1. Choose Table (excerpt) as the chart type.
2. Tick the columns to include (order follows the sheet header order of the ones you select).
3. Optionally set a row filter: pick a column and tick the values to include (e.g. only Status = Open). Multiple values on one field are OR; omit the filter to show all rows.
4. The preview and Charts pane show a compact scrollable grid with a “Showing N of M rows” footer (row cap defaults to 25).

The definition is stored like other charts (ap_report_charts + source_ref.columns / source_ref.filters), so the same card can be embedded on dashboards in a later stage without re-picking the sheet.

Aggregation options:

• Sum — total of the measure per category
• Average — mean of the measure per category
• Count — number of rows per category (no value column required)

Numeric columns include cells that store amounts as numbers or as numeric strings (for example "45200").

Charts Pane (Live View)

1. Click Charts in the editor toolbar to open a resizable side pane next to the spreadsheet.
2. The pane shows charts for the active sheet only. Switch sheet tabs to see that sheet’s charts.
3. Each card shows the chart name, type badge, a compact plot, and actions:
   • Edit — reopen the chart dialog with the current settings
   • Remove — soft-delete the chart definition (confirm if prompted)
4. Drag the pane handle to resize. Chart plots scroll inside the pane so the sheet stays usable.

While the pane is open, charts rebuild from the current workbook, including unsaved cell edits. Save the template when you want the stored workbook and server-side chart resolution to match.

Edit or Remove a Chart

• Edit: open the Charts pane → Edit on the card, or use Create chart flow with the same dialog in edit mode. You can change type, fields, range, colour, and name.
• Remove: Charts pane → trash control on the card. The chart is removed from the template’s chart list for everyone who can open the report.

Tips for Charts

• Put a clear header row on the first row of the range (column names). Charts use those names as field options.
• Prefer importing a data source onto its own sheet, then chart that sheet so Refresh keeps both the table and the chart current after save.
• Use a tight source range when a sheet mixes raw data with notes or totals below the table.
• Stacked bar and scatter need the extra field configured; the dialog will prompt you if it is missing.

Pivot Tables

Pivot tables summarise a source range into a new sheet of live spreadsheet formulas. When the source data changes (including after a data-source refresh and save), the pivot recalculates.

How This Compares to Excel

Excel PivotTables are a special object: you refresh from the source and change field layout — you do not type into the pivot body. Assignar Pay pivots are formula-linked sheets that follow the same practical rule:

• Value cells are formulas (SUMIFS / COUNTIFS / AVERAGEIFS, with label spills via UNIQUE / SORT)
• The pivot sheet is filterable but not editable (sheet protection in filter-only mode)
• Edit the source sheet, not the pivot body, so formulas stay intact
• Header and first column are styled so the sheet is obviously a pivot
• Grand Total is always the bottom row of the pivot block as the table grows into reserved headroom

Build a Pivot

1. Open the template and select the source sheet. Optionally select a multi-cell range; otherwise the used range is prefilled.
2. In the ribbon, click Pivot.
3. In the dialog:
   • Confirm or edit the source range
   • Choose the row field (categories down the left)
   • Optionally choose a column field (cross-tab)
   • Choose the values field and aggregation (Sum, Average, Count)
   • Optionally choose a filter field and uncheck values to exclude
4. Check the preview line (approximate size of the result), then click Build live pivot.

A new sheet is created (blue tab colour) with:

• A bold header row (frozen)
• Bold, highlighted row labels in the first column
• Value formulas linked to the source range
• A Grand Total row at the bottom of the pivot block

After You Build

• Switch back to the source sheet, change a value or Refresh a connected data source, then save if needed. Reopen or recalculate so formula spills repopulate.
• Use the filter dropdowns on the pivot header row to slice the result. Cell edits on the pivot body are blocked — change the source sheet instead (or rebuild the pivot if you need a new layout).
• You can export the workbook to Excel; live formulas export with the sheet.

Pivot Layout Notes

• Row-only pivot: one column of labels and one column of aggregated values, plus Grand Total.
• Cross-tab pivot: row labels, spilled column headers, a matrix of values, a Total column, and a Grand Total row.
• Headroom rows allow new categories to appear without rebuilding immediately; Grand Total stays the last row of the reserved block.

Permissions and Storage

• Chart definitions are stored for the account and linked to the report template (and sheet / range). They require the same Reports permission as editing templates.
• Pivot tables live inside the workbook as sheets (formulas + formatting). Saving the template saves the pivot sheet with the rest of the report.
• Soft-deleted charts no longer appear in the Charts pane; list views only show active charts.

Limits and Things to Know

• Charts resolve against the sheet data; empty ranges show an empty state until headers and rows exist.
• Very large sheets may need a narrowed source range for clear charts and pivots.
• Charts update live from the unsaved workbook in the editor pane; other surfaces that load a chart without the open editor use the last saved workbook.
• Pivot sheets use Univer filter-only worksheet protection: filters work, cell edits do not. Protection is re-applied when a template with reportPivot sheets is reopened.
• A standalone chart library, dashboard pinning, and scheduled reports are not part of this stage; see later product releases for those.

Related Guides

• Custom Reports — create templates, import data sources, formulas, save and export
• Report Data Sources — the 17 sources, filters, and columns
• Reporting & Analytics — overview of reporting in Assignar Pay