Formula builder
The formula builder is a visual editor for the formulas of measures, derived and constant dimensions, and derived and constant facts. It shows a formula as a diagram of connected blocks, and the diagram and the formula text stay synchronized: changing one updates the other.
Opening the builder
Open formula builder is available:
- On the table control panel of the Measures, Dimensions, and Facts tabs, when exactly one row is selected
- In the Details from RMD dialog of an element
- Next to the Formula field of the measure dialog
The button is active for measures always, and for dimensions and facts only when their type is Derived or Constant — primary elements are described by a connected source instead of a formula. For elements with errors the button is not available.
The builder window
| Area | Content |
|---|---|
| Formula bar | The name of the element and its formula as text. The formula can be edited here directly |
| Elements panel (on the left) | The elements, functions, operators, and constants that a formula can be built from |
| Canvas | The formula as a diagram of blocks |
| Properties panel (under the canvas) | Information about the selected block |
| Footer | Cancel and Apply |
Next to the formula bar there are the Undo, Redo, and Reset all changes buttons. Reset all changes returns the formula to the state it had when the builder was opened. Ctrl + Z and Ctrl + Y work as well. The canvas has the usual zoom, zoom-out and fit-to-view controls.
Elements panel
The panel starts with a search field and is divided into collapsible sections:
| Section | Content |
|---|---|
| Measures, Dimensions, Facts | The elements of the current project version |
| Functions | Grouped as Aggregation, String, Date & Time, Conditional, and Null Handling |
| Operators | Arithmetic and String |
| Comparison & Logic | Comparison, Logical, and Range |
| Constants | A numeric or text constant, the boolean values, an empty value, and the current date |
Every row shows the call syntax of the function or operator next to its name. A search expands the sections that contain matches; if there are none, the section shows No elements found. An element row also has an information icon that opens Details from RMD for that element. Elements with errors cannot be added to a formula.
Measures carry the health icon that shows their overall health status: in the elements panel, next to the name of the measure being edited, and on the measure blocks of the canvas. Hovering over the icon shows the health summary — see the Measure health cards page.
Building a formula
- Drag an element from the panel onto the canvas, or click it to place it in the middle of the canvas
- Functions and operators are placed together with empty slots for their arguments — a slot is shown as a block with a plus sign
- Blocks are connected by dragging from the output of one block to an input of another. Each input and each output can hold a single connection: a new connection replaces the one that conflicts with it
- A connection is deleted by selecting it and using its delete button; a block is deleted with the cross in its corner
- The Result block is always present on the canvas and cannot be deleted. It marks the value the whole formula produces
Block kinds on the canvas:
| Block | Contents |
|---|---|
| Element | The name of a measure, dimension, or fact |
| Function | The function name in the header and one row per argument |
| Operator | The operator symbol and its operands, marked A and B (and C for range operators) |
| Constant | The value itself. A numeric or text constant is edited by double-clicking it |
| Empty slot | An argument that has not been filled in yet |
For the aggregation functions the block contains a FILTER row above the FIELD rows: the connection to FILTER becomes the condition that limits the values being aggregated. The IN, NOT IN, BETWEEN, and NOT BETWEEN operators have their own fields for the list of values or the boundaries of the range.
Looking into a referenced formula
When a block refers to an element that has a formula of its own, the block has an expand button. Expanding shows the formula of that element to the left of the block, in a different color, as a preview. Two actions are then available:
- Apply to formula — inserts the expanded formula in place of the reference, so it becomes part of the formula being edited
- Collapse — hides the preview and leaves the reference as it was
Function arguments that have an incoming connection have their own Expand and Collapse controls, which hide or show everything that feeds that argument.
Properties panel
Clicking a block shows in the panel:
- The kind of the block and its name or value
- For functions and operators — a description of what they do, including the meaning of their
FIELDandFILTERinputs - Current Formula — the fragment of the formula that this block produces with its current connections
- For an element — its calculation type, its own formula with references shown as names, and the View details in RMD link
While nothing is selected the panel shows a hint to select an element or write a formula.
Checking a formula
The formula in the formula bar is checked as you type. Errors are underlined, and the description is shown on hover; the same set of checks as for a formula cell of the RMD table is used (see the Formula syntax page).
If the formula that the builder was opened with cannot be parsed, a message about syntax errors appears over the canvas together with a Clear formula button. It disappears as soon as you start building the formula anew.
Saving
Apply writes the formula into the Formula column of the element. The button is unavailable when the formula is empty and in read-only mode.
If you close the builder with Cancel, with the cross, or with Escape while there are unsaved changes, the system asks Save changes in the formula and offers Leave without saving and Save changes.
Roles
Editing a formula requires the Developer role or above and a valid license.
For the Analyst and Viewer roles the builder opens in read-only mode. They can read the formula and the whole diagram, move the canvas and the blocks, zoom, select blocks and read the Properties panel, expand and collapse referenced formulas, and open Details from RMD. The Undo, Redo, and Reset all changes buttons are not shown to them, Apply is unavailable, and blocks and connections cannot be added, changed, or deleted.