Design Advanced Formulas Using Scripts

Create a data action before adding an advanced formulas step to it.

1. In a Data Action page, select (Add Advanced Formulas Step).

2. Type a Name and Description for the advanced formulas step.

3. In the formula designer section, choose the tab Script.

   A template of advanced formulas is displayed by default.

4. Before writing advanced formulas in the body, we recommend you first familiarize yourself with the general rules and the tips that can enhance your script's performance. For details, refer to Understand General Rules for Advanced Formula Calculations for Planning and Optimize Advanced Formulas for Better Performance.

5. Begin typing your script in the body.

   As you start to type, a hint list shows all available functions that match the text you've typed. A short help text description is automatically displayed for each function.

   Note

   If you have defined number variables in the script, you can enter @ to view a list of all available variables. If you have defined parameters in the data action, you can enter % to view a list of all available parameters.

   For more information about available advanced formulas and use cases, refer to About Script Formulas and Calculations in Advanced Formulas for Planning. To learn about the syntax and semantics of advanced formula scripts, check out the SAP Analytics Cloud Advanced Formulas Reference Guide.

6. Formula Templates: If you select a formula from the hint list, a template is automatically entered in the formula entry bar.

   Tip

   Instead of typing formulas manually, you can press Ctrl + Space to choose from a list of values that are valid for that location in the formula, type [d/ for a list of valid dimensions , type [p/ for properties or type " (double quotation) for a list of valid members. Then members will be filtered by their ID.

   If pressing Ctrl + Space doesn't display any values, make sure the hot key is not used elsewhere. For example, in some Chinese input methods, this hot key is used to switch language between Chinese and English. In this case, you need to turn off the input method for the hot key to take effect in the application.

   Tip

   Use the following methods to find and select text quickly:

   • To highlight every occurrence of a word, double-click the word or select it by clicking and dragging.

   • To select multiple areas in the script, hold the CTRL key while making selections. You can then type to change all the selected text.

   • To select a rectangular area of text, hold the ALT key and drag a box.

7. To add a single line of comment, you can start the comment with //. To add multiple lines of comments, you can enclose the comments in a block start by /* and end with */.

   Note

   If you switch advanced formulas from visual to script, annotations in visual editor will be converted to script enclosed in a code block started by /*! and end with */, for example /*! Annotations */. This special comment is also editable in text script.

8. To format your script, select the Format button.

   After the 2020.18 release of SAP Analytics Cloud, your scripts will automatically apply monospace font instead of the previous proportional font. If you still want to use the old format, you can select the (Undo) button to revert the font style changes.

9. After you complete the script, select the Validate button to validate the script. All the lines with errors will be highlighted. Select the error messages at the bottom of the page, and you'll jump to the corresponding line containing that error.

10. If there are no errors in the script, you can check the calculation scope for specific functions. This helps you get good performance and correct calculation results in an advanced formulas step.

    Check the scope by hovering over one of the supported functions.

    The Estimated Function Scope window shows how many members are included for each dimension, and where the scope was restricted:

    

    Checking estimated function scope

    For details about checking the estimated function scope and the supported functions and types of filters, see Optimize Advanced Formulas for Better Performance.

11. Once you are familiar with all the functions and want to hide the hints, the colors that indicate different types of objects, or the estimated function scope, select the button and deselect Help, Highlight, or Estimated Function Scope.