Using the Script Editor to Create an Advanced Formula Data Action

In the previous lesson, we explored the advanced formulas step in data actions and created a simple advanced formula calculation using the visual editor. In this lesson, we will take a closer look at advanced formulas variables, configuration and formula options, as well as scripting syntax and auto complete.

The script editor is used for more complex programming, however, it is possible to start in the visual editor to add context, variables, and basic configuration, and then switch to the script editor at any time.

Variables in advanced formulas can be used to store data temporarily while the script is running, to avoid calculating the value repeatedly. This is the traditional concept referred to as a Local Variable or Virtual Variable Member. The virtual variable member will be stored independently and cannot be added to a dimension like a real dimension member. After defining a variable member for a certain dimension, you can use the variable member to store values only for that specific dimension.

1. Write the definition after the configuration settings and before all the instructions.
2. Create a new name for a virtual variable member. Make sure that in the variable member definition it always starts with the prefix #.
3. Dimension Name is mandatory and must be an existing dimension in the current data action model. The version dimension is not supported. Measures are supported, for example: VARIABLEMEMBER #Total_Revenue OF [d/Measures]
4. A virtual variable member can only be used in DATA and RESULTLOOKUP functions. When inputting # in DATA() or RESULTLOOKUP(), a list of all available virtual members will be shown.

In the following example of advanced logic virtual variables, you can see that SumOfProducts is the temporary account for total sales revenue. Since the total sales are over 1000 (1500), the system calculates other income of 1500 x - 0.01 for each product.

Time Hierarchy: This configuration of fiscal year or calendar year governs the time hierarchy used in the advanced formulas. This setting affects the result of time/date commands, such as FIRST and LAST. It controls which period is selected.

Sign Flip: If the account sign flip option is turned on, all calculations consider the sign value (debit or credit) of each account according to the account type (LEQ, AST, INC, and EXP).

Unbooked: Unbooked data refers to cells with empty values. If turned on, all empty cells in the source data to be copied will be treated as cells with zeroes.

Time can be interpreted as YQM: Year | Quarter | Month or as FYQP: Fiscal Year | Quarter | Period.

This configuration governs the time hierarchy of the script. If this configuration is not defined, the default time hierarchy of the model is used.

In advanced formulas, all data is calculated in absolute value by default, regardless of the account type. This is important when working with the Account dimension, where certain accounts will have positive values while others will have negative values. If you want to calculate with the existing sign-flipping rules of Account dimensions, then enter CONFIG.FLIPPING_SIGN_ACCORDING_ACCTYPE=ON at the beginning of your script.

In the following example, the Memberset command is selecting data for revenue and cost and then writing the result to retained earnings. If the sign flip setting is ON, the correct value (40) is written to retained earnings.

In the following example, January data for profit centers PC1/2/3 is being copied to February. By using the Unbooked setting, the existing 500 value for PC4 can either be retained or set to zero (to match the source).

• [ ]: Dimensions, properties, hierarchies are enclosed in square brackets
• d: Dimension. Example: [d/Cost_Center]
• p: Property. Example: [d/Account].[p/ElimTarget]
• h: Hierarchy. Example: [d/Cost_Center].[h/H1], "All_Cost_Centers"))
• %: Variables. Example: %YearMonth%
• #: Unassigned. Example: since labor hours aren’t currency related, currency is unassigned:RESULTLOOKUP([d/Account] = "LHours", [d/Currency] = "#")
• " ": Double quotes are required when specifying dimension members.
• /: Comments

For more syntax options, refer to the following reference guide: Advanced Formulas Reference Guide

Additional information can be found in this SAP Community Blog, including using virtual members: Advanced Formulas – How they work

As you type, a hint list shows all available functions that match the text you've typed. A short help text description is available for each function. You can use Ctrl+Space to choose from a list of values that are valid for that location in the formula, or type [d/ for a list of valid dimensions and properties to use in the formula.

