How do I…?

Short answers to the questions people actually ask, each with a link to the full page. Search this page (Ctrl+F or ⌘F) for a word in your question.

Datasets

…report across a many-to-many relationship?

Two tables that both have many rows per key, e.g. customers and orders, both by state. Joining them directly multiplies every total. Group each side to one row per key, list every key once, then join both to that list.

Full answer: Case study: report across a many-to-many relationship

…get one row per value, with no total?

Click the column and set Aggregate to Group by. Every other ticked column without a total is grouped too, so you get each unique value (or combination) once.

More: 03.6 Summarise rows

…add a blank or fixed-value column, e.g. to line up a union?

+ Add formula column → Custom formula, and type the value:

• text in single quotes: 'N/A'
• a number as it is: 0
• empty: NULL

Words without quotes are read as a column name and refused.

More: 03.5 Formula columns

…add up a number that’s stored as text?

Convert it first: the column’s ⇄ → TONUMBER (it also removes spaces and Excel’s leading apostrophe, e.g. '-12.5). Then set SUM on the new column.

More: 03.5 Formula columns

…stack two tables whose columns differ?

+ Union adds a part. Tick the columns in each part, add a fixed 0 or 'N/A' where a part has no such column, then Line up columns → Auto match. UNION drops duplicate rows, UNION ALL keeps them.

More: 03.8 Stack tables

…build a dataset on another dataset?

In the dataset builder, click a dataset in the Datasets list on the left. It’s placed like a table and joins like one.

More: 03.11 A dataset built on another dataset

…filter a dataset by what the reader picks on a dashboard?

Add a dataset filter shipping_state equals, click its value box, and on the Parameter tab choose + New parameter…: name it state, give it a default. On the report or dashboard, give the state filter the Tag state. The reader’s pick goes into the dataset’s own query.

More: 03.7 Filters and parameters

…use the report’s date range inside a dataset?

Add a dataset filter on the date column, is between, then click each box and pick :start_date and :end_date on its Parameter tab. The report’s or dashboard’s date filter fills them in, and the whole last day is kept, on a date-and-time column too.

In Formula mode instead: placed_at >= :start_date AND placed_at < :end_date_next, with no quotes around the parameters.

More: 03.7 Filters and parameters

…make a report faster by filtering dates inside the dataset?

Put the date filter in the dataset: a dataset filter on the date column, is between :start_date and :end_date, picked on the value box’s Parameter tab (see the question above). The database then reads only the chosen dates from the start, and can use an index on the date column.

Without it, the report’s date filter is applied around the dataset’s query. How much that costs depends on the dataset:

• A plain dataset (rows and joins, no totals): most databases move the date condition inside on their own, so it makes little difference.
• A dataset that totals (Group by, SUM…), ranks, stacks parts, or is built on another dataset: the database usually can’t. It reads and totals every date first, then keeps the ones asked for. On a large table this is where moving the filter inside helps most.

On a dataset that totals, the date column often isn’t in its result at all; then a filter inside the dataset is the only way for the report’s date to reach it.

For tables too large even then, load them into the warehouse and build a cube (06.1 Why and when).

More: 03.7 Filters and parameters

…delete a dataset?

On the Datasets list, point at its tile and click the bin. A dataset still used by a report, dashboard, another dataset, a move or a model can’t be deleted; the message names what uses it.

More: 03.10 Preview and save

…run my own SQL query?

Data sources → SQL editor: pick a connection and database, write the query, Run (Ctrl+Enter). Read-only, one statement at a time; needs the sql:run permission (Super Admin and company admin by default).

Full answer: 02.5 Run your own SQL (SQL editor)

Reports

…refer to the current row in a formula?

Use $thisrow.column inside sumif, countif or countifunique. sumif(state == $thisrow.state, sales) is this row’s state total, on every row. The column must be in Rows.

Full answer: 04.12 Refer to the current row ($thisrow)

…show each row’s share of its group?

A UI formula sum(sales) / sumif(state == $thisrow.state, sales), with the percent format.

More: 04.12 Refer to the current row

…count a row only when its group qualifies (Excel’s SUMIFS on A2)?

Say whose rows the total covers with $thisrow: if(sumif(Document_No = $thisrow.Document_No and Entry_Type = "Consumption", Quantity) > 0 and Entry_Type = "Output", Quantity, 0). In a dataset, tick only Document_No and give the column Aggregate SUM. Don’t add Entry_Type to the grouping: that makes every answer 0.

Full answer: Case study: count a row only when its group qualifies

…put a column’s values in my own order, not A→Z?

Make a sort order on the Sort orders page (e.g. Int Generation, Consumption), then pick it under Sort order on the column in the report.

More: 04.11 Sort orders

…sort month names as months?

Nothing to do: a column holding only month names (January… or Jan…) sorts as months on its own. Weekdays and quarters too. Choose Plain A→Z on the column to turn it off.

More: 04.11 Sort orders

…add subtotals and a grand total?

Click a field in Rows → Sub-total; Grand total is the switch beside the pivot boxes.

More: 04.4 Group, total and sort

…give a row a name that survives a refresh?

Right-click a value in a grouping column → Tag…. A tag follows the row’s values, not its position.

More: 04.9 Row tags

…subtract one tagged row from another (A1 − B1)?

Not available yet. Tags name rows, but formulas can’t refer to them by name. $thisrow (04.12) covers comparisons within a group.

…see the data on a small laptop screen?

Click ▸ Pivot settings above the pivot boxes to fold them to one line; the grid moves up. The page scrolls when the window is short, so nothing is cut off.

More: 04.5 Pivot, top N and more

Can’t find your question?

The user guide is organised by task, and the case studies work through bigger questions on real data.

Last updated 15 October 2026