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:

  1. By selecting the 'Formula' calculation in the Calculations editor like other calculations.
  2. By using the 'Formula' menu item.
  3. By converting an existing non-formula calculation to a formula using the 'Convert to Formula' command.
  4. 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 int he If function.
  • - 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-]).

Special Notes on the If function

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.

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. 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")