There are also formula templates for common calculations. If you select a formula from the hint list, a template is automatically entered in the formula entry bar.

Examples of how to use auto-complete:

1. Type M to access the command that start with the letter M.
2. Type [d/ to access the model dimensions.
3. Press Ctrl+Space to access members and parameters.

Calculation scope: Once a script is validated and free of errors, you can hover on a dimension in the data line to see what members are included in the scope of the calculation. In the example, you can see that Date is on line 4 of the script editor but it is the first line of the Calculation Scope. Lines 1-3 of the script editor do not count towards the calculation scope.

More information can be found on the SAP Help Portal at Understand General Rules for Advanced Formula Calculations for Planning.

When you create an advanced formula, you have 5 options:

1. Calculation will create an equation such as Target = Expression.

A calculation is divided into two parts: Target and Source.

• Target is the data range to which you plan to write data.
• Source is the data involved in the calculation.

2. Delete will prompt for a filter upon which to identify which cost center to delete, for example.

3. Condition will prompt for a filter upon which to create the if statement, and then you can add a calculation.

For example, if the company = US, then copy prior year data to the current year data +10%.

123456
MEMBERSET [d/Date] = (BASEMEMBER([d/Date].[h/YQM], "[2024]"))
MEMBERSET [d/Measures] = ("Amount")
MEMBERSET [d/Account] = ("Revenue")
IF [d/Company] = "US" THEN
DATA() = RESULTLOOKUP([d/Date] = Previous(12)) * 1.1
ENDIF

4. Repeat (loop) will prompt for a dimension to loop over, and then you can add a calculation, for example.

For example, calculate revenue amount with a 10% increase over the previous month:

123456
MEMBERSET [d/Date] = (BASEMEMBER([d/Date].[h/YQM], "[2024]"))
MEMBERSET [d/Measures] = ("Amount")
MEMBERSET [d/Account] = ("Revenue")
FOREACH [d/Date]
Data() = ResultLookup([d/Date]=PREVIOUS(1)) * 1.1
ENDFOR 

5. Comment creates an input field for explanations.

In the previous examples, you can see how the system creates formulas depending on which option you pick. Each one generates code that you can view in the script dialog.

In the following example, you can see an example of a formula used to calculate labor in the visual editor. You can also see the editing options available.

1. Duplicate
2. Annotate
3. Comment out
4. Remove

When using script to write advanced formulas, keep the following in mind:

1. Each line of instruction inherits the calculation results of previous lines of instructions.
2. Advanced formulas perform all calculations only with leaf-level members that don’t have any account formulas defined.
3. In a RESULTLOOKUP function, the function considers the most detailed values in the dataset, even if the formula doesn't specify them. For example, consider the following record set:

   Account	Product	Entity	Date	Value
   Quantity	Sparkling Water	Region	Dec.2025	210 (aggregate of all countries)
   Quantity	Sparkling Water	Germany	Dec.2025	50
   Quantity	Sparkling Water	France	Dec.2025	70
   Quantity	Sparkling Water	UK	Dec.2025	90

   You write a formula to add 20 to the Quantity Values for December 2025 and write the new values to January 2026:

   MEMBERSET[d/Product]="Sparkling Water" MEMBERSET[d/Account]="Quantity" DATA([d/Date]="Jan.2026")=RESULTLOOKUP([d/Date]="Dec.2025")+20

   The final result will add 20 to each detailed Entity value, not to the aggregated Region value:

   Account	Product	Entity	Date	Value
   Quantity	Sparkling Water	Region	Jan.2026	270 (aggregate of all countries)
   Quantity	Sparkling Water	Germany	Jan.2026	70
   Quantity	Sparkling Water	France	Jan.2026	90
   Quantity	Sparkling Water	UK	Jan.2026	110

4. A null value is treated as a non-existent value, while 0 is treated as an actual number. If the RESULTLOOKUP function returns a null value, advanced formulas do nothing.
5. Advanced formulas basically work with the fact record-set of the model. If you don't specify other dimension information and members via the MEMBERSET definition, the advanced formulas will take all members from the dimensions in the model.

SAP Analytics Cloud AI-assisted features enhance your planning and analytics workflows by leveraging generative AI-powered capabilities. You can use AI-assisted features that are integrated into the SAP Analytics Cloud interface to efficiently complete tasks in your existing workflows. AI-generated content appears instantly within the application, accompanied by visual cues and text that indicate it has been created by AI.

Once enabled by your administrator, the following AI-assisted features are available in SAP Analytics Cloud:

• SAP Analytics Cloud, AI-assisted data actions: When working in the script tab of an advanced formulas step from a data action, you can generate scripts or comments.
• SAP Analytics Cloud, AI-assisted commenting: Summarize comments on table data cells and within a comment widget, extracting key information and insights.
• SAP Analytics Cloud, AI-assisted calculations: When working in data analyzer, you can utilize natural language queries to generate calculation formulas in the calculation dialog.
• SAP Analytics Cloud, AI-assisted chart summary: This is an AI-assisted feature available with SAP Analytics Cloud, add-in for Microsoft PowerPoint. When working in a PowerPoint presentation, you can generate a summary on a selected SAP Analytics Cloud chart with the help of generative AI.

In this lesson, we focus on SAP Analytics Cloud, AI-assisted data actions.

When working in the Scripts tab of an advanced formulas step from a data action, you can generate AI-assisted scripts and comments. An AI-assisted data action makes the script writing process more efficient and simplifies the process of adding comments to document scripts.

Prerequisites: As well as AI-assisted features being enabled by your administrator, you must have the Execute permission for the Generative AI privilege to use the AI-assisted data action functionality.

AI-assisted data action script generation:

To generate a script:

1. Select a comment either enclosed by /* and */ or prefixed by // or write a new comment and select it.
2. Select the Generate Script button to generate an advanced formulas script.
3. Verify the results.
4. Select Accept or Reject.

   When you select Accept, the generated script becomes part of the whole script and is no longer highlighted. The comment Created with AI. Verify results before use. is kept. You can leave it in the script or delete it.

   When you select Reject, both the generated script that is highlighted and the comment Created with AI. Verify results before use. are removed from the script editor.

When you are generating script, pay attention to the following:

• If you refer to a specific member in the comment, the AI service will try to find the member whose description most closely matches your reference.
• If you want to refer to a specific member in the comment using its ID, we recommend enclosing the ID with double quotes .
• When you refer to a member in the comment, you don't need to explicitly mention the corresponding dimension the member belongs to. The AI service will find the corresponding dimension based on how the member is referred to in the comment. However, if multiple members across different dimensions share the same ID or description, we recommend including the dimension information in the comment to ensure that the AI service will pinpoint the correct dimension.
• We don't recommend using the generated comment as the basis for generating a script. The generated comment is intended to explain what the selected script does, but it may not cover all technical details like filters, calculations, and definitions. Therefore, trying to recreate the original script from the generated comment may result in a different script.

AI-assisted data action comment generation:

To generate a comment:

1. Select an advanced formula script.
2. Select the Generate Comment button to generate a textual description of the script.
3. Verify the results.
4. Select Accept or Reject. Comments are added or removed in the same way as the script generation.

   When you select Accept, the generated comment becomes part of the whole script and is no longer highlighted. The comment Created with AI. Verify results before use. is kept. You can leave it in the script or delete it.

   When you select Reject, both the generated comment that is highlighted and the comment Created with AI. Verify results before use. are removed from the script editor.

When you select a single line of script, the generated comment provides detailed insights, including specific filters and actions associated with the functions. However, if this line is part of a larger selection, the portion of the comment explaining it will be more condensed compared to when it's the sole focus of the comment generation. As the script selection grows in size and complexity, the generated comment tends to be more abstract. This approach allows you to understand the overall logic and intentions of complex scripts without being overwhelmed by details, which are likely clear or implied in the context.