Using the Formula Builder

The Formula Builder in SAP CPQ is used to bring automation and accuracy to configuring product offerings, calculating complex pricing, creating tags, creating expressions, and so on.

An expression in CPQ is a combination of values, variables, operators, and functions used to produce a specific result. Expressions are the building blocks of formulas, created to automate calculations or set specific conditions during the CPQ quotation process.

A tag is used in a formula to establish dynamic pricing rules as it represents the unit price and quantity of each item.

The Formula Builder window is divided into two main sections. The top section contains drop-down fields used to build the formula. The frame at the bottom, labeled Formula, is where you form the expression, either manually or by selecting the elements provided in the top section.

The Formula Builder is accessed wherever a formula expression can be used. To build a formula, select the kind of data (attribute, value, sequence, or complex tag) you want displayed. Then, provide the specifics, such as an attribute, sequence, or the complex tag's body. The top section includes operators and context-sensitive options, which are further divided into three tabs: Variables, Sequences, andActions/Conditions. 

The Check Syntax button checks the formula to ensure that the tags have been properly assembled. Any errors are displayed at the bottom of the window. It's a good practice to check a formula by using Check Syntax before use to help ensure its proper formation. When finished, select the Update button on the bottom to insert the created formula into the property being edited. 

Formula Builder allows you to insert a conditional or Boolean logic statement into a formula from the Select Operator drop-down field. Select an operator from the field and select the update button to include it in the formula. In most cases, the operator is added using its full syntax, including all necessary parentheses. The user then fills in the parameters.

In the Formula Builder under the Variables tab, begin creating a formula by selecting an action/condition/variable. Then select Attributes from the options.

Attribute values can also be selected for the formula if necessary. Attribute value code, attribute cost or price, and attribute quantity are examples of attribute variables. 

Select the Insert Variable button to add the new tag to the formula.

Attributes can include the following values and variables:

Attribute Values

• Attribute Values:
  • Create formula by selecting an Action, Condition or Variable.
  • Select Attribute and Attribute Value.
  • Insert Variable to add new tag to formula.
• Examples of Attribute Variables:
  • Attribute Value Code.
  • Attribute Cost or Price.
  • Attribute Quantity.

The next tab in Formula Builder is Sequences, which provides serialization to part numbers. Sequences can also be used in a formula. Under the Select Sequence Operator menu, select the operator to include in the sequence. Then select the sequence from the Select Sequence menu. Select the Insert Sequence button to add the new tag to the formula expression.

The attributes, which display in the box, are limited to those selected for the associated product or category. If a certain object or attribute isn't available, make sure that it has been selected, check its syntax, or created for the intended formula expression settings.

Arithmetic tags allow calculations within the Formula Builder. There are two arithmetic tags:

• Eval
• Round

The EVAL Tag

The EVAL tag evaluates an expression composed of mathematical operators and VBScript functions. It's often used to compute an arithmetic computation. This tag initiates the VBScript engine, allowing all VBScript functions to be used.

Here's an example of using an EVAL tag with a nested IF tag to perform a conditional arithmetic computation:

<* Eval( [IF](<* IsSelected(Operating System.Linux 10) *>){<* Price(Operating System) *> * 0.10}{<* Price(Operating System) *> * 0.05}[ENDIF] ) *>

This formula combines an Eval and an IF tag to verify whether Linux 10 is selected for the attribute Operating System. If so, it multiplies the price of the Operating System attribute's selected attribute value by 0.1; otherwise, it multiplies it by 0.05. The IF tag is evaluated first, and the resulting fragment is evaluated by Eval, which returns the computed result.

Here's an example of using an EVAL tag to invoke VBScript functions on text:

<* Eval( Replace("<* Value(Color) *>", "Light", "Pale") ) *>

This formula returns the current display value of the Color attribute, replacing every occurrence of Light with Pale.

The Round Tag

The Round tag rounds a numeric value. The number of decimal places can be specified. Otherwise, the value is rounded to the closest whole number.

Here are three examples of using a Round tag:

<* Round(<*EVAL(13/3)*>)*> - returns 4

<* Round(<*EVAL(13/3)*>,2)*> - returns 4.33 

The Formula Builder provides a Visualize button that can ease formula creation. Select the Visualize button to display tag names in black and operators in blue. Each tag appears as a node and its arguments appear as child nodes. If one of these nodes is also a tag, it's displayed as a tree structure.

SAP CPQ formulas can involve many nested expressions. The ability to visualize the formula as a tree structure can be helpful.

Some available variables are too complex to be created with the Syntax Checker. The UI provides a constructor to help build and nest complex tags.

Insert these variables by select them from the list. A new button displays, directing you to a custom-built window for constructing a complex tag. Once you've finished building the complex tag, insert the tag into the syntax builder window to continue building the rest of your formula.

Some complex tags include the following:

• CTX tags that retrieve values from various parts of the system
• Table tags that retrieve a cell from a custom table
• List tags that retrieve a column from a custom table