BRQL for custom fields

BRQL (Better Reports Query Language) is an extension of T-SQL used to create custom dimensions and measures in the platform. It supports data manipulation and analysis for complex reporting scenarios.

This guide explains the core structural elements of BRQL and how they relate to standard SQL concepts when creating custom fields.

For the steps to create a custom field, see Create a custom field.

In this article:

• Core concepts
• Syntax
• Functions
• Best practices
• Need more support?

Core concepts

When using BRQL, these core elements determine how a custom field retrieves and manipulates data:

• Field type: Whether the custom field is a dimension or a measure
• Root table: The table where this custom field is created. This determines which data the field can reference
• Expression elements: The fields, joins, and functions used to define the custom field
  • Reference field BRQL name and root table: The BRQL name of the reference field and the table it belongs to
  • Syntax: How the reference field is joined and accessed
  • BRQL function: The operation applied to the referenced data

Syntax

The syntax used to reference fields in BRQL determines how data is joined and accessed when building a custom field.

In the examples below, angle brackets indicate placeholders that should be replaced with actual table and field names.

• [$.<FieldName>]: References a field on the same table using a direct join
• [$.<TableName.FieldName>]: References a field on a related table using a direct join
• [$<TableName.FieldName>]: References a field using an open join, typically in a subquery

Join types

BRQL supports two join types that control how fields are accessed in expressions.

Direct join [$.]

• Uses the table's primary ID relationship
• Automatically aligns with the primary relationship between tables

Example: [$.Date]  retrieves the Date field from the root table

Example:[$.Order.ProcessedAt]  retrieves the Processed at field from the Orders table

Open join [$]

• Used when the default relationship does not match the calculation
• Requires the relationship to be defined in the subquery

Example: A dimension on the Products table that shows inventory quantity aggregated at the product type level.

Inventory quantities are stored at the variant level on the Product variants table, while the custom field is rooted in the Products table. Because the aggregation level does not match the default table relationship, an open join must be used and defined explicitly in a subquery.

(
	SELECT SUMZ([v.InventoryQuantity])
	FROM [$Variants v]
	WHERE [$.ProductType] = [v.Product.ProductType]
)

Functions

BRQL supports many commonly used T-SQL functions, with some variations and BRQL-specific extensions.

For BRQL-specific helper functions, see BRQL helper functions.

Aggregates

Aggregate functions return a single value based on a set of rows. They are typically used in measures to summarise data.

BRQL supports common aggregate functions such as SUM, MAX, and COUNT. Some behaviour differs slightly from standard SQL. For example, NULL values are excluded by default.

• SUM(expression): Returns the sum of values and excludes NULL values
• MAX(expression): Returns the maximum value
• AVG(expression): Returns the average of values and excludes NULL values
• COUNT(expression): Returns the count of non-null values

Example: A measure on the Orders table that shows the highest total order value.

MAX([$.TotalPrice])

Logical operations

Logical operations evaluate conditions and return boolean or conditional values. These are commonly used to create flags or conditional dimensions.

• IS_TRUE(expression): Returns TRUE when the expression evaluates to true
• IIF(condition, trueValue, falseValue): Returns one of two values based on a condition

Example: A dimension on the Product variants table that checks whether a variant is in stock and the related product is active.

IS_TRUE([$.InventoryQuantity]>0 AND [$.Product.Status] = 'active')

This example uses a field on the root table (Inventory quantity) and a field on a related table (Status) accessed using a direct join.

String manipulation

String functions allow you to combine, format, or extract text values. These are typically used in dimensions.

• CONCAT(string1, string2): Combines two or more strings into a single value
• LEFT(string, length): Returns a specified number of characters from the start of a string

Example: A dimension on the Customers table that combines the First name and Last name fields.

CONCAT([$.FirstName], ' ', [$.LastName])

Mathematical operations

Mathematical operations support  arithmetic and numeric transformations.

• Standard operators: +, -, *, /
• ROUND(expression, decimalLength): Rounds a numeric value to a specified number of decimal places.

Example: A measure on the Agreement lines table that adds the Total gross sales and Total discounts measures.

[$.TotalGrossSales] + [$.TotalDiscounts]

This calculation is evaluated within the context of the report and follows the aggregation rules of the referenced measures.

Date and time functions

Date and time functions allow you to compare dates, calculate intervals, and extract specific date components.

• DATEDIFF(datepart, startDate, endDate): Returns the difference between two dates based on the specified date part
• DATEPART(datepart, date): Returns a specific part of a date, such as the day or month

Example: A dimension on the Orders table that calculates the number of days between when an order was processed and fulfilled.

DATEDIFF(DAY, [$.ProcessedAt], [$.Fulfillment.FulfilledAt])

Null handling operations

Null handling functions help control how missing or unknown values are treated in calculations.

• ISNULL(expression, replacementValue): Returns the replacement value when the expression is NULL
• NULLIF(expression1, expression2): Returns NULL if both expressions are equal

Example: A dimension on the Product variants table that replaces missing cost values with 1.

ISNULL([$.Cost], 1)

Best practices

• Use descriptive, human-readable names for custom fields so reports are easier to interpret.
• Avoid deeply nested expressions, as they can impact report performance.
• Use window functions instead of subqueries where possible.
• Test complex expressions using a limited date range or a smaller number of records.
• Add comments to formulas to explain their purpose.