Formula

Understanding Formulas in RestBI

A Formula represents a calculated field within your BI model. Formulas allow you to create derived data by applying calculations, aggregations, or conditional logic to the columns in your model. Formulas can reference existing columns or other formulas, and they can be as simple or as complex as needed. The system also supports custom functions, which can be extended to add new capabilities.

Example

Here is an example of how a Formula might be defined:

const formulaBikes: Formula = {
    id: "1",
    name: 'Formula Bikes',
    expression: "SUM(CASE WHEN {{Category Name}} = 'Bikes' THEN {{Total Due}} ELSE NULL END)"
};

const averageSales: Formula = {
    id: "2",
    name: 'Average Sales',
    expression: "AVG({{Total Due}})"
};

const yearFormula: Formula = {
    id: "3",
    name: 'Year',
    expression: "YEAR({{Order Date}})"
};

Formula Reference

Property	Description
id	A unique identifier for the formula.
name	The name of the formula, used for reference in queries.
expression	The SQL expression or calculation that defines the formula. It may reference other columns or formulas within the model.

How an Expression is Generated

The expression in a formula is a critical component that determines the calculation or logic applied to your data. It’s essentially a pass-through SQL expression that gets directly translated into the SQL query sent to your database. Understanding how this works will help you craft powerful and efficient formulas.

What is a Pass-Through SQL Expression?

A pass-through SQL expression means that whatever you write in the formula’s expression field will be directly incorporated into the SQL query generated by the system. This allows you to leverage the full power of SQL, including functions, operators, and conditional logic, without any additional abstraction or translation layer.

Components of an Expression

Column References

You can reference any column in your model using the {{Column Name}} syntax.

When the formula is processed, {{Column Name}} is replaced by the fully qualified column name in the resulting SQL query, including any table aliases.

expression: "{{Total Due}} * 0.1"

This expression would be converted to something like:

sales_order_header.TotalDue * 0.1

Other Formulas

Formulas can reference other formulas using the {{Formula Name}} syntax.

The system will recursively resolve these references, replacing them with the corresponding SQL expressions.

expression: "SUM({{Formula Bikes}})"

This expression will expand to include the full logic of the Formula Bikes formula:

SUM(CASE WHEN category.Name = 'Bikes' THEN sales_order_header.TotalDue ELSE NULL END)

SQL Functions

You can directly use SQL functions like SUM, AVG, COUNT, CASE WHEN, EXTRACT, and any other function supported by your database.

expression: "SUM(CASE WHEN {{Category Name}} = 'Bikes' THEN {{Total Due}} ELSE NULL END)"

This will be passed directly to the SQL query as:

SUM(CASE WHEN category.Name = 'Bikes' THEN sales_order_header.TotalDue ELSE NULL END)

Custom Functions

You can define and use custom functions that extend SQL functionality. These are useful for complex operations that are not natively supported by SQL.

expression: "PCT_OF_TOTAL({{Total Due}})"

If PCT_OF_TOTAL is a custom function, it will be translated into its SQL equivalent, such as:

(sales_order_header.TotalDue / SUM(sales_order_header.TotalDue) OVER ()) * 100

Pre-Built Filter Keys

Pre-built keys like THIS_YEAR or LAST_MONTH can be used in your expressions, and they are expanded into SQL at runtime.

expression: "{{Order Date}} BETWEEN {{THIS_YEAR}}"

This would be expanded to:

sales_order_header.OrderDate BETWEEN '2024-01-01' AND '2024-12-31'

See Also

• Columns - Discover how Formulas interact with Columns to create dynamic data points.

• Tables - Learn how Tables house the Columns that Formulas reference.

• Joins - Understand how to combine data from multiple Tables, which can be used in Formulas.

• Query Filters - Explore how to apply conditions to the results of Formulas using Query Filters.