Support for Variables and Expressions in External Source Models

You can use variables and expressions in External Source Model (ESM) Formulas and Maps. Variable and expression usage can simplify ESM maintenance. For example, let’s say you use a #service end# named expression with an EDATE function. Maintenance becomes easier because you can simply update the #service end# expression with a new value in a single place and there is no need to modify any of the expressions where #service end# is used.

In SpotlightXL, navigate to Model > External Source Model > Source Model. The Variable Table and Named Expression fields are populated.

Exploring the Variable Table

In the Variable table portion of the screen, enter variables to be used in the map to transfer data to a source model and the master. 

You can also use a variable expression such as "@curMonth@+1" or "@curMonth@-7" instead of the field names. The order of field names is a sort order in the ESM definition.

Information on each of the fields in the variable portion of the table is provided below.

Field Descriptions

Variable Name:

Variables must start and end with the @ symbol. For example; @StartMonth@, @FixedTerm@, and @PlanStart@. The maximum number or characters supported for the variable name is 120 characters. The @ symbol cannot be used in the name portion nor are the following special characters supported in the variable name: /."$*<>:;|?]+[]{}()#%&=`-_,'

Type:

You can use variables of type ConstantMonth anywhere where the ConstantDate type is expected.

When used in the ConstantDate context, the value is automatically converted to the first day of the month. To add a month, use a Lead operator. To subtract a month, use the Lag operator. An example is provided below.

Format:

The Format field is applicable for ConstantDate and ConstantMonth types.

Value:

Value is the constant value. Variable values can depend on other variables. Variable values cannot refer to fields or named expressions. To fully explain how variable values work, several use cases are provided below in the Exploring Variables section.

Exploring Variables

Variable Values Dependent Upon Other Variables:

In the example below, the @FixedTerm@ is a variable which is being used in another variable (i.e. @HalfTerm@). The @FixedTerm@ variable value is set to 12. @HalfTerm@ variable is using @FixedTerm@ and the result is 6.

Variables Used in Field Expressions:

Variables can be used in field expressions for constant and formula fields. For example, the DATE expression value is Month (which is 12), Day (which is 31) and the next year substitution variable.

Exploring Expressions

Expressions consist of a Name and Value as shown in the image below.

The Expression Name must start and end with the hashtag (#). The Expression Name cannot contain special characters and the hashtag cannot appear within the name. The following special characters are not supported: /."$*<>:;|?]+[]{}()@%&=`-_,' Expression Values are formulas/functions that use fields.

Examples of expression names and values are provided below.

Example of an Expression Name and Value:

Example of an Expression Value that Uses the @FixedTerm@ Variable

Examples Where Expressions Use Other Expressions:

Example of a Named Expression Used in an ESM Field Expression:

When using a named expression in the Expression field, you can specify its name only. If the named expression is used, no functions or operators are allowed.

Rolling Month Use Case

Let’s say you have the following expressions and you want to simplify this setup.

To do so, create a named expression called #df# and refer to it from the field.

Using the named expression, the result will look as follows for the Jan-19 row:

However Feb-22 has a lead of 1 and Mar-22 has a lead of 2. So, the result will look as follows:

Tips and Tricks

If you have several fields to populate as shown in the table below, the following trick can reduce the amount of typing.

Enter the first expression fully. For the rest, do not enter anything inside the curly braces. Ensure that there are spaces around the column as in the example below.