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
• BRQL helper 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.

The sections below explain how direct and open joins work in more detail.

Join types

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

Direct join [$.]

• Represents a join based on the table’s primary ID relationship.
• Automatically aligns with the primary relationship between tables.

Example: [$.Date]      retrieves the Date field from the same table that the custom field is rooted in.

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

Open join [$]

• Used when the default relationship between tables does not match the logic needed for the calculation.
• Requires the relationship to be defined explicitly in the subquery.

Example: A dimension on the Products table that shows inventory quantity aggregated to the product type level. In this scenario, inventory quantities are stored at the variant level on the Product variants table, while the custom field is rooted in the Product table. Because the aggregation level does not match the default table relationship, an open join is required and must be 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.

Commonly used types of functions can be found below:

Aggregates

BRQL supports common aggregate functions such as SUM, MAX, and COUNT. Some aggregate behaviour differs from standard SQL. For example, the SUMZ helper function can be used when NULL values should be treated as 0. These helper functions are explained later in this guide.

• 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 that can be used in dimensions and measures.

• IS_TRUE(expression): Returns TRUE when the expression evaluates to true.
• IIF(condition, trueValue, falseValue): Evaluates a condition and returns one of two values.

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

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

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

String manipulation

String functions allow you to format, combine, or extract text values when creating custom fields.

• 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 from the same table.

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

Mathematical operations

Mathematical operations support basic arithmetic and numeric transformations in measures and dimensions.

• 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 the order Processed at date and the Fulfilled at field from the Fulfillments table.

This example uses a direct join to reference the related Fulfillments table.

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; otherwise, returns the expression.
• NULLIF(expression1, expression2): Returns the first expression, or NULL if both expressions are equal.

Example: A dimension on the Product variants table that returns a numeric value of 1 if the Cost field is NULL.

ISNULL([$.Cost], 1)

BRQL helper functions

BRQL includes helper functions that simplify common calculations, formatting, and logic. These functions are designed to reduce repetitive expressions and improve readability.

Numeric helper functions

SUMZ treats NULL values as 0 when summing values.

Example: A measure on the Orders table that sums the Net payment field while treating NULL values as zero.

// Syntax: SUMZ(expression)

SUMZ([$.NetPayment]) // Equivalent to SUM(ISNULL([$.NetPayment], 0))

String helper functions

REMOVECHARS and KEEPCHARS modify strings by keeping or removing specified characters.

Example: A dimension on the Orders table that removes non-numeric characters from the shipping address phone number.

// Syntax: REMOVECHARS(stringToClean, 'charactersToRemove')

REMOVECHARS([$.ShippingAddress.Phone], ' ,()-')
// +1(012)345-678 would become 1012345678

Example: A dimension on the Orders table that keeps only numeric characters from the shipping address phone number.

// Syntax: KEEPCHARS(stringToClean, 'charactersToKeep')

KEEPCHARS([$.ShippingAddress.Phone], '0123456789')
// +1(abc)345-678 would become 1345678

AFTER and BEFORE return substrings based on a delimiter.

Examples:

// Syntax: AFTER('strToFind', strToSearch, [searchForLastOccurence = 0])

AFTER('_', 'Hello_World_Again') // Returns 'World_Again'
AFTER('_', 'Hello_World_Again', 1) // Returns 'Again'

Examples:

// Syntax: BEFORE('strToFind', strToSearch, [searchForLastOccurence = 0])

BEFORE('_', 'Hello_World_Again') // Returns 'Hello'
BEFORE('_', 'Hello_World_Again', 1) // Returns 'Hello_World'

Date and time helper functions

Date and time helper functions support common calendar-based logic and time comparisons.

• NOW(): Returns the current date and time in the account’s time zone.
• TODAY(): Returns the current date in the account’s time zone.
• TIMEZONE(): Returns the account’s time zone.
• DAY_OF_WEEK(date): Returns the day of the week as a number, where 1 is Monday and 7 is Sunday.
• IS_NTH_DAY_OF_MONTH(date, nthOfMonth, dayOfWeek, [monthOfYear]): Returns TRUE when the date is the specified weekday occurrence within a month.
• NUM_DAYS_SINCE_MOST_RECENT_DAY_OF_WEEK(date, dayOfWeek): Returns the number of days since the most recent occurrence of the specified weekday.
• MOST_RECENT_DAY_OF_WEEK(date, dayOfWeek): Returns the most recent occurrence of the specified weekday.
• MOST_RECENT_TIME_OF_WEEK(datetime, dayOfWeek, timeOfDay): Returns the most recent occurrence of the specified weekday and time.

Examples:

// Return today's day of the week
DAY_OF_WEEK(TODAY())

// Check if a date is the third Sunday of February
IS_NTH_DAY_OF_MONTH([$.Date], 3, 7, 2)

// Check if a date is after the most recent Thursday
IS_TRUE([$.Date] > MOST_RECENT_DAY_OF_WEEK(TODAY(), 4))

// Check if a date and time is after the most recent Thursday at 12 noon
IS_TRUE([$.Date] > MOST_RECENT_TIME_OF_WEEK(NOW(), 4, '12:00'))

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.