Add Formula Functions Reference

Version: DashboardFox v12
Role: Composer or higher
Permission: Composer access to the application

This article documents every function available in Composer's Add Formula dialog. To open the picker, click the action menu on a field card → Add formula, then type @ in the Formula box (or click the function icon). The Select Function Type dropdown opens with five function categories — Aggregate, String, DateTime, Math, and Other — plus an Insert Report Field option for inserting alias-qualified references to other fields on the report.

The picker fills in the correct syntax for the database your app is connected to (Microsoft SQL Server, PostgreSQL, MySQL, or Oracle), so you typically don't need to remember dialect differences. This reference lists everything in the picker, what each function returns, and where dialect syntax differs across databases.

How to read the placeholders in this article

When the picker drops a function into your Formula box, the expression contains placeholders — snake_case names like decimal_places, number_of_chars, or [insert_field]. These placeholders are not valid SQL on their own. You must replace each one before saving the formula. If you don't, the database returns an error like:

Executing SQL Error: 42703: column "decimal_places" does not exist

The placeholder name itself tells you what to replace it with. The conventions:

• ? — the field this card is on. Composer pre-loads this; do not change it.

• [insert_field], [insert_end_date_field], [insert_divisor_field] — a reference to a different field on the report. Use Insert Report Field at the bottom of the function-type menu to drop the correct alias-qualified reference into your formula. Don't type the alias by hand — aliases are auto-generated and the picker emits them correctly for your database.

• decimal_places, exponent, divisor, number_of_chars, number_of_days, start_position — replace with a literal integer.

• 'text_to_find', 'text_to_replace_with', 'default_value', 'compare_value', 'matching_result', 'else_result' — replace the inner text with your literal string, keeping the single quotes.

The Data Type dropdown next to the Formula box must match what the formula returns. The full data type list is documented in Different DashboardFox Data Type Explained.

Aggregate functions

Aggregates collapse many rows into one summary value. Used in every "count by," "sum by," and "average by" report. Set Data Type to num.

Universal across all four database platforms (same expression on every database):

Per-database variants:

String functions

String manipulation. Returns cha unless noted (LENGTH and POSITION return num).

Universal across all four database platforms:

Per-database variants:

¹ MySQL's CONCAT(NULL, anything) returns NULL. The MySQL expression wraps each operand with COALESCE(operand, '') so a NULL operand becomes an empty string — matching the behavior of MSSQL, PostgreSQL, and Oracle. ² MSSQL TRIM(?) requires SQL Server 2017 or later. The picker ships the MSSQL TRIM entry as LTRIM(RTRIM(?)) by default — works on every MSSQL version. Customers on SQL Server 2017 or newer can swap the expression to the shorter TRIM(?) form if preferred.

DateTime functions

Date extraction, date arithmetic, and current-date helpers. Returns vary by function — num for numeric extractions, cha for name extractions, dat for current-date functions.

All DateTime functions have per-database variants:

³ Oracle's TO_CHAR(?, 'D') returns 1-7 but which weekday corresponds to 1 depends on the session's NLS_TERRITORY setting. Default may not be Sunday=1. Verify on your instance before relying on the numbering. ⁴ The PostgreSQL and Oracle expressions cast both operands to DATE so TIMESTAMP fields return an integer day count rather than an INTERVAL. Use Insert Report Field to fill in the [insert_end_date_field] reference.

Math functions

Numeric operations. All return num Data Type.

Universal across all four database platforms:

Per-database variants:

⁵ PostgreSQL has ROUND(numeric, integer) but not ROUND(real, integer) or ROUND(double precision, integer). Float-typed columns on PG would error with "function round(real, integer) does not exist." The PG expressions wrap with CAST(? AS NUMERIC); the CAST is a no-op on already-numeric columns and rescues float ones. ⁶ CAST_TO_DECIMAL is a one-click helper for the integer-division-needs-decimal pattern. When both operands of a division are integers, most databases return an integer result (5 / 2 gives 2, not 2.5). Casting one operand to decimal first restores the expected decimal output.

Other (conditional and NULL handling)

The most-asked-about functions after aggregates. Used in almost every advanced formula.

Universal across all four database platforms:

Per-database variants:

⁷ The MSSQL and PostgreSQL expressions cast the numerator to DECIMAL/NUMERIC because both databases use integer division when both operands are integers. Without the cast, 5 / 2 would silently return 2 instead of 2.5. MySQL and Oracle do floating-point division natively, so their expressions are simpler. Wrapping the divisor in NULLIF(divisor, 0) converts a zero divisor to NULL, so the division returns NULL instead of throwing a divide-by-zero error. ⁸ The condition ? > 0 is an example. Replace it with your actual comparison expression (e.g. ? = 'shipped', ? IS NOT NULL, ? > [insert_field]).

