Defining a Formula

You can recalculate the key figures in a structure using a formula. This allows you to obtain calculated key figures. You can include basic key figures, restricted key figures, calculated key figures, and reusable key figures in the formula definition.

1. If the query does not have a key figure structure yet, create a New Structure in the Rows or Columns by selecting the row or column directory using the right mouse button and choosing New Structure from the context menu.
2. If the structure does not contain any key figures yet, drag a (basic) key figure from the InfoProvider to the directory of the new structure.
3. Select the structure directory and choose New Formula from the context menu. The Create Formula dialog box appears.
4. You can enter a technical name and description for your formula here. If you do not do this, the formula is given the description Formula n, with "n" corresponding to the number of formulas per query.
   In the description, you can use text variables, which the user can enter at query runtime.
   • If you want to use one of the existing text variables in the description, choose Add Text Variable. The dialog box for choosing text variables appears. Mark the selected text variable and confirm with OK. The system inserts the selected text variable into the description. The text variable starts and ends with the character &.
   • If you want to create a new text variable, start with the character &. Using the key combination CTRL + space you can display the input help with all the variable names available in the system. You can also enter a new name. End the name with the character &.
5. Define the formula. You can use input help for this. To simplify the task of formula definition, you can select the required operands and operators for the formula from Groups. The system then inserts these into the formula definition with the correct syntax.
   1. From Groups, select the operands required for the formula definition. You can choose from all structure elements that you have assigned to the structure so far.
      Choose  Groups  Structure: Key Figures . Das System zeigt im Bildbereich Formula Elements die Kennzahlen Sales und Quantity. Sie markieren Sales und wählen Add.
   2. Choose the required operation for the formula definition. If it is a basic arithmetic operation, you can enter it using the numeric keypad on the right. You can select other operators from the Groups. You can choose from the following function types here:
      • Percentage Functions
      • Data Functions
      • Mathematical Functions
      • Trigonometrical Functions
      • Boolean Operators
      Select the symbol for dvision as operator on the numeric keypad on the right. In our example, we still need the second key figure Quantity as the divisor. We insert this as described above.
      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.
   3. Specify any numerical values you require for the formula by entering them on the numeric keypad.
      Note

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

   4. Choose Format Formula to format your formula. In this step for example, the system removes any unneeded spaces and inserts any line breaks required.
      You can also change the syntax highlighting for the formula. To do this, choose  Window  Preferences  BW Modeling  Query Formula Syntax Coloring , and configure the required colors, styles and fonts.
6. Confirm your formula definition by pressing OK. You are back on the Sheet Definition tab in the query definition.
7. Make any necessary settings for the properties of the formula on the right of the screen.