Defining Formulas

You can recalculate the key figures in a structure using a formula. You can include basic key figures, restricted key figures, and calculated key figures in the formula definition.

Defining a Formula

1. If the query does not have a key figure structure yet, create a New Structure in the Rows or Columns of the Query Designer by highlighting the row or column directory using the secondary mouse button and selecting New Structurefrom the context menu.
2. If the structure does not yet contain any key figures, use drag a (basic) key figure from the InfoProvider to the directory of the new structure.
3. Select the structure directory and choose New Formula. The New Formulaentry is inserted, and the properties for the formula are displayed in the Propertiesscreen area.
4. Select the NewFormulaentry and choose Edit from the context menu (secondary mouse button). The Change Formula dialog box appears.
   Note

   You can also call the Change Formula dialog box from the Properties screen area by pressing the Edit pushbutton.

   You make the basic settings on the General tab page.

   You enter a description of the formula in the text field provided in the upper part of the screen.

You canuse text variables in the description.

You can also enter a technical name in the Technical Namefield.

The formula entry field is below the text fields. The area to the bottom left contains all the operands available for defining the formula. These are:

The structure elements that you have already defined for the structure

The cells that you have defined as exception cells

All form variables

On the right side of the screen, you see the functions that are available as operators:

Basic functions

Percentage functions

Data functions

Mathematical functions

Trigonometric functions

Boolean operators

Above these two areas, the symbols for basic arithmetical operations are displayed. To the right, there is a numeric pad.

To define a formula, proceed as follows:

• Choose the required operands and insert them in the Detail View field by double-clicking them or dragging them.
• Choose the calculation functions you want to use by either clicking the icons for the basic arithmetic operations, double-clicking the functions or dragging them.
• Select the numerical values for the formula by clicking the numeric pad.
  Note

  You can also enter digits, parentheses and basic arithmetical operations using the keyboard.

1. Define your formula using the operands and operators provided. With each step, the system checks the syntactic correctness of the formula. If the formula is incomplete or syntactically incorrect, the system underlines the error in red.

   The system does not check whether the formula makes sense semantically, for example, if it contains the same units. Semantic checks are performed with the Check Query function. You call this function in theQuery Designer Toolbar.

   Note

   If you want to use a variable that is not contained in the operands while defining a formula, you first need to create the variable. Highlight the Formula Variable entry and choose New Formula from the context menu. The Variables Wizard now appears. This guides you step-by-step through the process for defining a new variable.

   If you want to change a formula variable, select the variable and choose Edit from the context menu. The Variables Editor appears.

   Caution

   Note that when you execute a query, the key figures are always calculated as the same unit. This means that a formula is semantically incorrect if, for example, a currency unit (like USD) is added to a weight unit (like kg). If you want to calculate values regardless of the unit, use the Value Without Dimension (Without Units) function . SeeData Functions.

2. Enter a description for the formula.
3. Make any necessary settings for the properties of the formula in the other tab pages. SeeSelection/Formula/Cell Properties
4. Choose OK. The new formula is defined in the structure.

Editing a Formula

1. Highlight the formula in the structure and, from the context menu, chooseEdit.
   Note

   You can also call the Change Formula dialog box from the Properties screen area by pressing the Edit pushbutton.

2. Make the required changes to the formula definition or in the properties on the corresponding tab page.
3. Choose OK.

Defining Properties for the Formula

1. Select the formula in the structure. The properties dialog box is displayed in the Properties screen area.
2. Make the required settings for the technical name, description, display, number format, calculations, aggregation behavior and formula collision in the formula. SeeSelection/Formula/Cell Properties

Defining Inverse Formulas

If you set formulas for planning applications as input-ready (in the formula properties on the Planning tab), an inverse formula is required for every input-ready operand in the formula. The inverse formula makes it possible to recalculate the valued entered back to the input-ready formula's operand.

1. Select the input-ready formula and choose Create Inverse Formulas from the context menu.
2. By double-clicking on an inverse formula, you can call the Change Formula screen.
3. Define the rules for the recalculation.

More information:Input-Ready Query andDefining Inverse Formulas.

See also:

Technical Notes about the Formula Operators

Formula Calculation