Formula
A key concept in Data Access Studio is to provide a relevant set of point-and-click calculation columns that are easy to use and understand. This allows us to present a powerful enterprise-grade reporting tool to a larger audience. While it is possible to chain together these simple calculations to solve ever more complex problems, it becomes burdensome to manage such complex chains of calculation columns. Therefore, especially for complex and large reports, it is suggested to use the formula calculation.
Key Features
- Allow combining many calculations into one column. This reduces the number of total columns thereby reducing both the complexity to understand the report but also reduces the memory footprint of such a report at runtime.
- Multi-line text with line-specific comments. The comments can be embedded within the expression as needed.
- Nested IF Statements. The non-formula conditional calculation could only express one expression. Nesting Ifs allows for an easy expression of a decision tree of expressions - making complex reports easier to manage.
- Horizontal Summaries. By default, formulas will calculate using the summaries of its inputs in group and report summarization.
How to Create a Formula
A formula calculation can be created in many different ways:
- By selecting the 'Formula' calculation in the Calculations editor like other calculations.
- By using the 'Formula' menu item.
- By converting an existing non-formula calculation to a formula using the 'Convert to Formula' command.
- By converting all non-formula calculations in a report to formulas using the 'Convert all to Formula' command.
Note
Converting all non-formula calculations to formulas in a report will combine simple calculations into more complex formulas automatically. Please see Formula Conversion for more information.
Note
Not all non-formula calculations can be converted to formulas. Please see Formula Conversion for a list of calculations that cannot be converted.
Working with the Formula Editor
Available Items
The Available Items section contains the items that can be used in the formula editor:
- Parameters - visible if the report has parameters. Parameters will always read as string values in formulas. If a non-string value is desired (such as an integer), consider first wrapping the parameter in a Global Variable leveraging the Type Converter calculation.
- Variables - visible if the report has variables.
- System Constants - always visible and contains such things as the current date, current user, etc.
- Grid Columns - all columns from the report
- Functions - all functions that can be used in a formula. Note that there are calculations (such as table lookup) that cannot be used in a formula.
Operators
Below the editor are the list of operators to use to create expressions for assignments and conditions.
- Comment and uncomment lines in the formula. Useful to temporarily disable a section of the formula.
- Basic math operator including modulus and power.
- Wrap the selected text in parenthesis to control the order of a boolean expression
- Wrap the selected text in quotes to make a string literal.
- Basic operators to compare values when used in the If function. See Comparisons and Data Types for how the data type of a comparison is determined.
- Basic boolean operator (AND or &, OR or |, NOT or !) to help construct boolean expressions.
- Like operator that follows the syntax of the Visual Basic Like Operator. This operator supports matching a single character (?), zero to many characters (*), a digit (#), and ranges ([-list of chars-] or [!-not in list of chars-]).
Understanding Formula Syntax
The formula editor supports prompting with type ahead auto-completion to make it easier to build the formulas properly. When creating a formula, inputs will come first followed by options as defined in the individual calculations' documentation pages.
This includes...
Typing a column or variable name while in a formula allows that field to be selected through a tab or double click of the value.
Typing a comma after the required inputs have been entered in a formula will sometimes bring up the additional options to be included.
Note
A great way to learn the formatting of formulas can be to utilize the Formula Conversion on a standard calculation, thus seeing the syntax used for a formula that might be created again in the future.
If Function (Conditional)
The If function is a three part function: If (expression, true value, false value). If the expression is true, the true value is returned, otherwise the false value. Another If statement can be embedded in either true or false positions to construct a decision tree. The function can be formatted across multiple lines with embedded comments to make the statement easier to manage.
Comparisons and Data Types
When two values are compared with >, <, =, !=, and similar operators, the value on the left determines the data type of the comparison. Data Access Studio then attempts to convert the value on the right to that same type:
| Left value's type | How the comparison is performed |
|---|---|
| Date or time | The right value is converted to a date (if possible) and the two are compared chronologically. |
| Numeric | The right value is converted to a number (if possible) and the two are compared numerically. |
| Anything else (including text) | Both values are compared as text. |
Important
Because the left value drives the comparison, the order of the operands changes the result.
[Order Date] > <TODAY> performs a date comparison, while <TODAY> < [Order Date] performs
a text comparison - the two are not equivalent, and only the first is reliable for true date comparisons.
Text comparisons are alphabetical rather than chronological or numeric, and they are not case sensitive by default (case sensitivity is altered by the setting "Case-sensitive queries" in Preferences for All Users). Alphabetically, "10/1/2026" comes before "9/1/2026", and "9" comes after "10", so a comparison that unintentionally falls back to text can return results that look wrong without being obviously broken.
If the right value cannot be converted to the left value's type - for example comparing a numeric column to "N/A" - the comparison does not report an error. It simply evaluates to false (or true for !=). This makes an incorrectly ordered comparison easy to mistake for a data problem, so it is worth checking operand order first when an If function returns unexpected results.
Which Values Read as Specific Data Types
The following read as text values and will force a text comparison if placed first:
- Parameters
- System constants such as
<TODAY>and<NOW> - Quoted literals such as
"1/1/2026"
Putting the Right Value First
Lead with the value whose data type you want the comparison to use - usually the grid column.
| Expression | Compared as | Recommended |
|---|---|---|
If([Order Date] > <TODAY>, "Future", "Past") |
Date | ✔ |
If(<TODAY> < [Order Date], "Future", "Past") |
Text | ✘ |
If([Qty Shipped] > "10", "Bulk", "Standard") |
Numeric | ✔ |
If("10" < [Qty Shipped], "Bulk", "Standard") |
Text | ✘ |
If the expression cannot be reordered - for instance when both values are parameters or constants - first convert one of them to the required type in a Global Variable using the Type Converter calculation, then reference that variable in the comparison.
Note
Blank and null values follow the same rule. When the left value is null, it matches only
another null or <BLANK>; any other comparison against it evaluates to false.
Filter Function
Syntax: Filter(Value, Filter condition)
Return: Boolean
Parameters:
- Value: a literal value, a reference to a column, parameter, or variable
- Filter condition: Filter field syntax
The Filter function is a special function that is used to evaluate a conditional filter on a value. For example,
Filter([Order Date], <Period Range>)
Where
<Period Range> = >1/15/19:<2/14/19
will return true if the order date for the given row is within the specified user provided date range. Because the filter condition is evaluated against the value using the value's own data type, the Filter function is a convenient way to test a date range without needing to consider the operand order described in Comparisons and Data Types. Such a function would usually be used in the following if function:
If(Filter([Order Date], <Period Range>), "Order in Range", "Order out of Range")