Creating Custom Fields

BRQL (Better Reports Query Language) is a specialized extension of T-SQL designed to create custom fields and measures in Better Reports. It supports data manipulation and analysis for complex reporting needs.

Core Concepts

Table:

Represents a logical grouping of fields and measures (e.g., Customers, Products).

Identified by a Table name.

Dimensions:

Represent individual data points or attributes within a schema.

Defined by:

BRQL Name: Identifier used in expressions.
Type: Data type (e.g., Numeric, Boolean, String).
BRQL Expression: The formula used to derive the field value.
Kind:

Persisted: Derived from stored data.
Computed: Calculated dynamically using expressions.

Measures:

Aggregate or summary calculations applied to fields.

Defined by:

BRQL Name: Identifier for referencing the measure.
BRQL Expression: Formula defining the calculation.

BRQL Syntax Components

Field Reference

Fields are referenced using the notation:

[$.<FieldName>]: Indicates a direct join to the referenced table. [$<FieldName>]: Represents an open join and is only valid within a subquery.

Understanding the [$] and [$.] Syntax

[$.] (Dot Notation):

Represents a join based on the table’s ID key.

Automatically aligns with the primary relationship between tables.

Example: Retrieve the Date field (in the table the field is rooted in) or retrieve the Processed At field from the Order table (referenced from a different table).

sql
[$.Date] or [$.Order.ProcessedAt]

[$] (Open Join):

Used within subqueries to create custom joins.

Requires additional context in the subquery to define the relationship explicitly.

Note: Window functions should be chosen when possible over subqueries as these may slow down reports loading.

Example: Shows inventory quantity aggregated to the product type level, created on the Products table. Here, the join is explicitly managed in the subquery logic.

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

Functions

BRQL supports all T-SQL formulas and additional syntax for ease of use in Better Reports. Key functions include:

Aggregates:

SUM(expression): Sum of values.

AVG2(expression): Average of values, including null handling.

COUNT(expression): Count of non-null values.

Example: Aggregate total net sales (measure)

sql
SUMZ([$.TotalNetSales])

Logical Operations:

IS_TRUE(expression): Checks if the condition is true.

IF(condition, trueValue, falseValue): Conditional evaluation.

Example: Check whether a customer is subscribed to email marketing (dimension).

sql
IS_TRUE([$.EmailMarketingConsent.State] = 'subscribed')

String Manipulation:

CONCAT(string1, string2): Combines strings.

LEFT(string, length): Extracts the left part of a string.

Example: Create a full name field combining first and last name (dimension).

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

Mathematical Operations:

Standard operators: +, -, *, /.

Round functions: ROUND(expression, decimals).

Example: Calculate average order value (measure)

sql
ROUND([$.TotalSales] / [$.TotalOrders], 2)

Date and Time Functions:

DATEDIFF(datepart, startdate, enddate): Difference between dates.

Example: Calculate the number of days between the order being placed and it being fulfilled (dimension).

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

Built-In Helper Functions in BRQL

The built-in helper functions in BRQL are designed to simplify common patterns and tasks in report creation. These functions provide efficient ways to handle calculations, formatting, and logic, saving time and reducing repetitive code.

List of Helper Functions

Numeric Helper Functions

SUMZ

Ensures that null values are treated as 0 when summing fields, avoiding errors caused by unknown values.

Syntax:

sql
SUMZ(<expression>)

Equivalent to:

sql
ISNULL(SUM(<expression>), 0)

2. String Helper Functions

KEEPCHARS and REMOVECHARS

Helps clean or format strings by keeping or removing specific characters.

Syntax:

sql
KEEPCHARS(<property>, '<characters to keep>')
REMOVECHARS(<property>, '<characters to remove>')

Example:

sql
Clean_Phone = REMOVECHARS([$.ShippingAddress.Phone], ' ,()-')
	Example: +1(012)345-678 would become 1012345678
	
Clean_Phone = KEEPCHARS([$.ShippingAddress.Phone], '0123456789')
	Example: +1(abc)345-678 would become 1345678

AFTER and BEFORE

Extracts parts of a string based on a delimiter.

Syntax:

sql
AFTER(<string>, <delimiter>, <occurrence>)
BEFORE(<string>, <delimiter>, <occurrence>)

Example:

sql
AFTER('Hello_World', '_') -- Returns 'World'
AFTER('Hello_World_Again', '_', 1) -- Returns 'Again'

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

Date and Time Helper Functions

NOW(): Returns the current 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: Returns the day of the week as a number (1 for Monday, 7 for Sunday).

IS_NTH_DAY_OF_MONTH: Determines if a date is the Nth weekday of a specified month.

NUM_DAYS_SINCE_MOST_RECENT_DAY_OF_WEEK: Returns the number of days since the most recent specified day of the week.

MOST_RECENT_DAY_OF_WEEK: Retrieves the most recent occurrence of a specific day of the week.

MOST_RECENT_TIME_OF_WEEK: Gets the most recent occurrence of a specific day and time of the week.

Examples:

sql
DAY_OF_WEEK(TODAY()) -- Returns today's day of the week


IS_NTH_DAY_OF_MONTH(<date>, <nthOfMonth>, <dayOfWeek>, <monthOfYear>)

IsThirdSundayOfFeb = IS_NTH_DAY_OF_MONTH([$.Date], 3, 7, 2)

IsSinceMostRecentThursday = IS_TRUE([$.Date] > MOST_RECENT_DAY_OF_WEEK(NOW(), 4))
IsSinceMostRecentThursdayAtNoon = IS_TRUE([$.Date] > MOST_RECENT_TIME_OF_WEEK(NOW(), 4, '12:00'))

Best Practices

Use meaningful names for fields and measures to improve report clarity.

Avoid nested expressions that may impact performance.

Use window functions over subqueries whenever possible.

Test complex expressions in smaller parts to verify accuracy.

Include notes in your formula for easier reference.

We are always here to help create custom fields or provide guidance on the fields you make yourself. Please reach out to us at hello@betterreports.com for any assistance that you need.