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 familiar yourself with the general rules and the tips that can enhance your script's performance. For details, refer to General Rules and Performance Best Practices.

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 All Script Formulas and Calculations in Advanced Formulas.

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. Once you are familiar with all the functions and want to hide the hints or the colors that indicate different types of objects, select the button and deselect Help or Highlight.