Insert Report Field

The sixth entry in the function-type menu is Insert Report Field. Unlike the function categories above, this isn't a SQL function — it's a field picker. Selecting it opens a list of every field currently on the report. Picking a field drops its alias-qualified reference into the Formula box, formatted for the database your app is connected to:

• MSSQL → [alias].[field] (square brackets)

• PostgreSQL → "alias"."field" (double quotes)

• MySQL → aliasfield (backticks)

• Oracle → "alias"."field" (double quotes)

Use Insert Report Field anywhere a placeholder like [insert_field], [insert_end_date_field], or [insert_divisor_field] appears in a formula. Don't type the alias by hand — aliases are auto-generated (svc_8066, chnl_5967, etc.) and the picker always emits the correct value for your database.

Common errors and fixes

The errors below come up often enough to warrant lookup. Each lists the symptom, the cause, and the fix.

column "<placeholder_name>" does not exist (PostgreSQL)<br>or Invalid column name '<placeholder_name>' (MSSQL)

Cause. You saved a formula with a picker placeholder still in it — decimal_places, number_of_chars, start_position, number_of_days, etc. The database treats the unquoted bare identifier as a column reference and can't resolve it.

Fix. Open the formula, find the snake_case word the error names, and replace it with a literal value. For example: ROUND(?, decimal_places) → ROUND(?, 2).

column "[insert_field]" does not exist or similar field-reference errors

Cause. You left an [insert_field], [insert_end_date_field], or [insert_divisor_field] placeholder in your formula.

Fix. Open the formula, place your cursor on the placeholder, type @, choose Insert Report Field, and pick the field you want. The picker drops in the correct alias-qualified reference for your database.

function round(real, integer) does not exist (PostgreSQL)

Cause. Your field is stored as real or double precision in the database, and PostgreSQL only has ROUND(numeric, integer) — there's no two-argument ROUND for floats.

Fix. Use the picker's ROUND entry; it already wraps with CAST(? AS NUMERIC). If you hand-wrote the formula, wrap the field reference the same way: ROUND(CAST(? AS NUMERIC), 2).

SAFE_DIVIDE returned 0 when I expected a decimal

Cause. Both numerator and divisor are integer-typed columns, and you're on MSSQL or PostgreSQL — both do integer division when both operands are integers, so 1 / 2 = 0.

Fix. Use the picker's SAFE_DIVIDE entry; the MSSQL and PG versions already cast the numerator. If you hand-wrote it, cast the numerator: CAST(? AS DECIMAL(18, 4)) / NULLIF([insert_divisor_field], 0) (MSSQL) or CAST(? AS NUMERIC) / NULLIF([insert_divisor_field], 0) (PG).

DATEDIFF_DAYS on a TIMESTAMP field returns a weird value (PostgreSQL)

Cause. The original PostgreSQL expression ([alias].[end_date] - ?) returned an INTERVAL when either operand was a TIMESTAMP, not an integer day count.

Fix. Use the picker's DATEDIFF_DAYS entry; the PG version now casts both sides to DATE: (CAST([insert_end_date_field] AS DATE) - CAST(? AS DATE)). If you have an existing formula using the old expression, replace it.

CONCAT returned NULL instead of my text (MySQL)

Cause. MySQL's CONCAT(NULL, anything) returns NULL. MSSQL, PostgreSQL, and Oracle treat NULL operands as empty strings.

Fix. Use the picker's CONCAT entry on MySQL; it already wraps each operand with COALESCE(operand, ''). If you hand-wrote it: CONCAT(COALESCE(?, ''), COALESCE([insert_field], '')).

Trailing zero is missing on rounded numbers (e.g. 257.4 instead of 257.40)

Cause. Numeric display in DashboardFox strips trailing zeros after the decimal point. The database is rounding to 2 decimals correctly; the display layer is hiding the trailing zero.

Fix. No clean fix at the SQL formula level — this is a display-layer behavior. Three workarounds: (1) Use the cur Data Type, which always shows 2 decimal places (trade-off: shows a currency symbol). (2) Format as a string with TO_CHAR(ROUND(CAST(? AS NUMERIC), 2), 'FM999999990.00') and set Data Type to cha (trade-off: alphabetic sort, no aggregation). (3) Pair a display string column with a non-visible numeric sort column. None are ideal; this is a known limitation.