Report functions

Every function a report can write in its expressions, family by family: what it takes, what it gives, what it does with a missing value, and an example on the sample data with the result it prints. The language itself (fields in brackets, texts in double quotes, &, the operators) is in Reports: expressions.

The expression editor: the first letters, or Ctrl+Space, list the functions with their arguments and what they do; [ lists the dataset's fields with their types; the line under the field shows the function the cursor is in, its current argument in bold. A mistake is underlined, and the message says what to write instead.

The examples' data

Every example below was rendered on the sample data of a fresh installation (the «Sample» folder), in a report in English, with these datasets:

• margins, SQL on the catalog with DuckDB: the first six months of 2025 of the datasource «Margin by Category and Month», 30 rows, five categories by six months:

  SELECT categoria, anno_mese, ricavo, costo, margine, margine_pct, ordini
  FROM "Margin by Category and Month"
  WHERE ({category} IS NULL OR categoria = {category})
    AND anno_mese BETWEEN 202501 AND 202506
  ORDER BY categoria, anno_mese
  

  
  with two calculated fields:

  Name	Expression
  Month	DATE(FLOOR([anno_mese] / 100), [anno_mese] - FLOOR([anno_mese] / 100) * 100, 1)	202503 becomes the date 1 March 2025
  MarginPct	IF([ricavo] = 0, NULL, [margine] / [ricavo])	the margin as a fraction, 0.307

• history, the same datasource with every month it has, from January 2024, in the same order, with the calculated field Month: what the comparisons with the year before read: SELECT categoria, anno_mese, ricavo FROM "Margin by Category and Month" ORDER BY categoria, anno_mese.
• order, one order of the datasource orders, placed on Saturday 1 July 2023: SELECT numero, data_ordine, canale, stato, deposito FROM orders WHERE numero = 'ORD-12902'.
• top and prices, the twenty best products and the price list, for a value from a price list.

The report has two parameters: category, a text, optional, its choices the categories (none chosen unless an example says so), and since, a date, 2025-01-01. margine_pct is a percentage as the sample flow writes it (30.75), MarginPct a fraction (0.3075).

An expression goes in a Text that starts with =, or in any expression property (a column's Value and Total, a KPI's Value…). In the tables, Gives is what the report prints when the expression has no format of its own: a whole number with no separators, other numbers with separators and up to two decimals, a date as d MMM yyyy, true and false as Yes and No, NULL as nothing.

Names are written in capitals or not: sum([ricavo]) is SUM([ricavo]). Arguments in brackets in the signatures are optional. Recipes put them together for a real report, and When an expression is wrong lists what the checks say.

Aggregates and scope

An aggregate works on a set of rows, and which rows depends on where it is written:

An aggregate cannot be written where the value of each row is wanted: a group's Group by, the Order of the rows, a matrix's One column per value of, a chart's axis and One series per value of, a sparkline's Along, the choices of a parameter. An aggregate takes no other inside it: SUM([margine]) / SUM([ricavo]), not SUM(SUM(...)). A field written where there is no current row (a group's total row, a text) is the value of the first of its rows: in a text on margins, [categoria] gives Insaccati cotti.

All aggregates leave NULLs out. On no rows, or only NULLs, COUNT gives 0 and the others NULL.

A group by name

The last argument of SUM, AVG, MIN, MAX, COUNT, COUNTDISTINCT, FIRST, LAST, SUMIF and COUNTIF can name a group of the table, with the name of what it groups by: a table grouped by [categoria] has the group categoria. A group by an expression has no name until it is given one, in the group's Name in expressions: with "Q" & CEILING(MONTH([Month]) / 3) named quarter, SUM([ricavo], "quarter") is the quarter's total. On margins, grouped by [categoria] and by that quarter, sorted by [anno_mese], on the row of March 2025 of Insaccati cotti:

On a row, SUM([ricavo]) with no name is the innermost group's total, not the row's: the row's share is [ricavo] / SUM([ricavo], "categoria"), or PERCENT(SUM([ricavo]), "categoria") (PERCENT). In a total, SUM([ricavo]) / SUM([ricavo], "categoria") is that total's share: in the table above, 45.1% for the first quarter of Insaccati cotti, 54.9% for the second, 100.0% for the category.

A group has to hold the place the expression is in, or the check says so:

• a row is inside all the table's groups;
• a Total is repeated at every level with a total row: it can name the group of the outermost one and the groups around it. SUM([ricavo], "quarter") in a Total is refused while the category has a total row, since that row is outside the quarter;
• the grand total is in no group: there a total that names one prints nothing, so a share of the category can stand beside the sums in a table with a grand total. If the grand total is the table's only total, the check refuses the name;
• a group's Label sees that group and those around it;
• a matrix's cell reads the whole group, on every column: SUM([ricavo]) / SUM([ricavo], "categoria") is the cell's share of its row;
• Rows where comes before the groups: it cannot name any.

If a dataset of the report has the same name as the group, the check says that too: rename one of them, or give the group a name of its own. Two groups with the same name are told apart the same way.

SUM

SUM(value, [dataset or group]): the sum of a number over the rows. A text is refused when the report is checked: «SUM adds numbers: a field of type text is here».

AVG

AVG(value, [dataset or group]): the average of a number; NULLs are left out of the count as well.

The average of the percentages is not the margin percentage of the whole, which is SUM([margine]) / SUM([ricavo]), 24.5% (margin percentage).

MIN, MAX

MIN(value, [dataset or group]), MAX(value, [dataset or group]): the smallest and the largest value. They compare numbers, texts or dates, one type per column.

COUNT

COUNT([value], [dataset or group]): with no argument, how many rows; with a value, how many rows where it is not NULL. COUNT("margins") counts the rows of a named dataset, and COUNT("categoria") those of a group.

COUNTDISTINCT

COUNTDISTINCT(value, [dataset or group]): how many different values, NULL not counted.

FIRST, LAST

FIRST(value, [dataset or group]), LAST(value, [dataset or group]): the value of the first and of the last row that has one, in the order of the rows: the dataset's ORDER BY, or in a table its Order of the rows.

SUMIF, COUNTIF

SUMIF(condition, value, [dataset or group]): the sum of the value over the rows where the condition is true. COUNTIF(condition, [dataset or group]): how many rows those are. A condition that is NULL does not count, like a false one, and neither does a NULL value. They are SUM(IF(condition, value)) and COUNT(IF(condition, 1)), in fewer words and with the same numbers. With no row that counts, COUNTIF gives 0 and SUMIF gives NULL, which prints nothing: COALESCE(SUMIF(…), 0) for a zero. On margins:

The condition is written on each row, in the dataset the aggregate reads. SUMIF([ricavo], [ricavo]) is refused, «SUMIF: the first argument is a condition», and so is SUMIF([ricavo] > 0, [categoria]), «SUMIF adds numbers».

PERCENT

PERCENT(aggregate, [group]): the aggregate as a share of the same aggregate one level up. In a row of a table, the row's share of its group (of the whole table when it has no groups); in a group's total row, the group's share of the whole table; in the grand total, 100%. With a group named, the share of that group, whatever is between: PERCENT(SUM([ricavo]), "categoria"). It takes an aggregate: PERCENT([ricavo]) is refused, «PERCENT takes an aggregate, as PERCENT(SUM(amount))». NULL when the level above adds up to zero.

On margins, a table grouped by [categoria], sorted by [anno_mese], with PERCENT(SUM([ricavo])) as a column's Value and Total, format 0.0%:

With two levels, by [categoria] and by the quarter named quarter, the same column without a name is the share of the level above, and with one the share of the category:

RUNNING

RUNNING(aggregate): the aggregate from the first row of the group to the current one, in the order the rows are shown: a running total that starts again with every group, or runs over the whole table when it has none. It takes an aggregate of the group, RUNNING(SUM([ricavo])); one of a named dataset is refused, and so is RUNNING in a calculated field, which has no rows shown to run over: write it in a column. When a reader sorts the table by another column in the viewer, the running total follows the new order.

ROWNUMBER

ROWNUMBER(): the number of the row within its group, from 1. In the first group of the table above it goes from 1 to 6, and starts from 1 again in Insaccati da cuocere. The page header and footer have no rows: there it is refused. So it is in Rows where, Group by, the Order of the rows and a matrix's One column per value of: there the rows are being chosen and put in their order, and none has its number yet.

Other rows

PREVIOUS

PREVIOUS(value): the value on the row before, in what the table shows. It starts again with every group: the first row of a group gives NULL, so January of a category is not compared with December of the one before, and a NULL in a calculation prints nothing, with no error. On margins, grouped by [categoria], sorted by [anno_mese]:

In a column's Total it takes an aggregate, and is the total of the group before at the same level, under the same parent: with the groups by month inside a category, the first month of each category has none. In the grand total it gives NULL.

What «before» means, in every case:

• Rows where applies first: a row the filter leaves out is not there, and the first row kept has nothing before it. For a January that compares with December all the same, see the month before the first.
• Order of the rows and the group's Order: the row above is the row above, and a group sorted from Z to A has the later group before it.
• A reader who sorts the table by a column in the viewer changes the order, and PREVIOUS follows it. A column that holds PREVIOUS or names a group cannot itself be used to sort: its value depends on the order, so its header offers no click.
• A closed group keeps its total and hides its rows: closing it changes no number. The total of the group after it still compares with it, and a row is only compared inside its own group.
• A table without Detail rows has its totals, and PREVIOUS works on them as above.
• The PDF, the screen, Excel and the email are one calculation: they show the same numbers. In Excel the cell holds the value, not a formula.

PREVIOUS(PREVIOUS([ricavo])) goes two rows back, and PREVIOUS(RUNNING(SUM([ricavo]))) reads the running total on the row above. In a Rule, [ricavo] < PREVIOUS([ricavo]) colours the months that fall. Along a Chart's axis, PREVIOUS(SUM([ricavo])) is the value of the category before it in the data's order, in each series; the options Sort and Top choose the categories by their values, so with them it is refused.

It is refused where there is no row before: «PREVIOUS reads the row above among those a table shows» in Rows where, Group by, the Order of the rows, a calculated field, a text, a KPI, and in a matrix, whose rows are groups across columns. An aggregate takes no PREVIOUS inside it, SUM(PREVIOUS([ricavo])); on a row an aggregate inside it is refused too, since PREVIOUS(SUM([ricavo])) would be the same number on every row of the group.

LOOKUP

LOOKUP(key, key field, result, [dataset]): the result on the rows whose key field equals the key. Without a dataset it reads the item's own. The key is an expression where LOOKUP is written; the key field and the result are read on the rows of the dataset:

• a field in the result is the one of the first row with that key, in the dataset's order, that is its SQL's ORDER BY. Write one when the keys can repeat;
• an aggregate in the result is worked out over all the rows with that key, or an expression of them: SUM([margine]) / SUM([ricavo]);
• no row with the key, or a NULL key, gives NULL. A row whose key field is NULL equals nothing.

On margins, in a KPI:

It reads the whole dataset: the Rows where of the table it is written in do not apply, so a table of 2025 finds its 2024, as SUM([ricavo], "history") would.

Two values are equal when = says they are. A number equals a number of the same value, 1 and 1.0; a date equals a date-time at its midnight; texts are compared as written, capitals and accents included. A text never equals a number: when the types of both are known the check says so, «LOOKUP compares a number with a text», and the answer is to make them one type, LOOKUP(TEXT([code]), [sku], [price], "prices"); when they are not yet known, a key of another type finds nothing.

LOOKUP can go in a calculated field, in Rows where, in Group by, in the Order of the rows and inside an aggregate, SUM([quantita] * LOOKUP([codice], [codice], [prezzo_listino], "prices")): the results are worked out once per key and joined to the rows, never row by row. What it reads is a dataset like any other: it counts towards what the report reads, it is read with the rights of the person looking at the report, and over the rows a report reads it stops the report and names the dataset.

Logic

A comparison (= <> < > <= >=) gives true, false, or NULL when either side is NULL. Values of different types are not compared: [categoria] = 2025 is refused, «Compares a text with a number: they are of different types». AND, OR and NOT join conditions, one comparison at a time. Wherever a condition decides (IF, Rows where, a rule, Shown when), NULL counts as false.

IF

IF(condition, value if true, [value if false]): one value or the other; without the third argument, NULL when the condition is not true. The first argument must be a condition: IF([ricavo], 1, 0) is refused.

IFS

IFS(condition1, value1, condition2, value2, …, [else]): the value of the first true condition. With an odd number of arguments the last is the value when none is true; otherwise that is NULL. Thresholds read from the highest down:

ISNULL

ISNULL(value): true when the value is missing.

COALESCE

COALESCE(value1, value2, …): the first value that is not missing; NULL when none is.

Maths

+ - * / work on numbers. NULL on either side gives NULL (ISNULL(NULL + 1) is Yes), and a division by zero gives NULL. A text is refused: "Total: " + [ricavo] says ««+» works on numbers, and this is a text: join texts with &». There is no ^ and no %: write POWER(x, y), and a format such as 0%.

ROUND

ROUND(number, [decimals]): rounded half away from zero, to 0 decimals by default; negative decimals round to tens, hundreds…

Round to compute; to print, a format is enough: 0.0% writes 24.5% from 0.2453.

ABS, FLOOR, CEILING

ABS(number): the number without its sign. FLOOR(number): the whole number at or below it. CEILING(number): the whole number at or above it.

POWER

POWER(number, exponent): the number raised to the exponent; NULL when the result is not a real number, or past what a number holds (about 10 to the 308th).

Text

& joins values into a text, each written as the report writes it, NULL as nothing:

The text functions take a text: a number goes through TEXT() first (LEFT(202503, 4) is refused, LEFT(TEXT(202503), 4) is 2025). A NULL argument gives NULL. Texts are compared as written: capitals count.

TEXT

TEXT(value): the value as text, as the report prints it without a format, in the report's language; NULL gives an empty text.

CONCAT

CONCAT(value1, value2, …): the values joined, as & joins them: CONCAT("Q", CEILING(5 / 3), " ", 2025) gives Q2 2025.

Dates

A date plus or minus a number moves it by days; a date minus a date is the days between them. *, / and a date plus a date are refused («use DATEADD and DATEDIFF»). A field of type number is not a date: YEAR([anno_mese]) is refused; a month code becomes a date with DATE, as the calculated field Month above does.

DATE

DATE(year, month, day): a date; NULL when it does not exist.

YEAR, MONTH, DAY, WEEKDAY

YEAR(date), MONTH(date) from 1 to 12, DAY(date) of the month, WEEKDAY(date) from 1 (Monday) to 7 (Sunday). On the dataset order, placed on Saturday 1 July 2023:

DATEADD

DATEADD(unit, count, date): the date moved by count units, "d" days, "w" weeks, "m" months or "y" years; a negative count goes back. A month or a year that lands on a day its month does not have ends on the month's last day. NULL when the count or the date is.

DATEDIFF

DATEDIFF(unit, start, end): how many whole units from start to end, negative when the end comes first. A month counts once the same day of the month is reached.

TODAY, NOW

TODAY(): today's date; NOW(): the date and time of the render; both in the report's time zone (Page setup). Rendered on 28 October 2026 at 22:03, Europe/Rome:

Report and page

PARAM

PARAM("name"): the value of a parameter, its name in quotes; NULL when an optional one has no value. A parameter with Several values gives a list, and = and <> compare a value with each of them. In an expression {category} is refused: that is how the SQL writes a parameter.

REPORT

REPORT(): the report's name as it is now, so a rename changes it on every page: UPPER(REPORT()) on «Monthly margin» gives MONTHLY MARGIN.

PAGE, PAGES

PAGE() and PAGES(): the number of the page and how many pages there are. They are known only when the pages are laid out, so they go only in a Text, joined with &: ="Page " & PAGE() & " of " & PAGES() prints Page 1 of 4 on the first of four pages. In a column, a KPI or an aggregate they are refused, and so is arithmetic on them (PAGE() + 1).

Formats

FORMAT

FORMAT(value, pattern): a number or a date as text. NULL gives an empty text; a value the pattern does not fit (a text, a date with a number's pattern) is written as with no format. The pattern is checked with the report: "[Red]#,##0" and "abc" are refused, saying why. The same patterns go in the Format of a text, a column, a chart or a KPI, which keeps the value a number (in Excel too); FORMAT() is for a value inside a text.

The language is the report's (Page setup), not the reader's. Rounding is half away from zero, on the decimal value as written: FORMAT(1.005, "0.00") gives 1.01, and FORMAT(-0.004, "0.00") gives 0.00, without a minus. Every symbol of the patterns is in Reports: formats.

Recipes

Margin percentage

As a total, the ratio of the sums, never the sum or the average of the ratios:

SUM([margine]) / SUM([ricavo])        format 0.0%

24.5% over the six months, 21.2% in the total of Prosciutti. Row by row, the calculated field MarginPct: [margine] / [ricavo] alone would also give NULL on a month with no revenue, since a division by zero is NULL; the IF says it on purpose.

Growth on the month before

A column in a table grouped by [categoria], sorted by [anno_mese]:

[ricavo] / PREVIOUS([ricavo]) - 1          format +0.0%;-0.0%;0.0%

It gives nothing in January, -2.9% in February and +39.4% in March for Insaccati cotti: the first row of each category has no month before, and the NULL prints as nothing, with no error. For the change between totals, as the Total of the column: SUM([ricavo]) / PREVIOUS(SUM([ricavo])) - 1.

Rows where comes first, so the first month the table keeps has no month before. When it should compare with the month the filter leaves out, ask the whole dataset: on history, with Rows where YEAR([Month]) = 2025 AND MONTH([Month]) <= 6, the month before is found by its key, here with the category inside it, since a month has one row per category:

[ricavo] / LOOKUP([categoria] & [Before], [categoria] & [anno_mese], [ricavo]) - 1

with the calculated field Before, YEAR(DATEADD("m", -1, [Month])) * 100 + MONTH(DATEADD("m", -1, [Month])): January of Insaccati cotti gives -44.1% on the 119,264 of December 2024.

With SQL, LAG does the same in the dataset, and the growth is then a field of the row like any other, which is the way when it has to be sorted by, filtered on or charted with a Sort, as PREVIOUS cannot be:

SELECT * FROM (
  SELECT categoria, anno_mese, ricavo, costo, margine,
         LAG(ricavo) OVER (PARTITION BY categoria ORDER BY anno_mese) AS ricavo_prev
  FROM "Margin by Category and Month") AS m
WHERE anno_mese >= 202501
ORDER BY categoria, anno_mese

and [ricavo] / [ricavo_prev] - 1 in a calculated field. LAG written outside the subquery runs after the WHERE, and the first month of the range has no month before.

For one number against another period, a KPI's Compared with needs neither: the card shows the change in percent with its arrow.

The share of a level you choose

A table grouped by [categoria] and by the quarter, "Q" & CEILING(MONTH([Month]) / 3) named quarter, sorted by [anno_mese], with the column:

Value   PERCENT(SUM([ricavo]), "categoria")       format 0.0%
Total   PERCENT(SUM([ricavo]), "categoria")

Each month and each quarter is a share of its category, not of the quarter: January 13.6%, the first quarter 45.1%, the category 100.0%, and nothing in the grand total. In the total rows SUM([ricavo]) / SUM([ricavo], "categoria") is the same number (a group by name). The share of the level just above is plain PERCENT.

The year before

The same month one year earlier, on history, with Rows whereYEAR([Month]) = 2025 AND MONTH([Month]) <= 6, grouped by [Month], without detail rows, as the Total of two columns:

LOOKUP(DATEADD("y", -1, [Month]), [Month], SUM([ricavo]))
SUM([ricavo]) / LOOKUP(DATEADD("y", -1, [Month]), [Month], SUM([ricavo])) - 1

The filter does not hide 2024 from LOOKUP, which reads the whole dataset. A month has one key here, so SUM adds its five categories. DATEADD keeps the end of the month: 29 February 2024 and one year is 28 February 2025, and the months of a code such as 202503 need no date at all, [anno_mese] - 100 is the same month a year earlier.

Category by category the key holds both, in a table grouped by [categoria]:

LOOKUP([categoria] & ([anno_mese] - 100), [categoria] & [anno_mese], [ricavo])

which for Insaccati cotti gives 62,408 in January 2025 (the row has 66,648, so +6.8%) and 54,765 in February (+18.1%). Without the category in the key the first row of that month in the dataset would answer for all of them, the one of another category.

A value from a price list

The dataset prices has one row per product and date, the latest first, so that the first row with a key is the price in force:

SELECT p.codice, l.prezzo_listino, l.valido_dal
FROM price_list l JOIN products p ON p.id = l.prodotto_id
ORDER BY p.codice, l.valido_dal DESC

In a table on top, SELECT codice, nome, ricavo, quantita FROM "Top 20 Products by Revenue":

LOOKUP([codice], [codice], [prezzo_listino], "prices")     the list price
[ricavo] / [quantita]                                      the price it was really sold at

And the whole of it, inside an aggregate, as the Total of a column: SUM([quantita] * LOOKUP([codice], [codice], [prezzo_listino], "prices")) gives 10,113,886, what the twenty products would have made at their list price, against 8,656,855.

The sum of a period

SUMIF([anno_mese] <= 202503, [ricavo])              format #,##0
SUMIF([anno_mese] <= 202503, [ricavo]) / SUM([ricavo])    format 0.0%

In a KPI on margins, 1,399,329, the first quarter of the six months. As the Total of two columns of a table grouped by [categoria], 221,552 and 45.1% for Insaccati cotti, 453,337 and 51.3% for Salami. SUM(IF([anno_mese] <= 202503, [ricavo])) gives the very same numbers.

Labels with FORMAT

A text that mixes words and numbers formats each number:

="Revenue " & FORMAT(SUM([ricavo]), "€ #,##0") & " · margin " & FORMAT(SUM([margine]) / SUM([ricavo]), "0.0%")

gives Revenue € 2,921,909 · margin 24.5%. A number joined with & and no FORMAT keeps up to two decimals (Revenue: 1,234,567.89).

The share of the category

A column with PERCENT(SUM([ricavo])) as Value and Total, format 0.0%, in a table grouped by [categoria]: each month's share of its category, and in the total row the category's share of the whole (PERCENT).

A running total

A column RUNNING(SUM([ricavo])) in a table sorted by [anno_mese] and grouped by category: the revenue from the first month of the category to this one (RUNNING). With no group, it runs over the whole table.

Thresholds

A Rule colours cells: on the column Margin %, when [MarginPct] < 0.22, red and bold. A column with a word: IFS([MarginPct] >= 0.3, "High", [MarginPct] >= 0.22, "On target", "Below target"). A text that appears only when it matters: on the dataset margins, Shown when SUM([margine]) / SUM([ricavo]) < 0.25; at 24.5% it is shown.

Dates in titles

="Monthly margin" & IF(ISNULL(PARAM("category")), "", " — " & PARAM("category"))
="From " & FORMAT(PARAM("since"), "MMMM yyyy") & " to " & FORMAT(DATEADD("d", -1, {start_of_month}), "d MMMM yyyy")

The first gives Monthly margin — Prosciutti when a category is chosen; the second, on 28 October 2026, From January 2025 to 30 September 2026.

When an expression is wrong

The editor checks while you type and underlines the part that is wrong; the server checks the whole report against the datasets' fields (Refresh fields) after a pause and at every save, which lists what it refuses with the character where the mistake is. Some of the messages:

Related

• Pixel-perfect reports: the designer, datasets and parameters, tables, charts, the viewer and the Reports tab
• Date placeholders: {today}, {start_of_month-1m} and the others
• SQL scripts: the SQL a dataset can read