BRQL helper functions
BRQL includes helper functions that simplify common calculations, formatting, and logic.
These functions extend standard T-SQL behaviour and are designed to reduce repetitive expressions, improve readability, and handle common reporting scenarios more efficiently.
Use helper functions when standard SQL functions require more complex or repetitive expressions, such as handling NULL values or simplifying calculations.
In this article:
- Numeric helper functions
- String helper functions
- Logic helper functions
- Context helper functions
- Date and time helper functions
- Need more support?
Numeric helper functions
Numeric helper functions are used for calculations such as totals, averages, and running values. Use these functions when you need to aggregate data or control how values are calculated across rows.
SUMZ
Treats NULL values (displayed as — in reports) as 0 when summing values.
Use this when your data contains NULL values that should be included in totals as zero instead of being ignored.
Example: A measure on the Orders table that sums Net payment while treating NULL values as zero.
// Syntax: SUMZ(expression) SUMZ([$.NetPayment]) // Equivalent to SUM(ISNULL([$.NetPayment], 0))
COUNTD
Counts the number of distinct (unique) non-null values within a group of rows.
Use this when you need to count unique values, such as the number of unique orders, customers, or products.
Often used with OVER (…) to calculate distinct counts per group without changing the report structure.
Example: A dimension on the Orders table that counts how many unique orders each customer has placed.
// Syntax: COUNTD(expression) COUNTD([$.Id]) OVER (PARTITION BY [$.CustomerId]) // Counts distinct Order IDs per customer
SUMB
Calculates a running (cumulative) sum across rows based on a defined order.
Use this to track how values build over time, such as running totals, counts, or event progression.
Think of this as adding values row by row in a sequence, keeping a running total.
Used with OVER (…) to define how rows are grouped and the order in which the running total is calculated.
Example: A dimension on the Orders table assigning a running order count for each customer across their orders.
// Syntax: SUMB(expression) OVER (PARTITION BY ... ORDER BY ...) SUMB(1) OVER (PARTITION BY [$.CustomerId] ORDER BY [$.Id]) // Order 1 → 1 // Order 2 → 2 // Order 3 → 3 // Increments by 1 for each order per customer
Example: A dimension on the Orders table tracking how many cancelled orders have occurred for each customer up to each order.
SUMB([$.IsCancelled]) OVER (PARTITION BY [$.CustomerId] ORDER BY [$.Id]) // No cancellations → 0 // First cancelled order → 1 // Next order (not cancelled) → 1 // Second cancelled order → 2 // Adds 1 for each cancelled order as rows progress
CONSTRAIN
Limits a value so it stays within a specified range.
Use this to cap values within a minimum and maximum, such as restricting dates to a reporting period or limiting numeric values to a defined range.
Think of this as setting boundaries that a value cannot go outside.
- If the value is less than the minimum, returns the minimum
- If the value is greater than the maximum, returns the maximum
- Otherwise, returns the original value
Example: A dimension on the Orders table that constrains a date to a specific range.
// Syntax: CONSTRAIN(value, min, max) CONSTRAIN([$.CreatedAt], '2024-01-01', '2024-12-31') 2023-11-15 → 2024-01-01 // Before range → returns min 2024-06-10 → 2024-06-10 // Within range → unchanged 2025-02-20 → 2024-12-31 // After range → returns max // Limits the date to the specified range
RANDOM_INT
Returns a random integer within a specified range.
- The first argument is the minimum value
- The second argument is the maximum value
- The result is inclusive of both bounds
Use this to generate sample data, assign random groupings, or simulate values for testing and experimentation.
Random values will change each time the report is refreshed.
Example: A dimension that generates a random integer between -10 and 10.
// Syntax: RANDOM_INT(min, max) RANDOM_INT(-10, 10) // Returns a random integer between -10 and 10 // Example output: -3, 7, 0, 2
RANDOM_FLOAT
Returns a random decimal (floating-point) number between 0 and 1.
Use this to generate random proportions, probabilities, or sample values for testing.
Random values will change each time the report is refreshed.
Example: A dimension that generates a random decimal value.
// Syntax: RANDOM_FLOAT() RANDOM_FLOAT() // Returns a random decimal between 0 and 1 // Example output: 0.472917
String helper functions
String helper functions are used to clean, extract, and format text values. Use these functions when working with product titles, SKUs, tags, or other text-based data.
STRING_AGG
Combines multiple text values from related rows into a single string using a chosen separator.
Use this when you need to display multiple related values as a single field, such as listing all products, tags, or items within a group.
Think of this as turning multiple rows into a single text value.
Example: A dimension on the Orders table that lists all unique product titles within each order as a comma-separated list.
// Syntax: STRING_AGG(expression, 'separator')
(
SELECT STRING_AGG(title, ', ')
FROM
(
SELECT DISTINCT [i.ProductTitle] AS title
FROM [$.AgreementLines i]
WHERE [i.LineType] = 'PRODUCT'
) t
)
// Lists all unique product titles within each order
// Returns: Product A, Product B, Product C
NEW_LINE
Inserts a line break character.
Use this with STRING_AGG when you want to display multiple values on separate lines instead of combining them into a single line.
Think of this as replacing a separator (such as a comma) with a line break.
Example: A dimension on the Orders table that lists all unique product titles within each order on separate lines.
// Syntax: NEWLINE() ( SELECT STRING_AGG(title, NEWLINE()) FROM ( SELECT DISTINCT [i.ProductTitle] AS title FROM [$.AgreementLines i] WHERE [i.LineType] = 'PRODUCT' AND [i.Kind] = 'Sale' ) t ) // Displays each product title on a new line within the order // Returns: // Product A // Product B // Product C
AFTER and BEFORE
Extract parts of a string based on a delimiter.
- AFTER returns the portion of the string after the delimiter
- BEFORE returns the portion of the string before the delimiter
Use these functions to split values such as SKUs, IDs, or formatted text into meaningful parts.
Examples:
// Syntax: AFTER(delimiter, string, [lastOccurrence = 0])
// Syntax: BEFORE(delimiter, string, [lastOccurrence = 0])
AFTER('_', 'Hello_World_Again') // Returns World_Again
AFTER('_', 'Hello_World_Again', 1) // Returns Again
BEFORE('_', 'Hello_World_Again') // Returns Hello
BEFORE('_', 'Hello_World_Again', 1) // Returns Hello_World
REMOVECHARS and KEEPCHARS
Modify strings by removing or keeping specified characters.
- REMOVECHARS removes all specified characters from a string
- KEEPCHARS keeps only the specified characters and removes others
Use these functions to clean or standardise text, such as formatting phone numbers, extracting numeric values, or removing unwanted symbols.
Example: Clean a phone number by removing unwanted characters or keeping only numeric values.
// Syntax: REMOVECHARS(string, 'charactersToRemove') // Syntax: KEEPCHARS(string, 'charactersToKeep') REMOVECHARS([$.ShippingAddress.Phone], ' ,()-') // +1(012)345-678 → 1012345678 KEEPCHARS([$.ShippingAddress.Phone], '0123456789') // +1(012)345-678 → 1012345678
COLLAPSE_CONSECUTIVE_CHARS
Replaces repeated consecutive occurrences of a specified character with a single instance.
Use this to clean text that contains duplicate characters, such as removing extra spaces or repeated separators.
Think of this as compressing repeated characters into one.
Example: Clean a product title by removing extra spaces.
// Syntax: COLLAPSE_CONSECUTIVE_CHARS(string, 'character') COLLAPSE_CONSECUTIVE_CHARS([$.ProductTitle], ' ') // Durable Skin Cream → Durable Skin Cream
LAST_CHARINDEX
Returns the position of the last occurrence of a substring within a string. Returns 0 if the substring is not found.
Use this when you need to locate the final separator in a value, such as the last hyphen, slash, or underscore in a SKU, code, or path.
Think of this as finding the final matching characters in a string.
Example: Find the position of the last hyphen in a SKU.
// Syntax: LAST_CHARINDEX('substring', string)
LAST_CHARINDEX('-', [$.SKU])
// SKU 1234-67-9 → 8
Logic helper functions
Logic helper functions evaluate conditions and return TRUE or FALSE values. Use these for filtering, grouping logic, and determining whether conditions are met across rows.
IS_TRUE and IS_FALSE
Evaluates a condition and explicitly returns TRUE or FALSE.
Use these functions when you need to ensure a condition returns a boolean value, such as within other functions or for clearer, more explicit logic.
Example: A dimension on the Orders table that checks whether an order total is greater than 100.
// Syntax: IS_TRUE(condition) // Syntax: IS_FALSE(condition) IS_TRUE([$.TotalPrice] > 100) IS_FALSE([$.TotalPrice] > 100) // Returns TRUE if TotalPrice is greater than 100, otherwise FALSE // 150 → TRUE (IS_TRUE) / FALSE (IS_FALSE) // 80 → FALSE (IS_TRUE) / TRUE (IS_FALSE)
MINB and MAXB
Evaluate a condition across a group of rows.
- MAXB returns TRUE if any row meets the condition
- MINB returns TRUE if all rows meet the condition
Use these functions to check whether a condition is met anywhere or everywhere within a group, such as whether an order contains a specific product or whether all items meet a condition.
Think of MAXB as “any” and MINB as “all”.
Example: A dimension on the Agreement lines table that checks whether an order contains items with a specific product title.
// Syntax: MAXB(expression) OVER (...) // Syntax: MINB(expression) OVER (...) MAXB(IS_TRUE([$.ProductTitle] = 'Banana')) OVER (PARTITION BY [$.OrderId]) // Returns TRUE if at least one line in the order has ProductTitle = 'Banana' MINB(IS_TRUE([$.ProductTitle] = 'Banana')) OVER (PARTITION BY [$.OrderId]) // Returns TRUE only if all lines in the order have ProductTitle = 'Banana'
Context helper functions
Context helper functions control how values behave based on the report’s structure and grouping. Use these functions to detect totals, control when values appear, and adjust calculations depending on the current grouping context.
IS_TOTAL, IS_SUBTOTAL, IS_GRANDTOTAL
Return TRUE or FALSE depending on the level of aggregation in the report.
- IS_TOTAL returns TRUE for all total rows (including subtotals and grand totals)
- IS_SUBTOTAL returns TRUE only for subtotal rows
- IS_GRANDTOTAL returns TRUE only for the grand total row
Use these functions to control how values appear in totals, such as hiding values in subtotal rows or applying different logic at different aggregation levels.
Think of these as detecting where you are in the report: row level, subtotal, or grand total.
These functions must be used in a measure.
IS_TOTAL() // TRUE for all totals IS_SUBTOTAL() // TRUE only for subtotals IS_GRANDTOTAL() // TRUE only for the grand total
IS_ACTIVE_DIMENSION
Returns TRUE when the specified dimension is included in the current grouping in the report.
Use this to control when values appear based on whether a dimension is present, such as showing values only at a specific level and hiding them in subtotals or when the dimension is not included.
Think of this as checking whether a column is currently being used to group the data in the report.
This function must be used in a measure.
Example: Show a value only when Kind is included in the report grouping.
// Syntax: IS_ACTIVE_DIMENSION(expression) IS_ACTIVE_DIMENSION([$.Kind]) // Returns TRUE when Kind is included in the grouping // Returns FALSE when Kind is not included (for example, in subtotals)
SINGLE
Returns a value only when all rows in the current context have the same value. Otherwise, returns NULL.
Use this when you want to display a value only if it is consistent across a group, such as showing a field only when all rows share the same value.
Think of this as returning a value only if all rows agree.
This function must be used in a measure.
Example: Show the kind only when all rows in the current grouping have the same value.
// Syntax: SINGLE(expression) SINGLE([$.Kind]) // All rows have "Sale" → "Sale" // Mixed values → NULL
HYPERLINK
Creates a clickable link using a URL and display text.
Use this to add navigation or external links to your report, such as linking to other reports, records, or external pages.
Example: A dimension that creates a clickable link to a report using its name.
// Syntax: HYPERLINK(url, displayText) HYPERLINK([$.ReportImpersonateUrl], [$.Report.Name]) // Displays the report name as a clickable link to the report
Date and time helper functions
Date and time helper functions are used to work with dates, perform time-based comparisons, and calculate intervals or offsets. Use these functions when filtering data by date, comparing time periods, or transforming date and time values.
NOW, TODAY, TOMORROW, YESTERDAY
Return current date or datetime values based on the account’s time zone.
- NOW returns the current date and time
- TODAY returns the current date
- TOMORROW returns the next date
- YESTERDAY returns the previous date
Use these functions to filter data relative to the current date or time, such as showing recent activity, comparing periods, or creating dynamic date-based calculations.
Think of these as dynamic date values that update automatically based on the current time.
Example: Filter orders created yesterday or today.
IS_TRUE([$.CreatedAt] >= YESTEDAY()) // Returns TRUE for orders created yesterday or today
DATEDIFF
Returns the number of date part boundaries crossed between two dates.
Use this to calculate differences between dates, such as the number of days, weeks, or months between events.
Think of this as counting how many times a date boundary (such as day or month) is crossed between two values.
Example: A dimension on the Orders table that counts how many day boundaries were crossed between when an order was placed and when it was fulfilled.
// Syntax: DATEDIFF(datePart, startDate, endDate) DATEDIFF(DAY, [$.ProcessedAt], [$.FulfilledAt]) // Counts how many day boundaries are crossed // 2024-01-01 23:59 → 2024-01-02 00:01 = 1
WEEKDAYDIFF
Returns the number of times a specified weekday occurs between two dates, excluding the start and end dates.
Use this to count how often a specific day of the week occurs within a date range, such as the number of Mondays in a month or the number of business days between events.
Think of this as counting how many times a specific weekday appears between two dates.
Example: A dimension that counts how many Mondays occur between two dates.
// Syntax: WEEKDAYDIFF(dayOfWeek, fromDate, toDate) WEEKDAYDIFF(1, '2024-01-01', '2024-01-31') // Counts how many Mondays occur between the dates // Returns: 4
DAY_OF_WEEK
Returns the day of the week as a number, where 1 is Monday and 7 is Sunday.
Use this to group, filter, or apply logic based on the day of the week, such as identifying weekends or analysing weekly patterns.
// Syntax: DAY_OF_WEEK(date) DAY_OF_WEEK([$.Date]) // Returns a number from 1 (Monday) to 7 (Sunday)
TIMEZONE
Returns the account’s time zone.
Use this when working with date and time calculations that depend on the account’s configured time zone.
// Syntax: TIMEZONE() TIMEZONE() // Returns: UTC
IS_NTH_DAY_OF_MONTH
Returns TRUE when the date matches a specific occurrence within a month.
Use this to identify recurring dates, such as the third Sunday of a month or the first Monday of each month.
// Syntax: IS_NTH_DAY_OF_MONTH(date, nthOfMonth, dayOfWeek, [monthOfYear]) IS_NTH_DAY_OF_MONTH([$.Date], 3, 7, 2) // Returns TRUE if the date is the third Sunday of February // Returns FALSE otherwise
NUM_DAYS_SINCE_MOST_RECENT_DAY_OF_WEEK
Returns the number of days since the most recent specified weekday.
Use this to measure how many days have passed since a recurring weekday, such as the most recent Thursday or Monday.
// Syntax: NUM_DAYS_SINCE_MOST_RECENT_DAY_OF_WEEK(date, dayOfWeek) NUM_DAYS_SINCE_MOST_RECENT_DAY_OF_WEEK(TODAY(), 4) // Returns the number of days since the most recent Thursday // Example: If today is Friday, returns 1
MOST_RECENT_DAY_OF_WEEK
Returns the date of the most recent specified weekday.
Use this to anchor calculations or comparisons to a recent weekday, such as finding the most recent Monday or Thursday.
Used together with comparison logic to filter data relative to a specific weekday.
// Syntax: MOST_RECENT_DAY_OF_WEEK(date, dayOfWeek) MOST_RECENT_DAY_OF_WEEK(TODAY(), 4) // Returns the date of the most recent Thursday
MOST_RECENT_TIME_OF_WEEK
Returns the datetime of the most recent specified weekday and time.
Use this to anchor comparisons to a recurring point in the week, such as the most recent Thursday at 12:00.
// Syntax: MOST_RECENT_TIME_OF_WEEK(datetime, dayOfWeek, timeOfDay) MOST_RECENT_TIME_OF_WEEK(NOW(), 4, '12:00') // Returns the most recent Thursday at 12:00
UNIX_TIMESTAMP
Converts a date or datetime value into a Unix timestamp.
A Unix timestamp represents the number of seconds since January 1, 1970 (UTC).
Use this to standardise date and time values for comparisons, calculations, or integrations that require numeric timestamps.
// Syntax: UNIX_TIMESTAMP(date)
UNIX_TIMESTAMP('2025-02-01 10:03:05')
// Returns the Unix timestamp for the given datetime
// Returns: 1738404185
TRY_PARSE_DATE
Attempts to convert a string into a date.
Supports multiple common date formats and returns a valid date if the value can be interpreted. If parsing fails, it returns NULL.
Use this to convert text-based date values into a usable date format, such as when working with imported or inconsistent data.
Set the data type to date when creating the custom field.
Example: A dimension that converts a text value into a date.
// Syntax: TRY_PARSE_DATE(string, format)
TRY_PARSE_DATE('2025-02-01', 'yyyy-mm-dd')
// Returns: Feb 1 2025
TRY_PARSE_DATETIME
Attempts to convert a string into a datetime value.
Supports multiple common datetime formats and returns a valid datetime if the value can be interpreted. If parsing fails, it returns NULL.
Use this to convert text-based datetime values into a usable datetime format, such as when working with imported or inconsistent data.
Set the data type to Date & Time when creating the custom field.
Example: A dimension that converts a text value into a datetime.
// Syntax: TRY_PARSE_DATETIME(string, format)
TRY_PARSE_DATETIME('2025-02-01 10:03:05', 'yyyy-mm-dd hh:mm:ss')
// Returns: Feb 1 2025 10:03:05
Need more support?
If you get stuck or have additional questions, you can contact our team directly through the Help widget in the bottom-right corner — we typically respond within one business day.