Skip to content
PlaidCloud

Build a Report With Custom Functions

A PlaidXL report is an ordinary Excel grid: a dimension’s nodes down the side, another dimension’s nodes across the top, and a PLAIDXL.CELL formula at each intersection that rolls up a measure from a PlaidCloud table. Because the numbers are formulas, the report recalculates against current data instead of being rebuilt every month.

rows from PLAIDXL.DRILLRegionFY2025FY2026TotalEMEAAPACAmericas=CELL(…)PLAIDXL.CELL combinesTABLE_REPR(“Sales”)MEASURE(“revenue”)MEASURE_TYPE(“sum”)DIMENSION_REPR(“Region”, row)DIMENSION_REPR(“Period”, column)→ revenue for EMEA in FY2026
Each cell takes its row’s node and its column’s node, and rolls the measure up to both.

Before You Start

Section titled “Before You Start”
  • Sign in and select the project that holds your data.
  • You need a table with a numeric column to report on, such as revenue, and a column for each dimension you’ll report by, such as Region and Period, linked to that dimension in PlaidCloud. CELL filters by column name, and rolls a node up through its dimension only on a linked column. See Values.
  • Not sure of the exact names? =PLAIDXL.TABLE.TBL_LIST(), =PLAIDXL.TABLE.GET_COLUMNS("Sales") and =PLAIDXL.DIMENSION.DIM_LIST() list them on a scratch sheet.

Build the Report

Section titled “Build the Report”

The steps use a table named Sales with the measure column revenue and the dimension columns Region (rows) and Period (columns), linked to dimensions of the same names. Swap in your own names — in DRILL use the dimension’s name, and in DIMENSION_REPR the table’s column name.

  1. Describe what you’re measuring. In three cells at the top of the sheet, enter:

    B1  =PLAIDXL.REPR.TABLE_REPR("Sales")
    B2  =PLAIDXL.REPR.MEASURE("revenue")
    B3  =PLAIDXL.REPR.MEASURE_TYPE("sum")

    Every value in the report refers to these cells, so changing one — "mean" instead of "sum", say — changes the whole report.

  2. Lay out the rows. In A6, drill the row dimension from its top node:

    A6  =PLAIDXL.DRILL("Region", "Total", PLAIDXL.REPR.DRILL_KEY("1 level"))

    Total and its children spill down column A, indented under their parent and grouped so you can collapse them. Use "all" for every level, or "leaves" for just the bottom level. To type the row nodes yourself instead, put one node name in each cell.

  3. Lay out the columns. Type the column nodes across row 5, starting in B5 — for example FY2025 and FY2026.

  4. Fill the first value. In B6, enter:

    =PLAIDXL.CELL($B$1, $B$2, $B$3, PLAIDXL.REPR.DIMENSION_REPR("Region", $A6), PLAIDXL.REPR.DIMENSION_REPR("Period", B$5))

    The $ signs keep the table, measure and type fixed, the row node in column A, and the column node in row 5. The indentation DRILL adds to node names is ignored.

  5. Fill the grid. Copy B6 across to your last period and down to your last row. Each cell shows revenue for its row’s region in its column’s period.

To add a filter that applies to the whole report — one product line, say — add another DIMENSION_REPR in a cell at the top, such as B4 =PLAIDXL.REPR.DIMENSION_REPR("Product", "Hardware"), and pass $B$4 to every CELL.

To show several measures for each row, use PLAIDXL.CELLS with PLAIDXL.REPR.MEASURES.

Keep the Report Current

Section titled “Keep the Report Current”
  • Force a full recalculation to pull current values into every PlaidXL formula — on Windows, press Ctrl+Alt+F9. An ordinary recalculation doesn’t fetch again unless a formula’s arguments changed.
  • Change the scope by editing a building-block cell: another table, measure or aggregation, or another top-level filter node. Formulas that use the cell update straight away.
  • Report on another project by selecting it in the task pane, then forcing a full recalculation. The formulas read from whichever project is selected, so the same workbook can report on each project that shares the table, column and dimension names.

Share a Values-Only Copy

Section titled “Share a Values-Only Copy”

People without PlaidXL, or without access to the project, see errors in place of PlaidXL formulas. To send them the numbers, replace the formulas with their values first:

  1. Save your report, then save a copy under a new name for sending.
  2. In the copy, click PlaidCloud → Sanitize Workbook.
  3. Send the copy.

Sanitize Workbook replaces every formula in the workbook with its current value. Restore Workbook puts formulas back, but it doesn’t restore formulas whose results spill, such as DRILL, cleanly, and it overwrites anything you changed after sanitizing. Keep your original — it’s the reliable way to get your live report back. See Sanitize and Restore.

Section titled “Related”