BRQL for custom fields

BRQL (Better Reports Query Language) is an extension of T-SQL used to create custom dimensions and measures in Better Reports. This guide covers the core elements of a BRQL expression and how to reference fields and tables when building custom fields.

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

In this article:

• How BRQL references fields and tables
• BRQL field reference syntax
• Functions
• Best practices for BRQL expressions
• Need more support?

How BRQL references fields and tables

Every custom field in Better Reports is made up of three elements:

• 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 BRQL expression that defines what the custom field calculates, including which fields and functions to use

BRQL field reference syntax

When you write a BRQL expression, you need to tell Better Reports where each field comes from. BRQL supports two approaches for this: a joined field reference, which lets BRQL resolve the table relationship automatically, and a direct table query, which references a table explicitly in a subquery.

Joined field references

A joined field reference uses dot notation to access a field. BRQL resolves the table relationship automatically based on the primary relationship between tables. Use this approach when the built-in table relationship covers the data you need.

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

Example:[$.Order.ProcessedAt]  retrieves the Processed at field from the Orders table via an automatically managed join.

Example: The following example shows a joined field reference used in a complete expression. The dimension checks whether a variant is in stock and the related product is not active:

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

[$.InventoryQuantity] references a field on the root table. [$.Product.Status] uses dot notation to access Status on the related Products table via an automatically managed join.

Direct table queries

A direct table query references a table explicitly in the FROM clause of a subquery. Use this approach when the default table relationship does not match your calculation — for example, when the aggregation level of a subquery differs from the root table. You define the relationship condition yourself.

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, a direct table query is used and the relationship is defined explicitly in the subquery.

In this expression, [$Variants v]  queries the Product variants table directly and v  is the alias used to reference it within the subquery. [v.InventoryQuantity]  accesses the Inventory quantity field using dot notation on the alias. [v.Product.ProductType]  uses a joined field reference within the direct table query to traverse from Product variants to Products.

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

Functions

This section covers the most commonly used function categories in BRQL. BRQL supports many standard T-SQL functions, with some variations in behaviour.

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 not active. See Joined field references for an explanation of the syntax used.

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

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 for BRQL expressions

• 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 joined field references instead of direct table queries where possible — direct table queries use subqueries, which can slow down reports
• Test complex expressions using a limited date range or a smaller number of records.
• Add comments to formulas to explain their purpose — for example, // Calculates net revenue excluding refunds