View Formulas in External Source Model

In your External Source Model data, you can view the formula associated with a calculation at a cell intersection in the formula bar of Spotlight Web and SpotlightXL. If your External Source Model data has a formula in the form of an expression, you can view the resolved formula associated with that expression in the formula bar.

Let us take the following example to understand the details. In any Source Model containing a formula or a formula with an expression, you can view the complete formula in the formula bar of Spotlight Web or SpotlightXL. For example, in the following image, you can see two formulas configured for the ESM model, Total Team Count and Total Team Budget, where Total Team Count uses a variable @CurrentMemberCount@  in the formula, while Total Team Budget has an expression #TotalTeamBudget#  associated with it.

When you view this ESM model in the Spotlight Web or SpotlightXL, you can view the complete formula details including the resolved variable for Total Team Count in the formula bar as shown in the image below.

The following is the image in SpotlightXL.

Similarly, for Total Team Budget, the application validates the value configured for the #TotalTeamBudget# expression and displays the complete formula including the resolved expression in the formula bar instead of the expression name as you can see in the image below.

The following is the image in SpotlightXL.

How to verify formula fields on the External Source Model page in SpotlightXL?

1. In SpotlightXL, select the Model task and the External Source Model > Source Model subtask.
2. Select a model and navigate to the available Formula fields.
3. Verify the formula details in the Expression column. For example, variables, expressions, and individual formulas.
   

How to Validate Formula in the Formula Bar?

1. In Spotlight Web or SpotlightXL, select a View and navigate to any cell intersection that has a calculation associated with it.
2. View the formula in the formula bar.
   

Limitations

• If the Enable Save property is set to Yes and the Calculation on Save property is configured with a calculation, then the formula configured in the calculation will be displayed in the formula bar. For any other calculation where the Enable Save property is not set to Yes, then the formula will not be displayed.
• If the Enable Save property is set to Yes and the Calculation on Save property is configured with a calculation. Still, if the application runs a different calculation, then the formula displayed in the formula bar and the formula configured in the calculation will not match.
• If multiple formulas are configured for any intersection, then the application will display only one formula in the formula bar.
• How the formula is displayed in the SpotlightXL varies slightly from how it is displayed in the Spotlight Web. The formula in the SpotlightXL starts with an additional =@SpotlightFormula in SpotlightXL.

External Source Model Field Types, Expressions, and Formulas

External Source Models support the use of expressions and formulas in laying out the Source Model and in calculations.

• Data Types

Data Types

The following data types are supported.

• Text
• Numeric
• Date
• Formula
• Constant Text
• Constant Numeric
• Constant Date

Change Constant Field Values without Clearing External Source Model Data

You can directly change values for the Constant fields available in the External Source Model (ESM) without clearing the model data. For example, if you have four constant fields along with other fields in your ESM and you want to change the values for two Constant fields, then you can directly change the values in the Expression column for these constant fields without clearing the model. You can view all the updated values in the Constant fields Source Data Load.

In addition, you can view all the changes made to the Constant fields and Formula fields in the Audit Log, thereby easily tracking all changes made to the model data at one place.

Let us take the following example to understand the details.

In any Source Model containing fields of ConstantText, ConstantNumeric, and ConstantDate type, you can change the existing cell values with new values. For example, in the following image, you have a Source Model containing

Department Name, Department ID, Department Date, and Department Manager fields that are of type ConstantText, ConstantNumeric, ConstantDate, and ConstantText respectively.

You can change the value of any Constant fields based on your requirement and update the fields with new values.

Once you change the required values in your source model, you can create a new calculation for your ESM and run the calculation to view the changes in the Source Data Load page.

While creating a new calculation, you must select ExternalSourceFormula as the value in the Type column and select your source model as the value in the Name column. After creating the new calculation, you must Save and Run it.

Once you run the calculation, to verify the updated values you can navigate to the Source Data Load page. You can view all the new values updated for the Constant fields in the Source Data Load page when you enter values in the respective Data Load fields, and click Refresh.

You can also view all the new values updated for the Constant fields in the Source Data Load page without creating a calculation. You can click the Load Data option, and then click Refresh to view the updated values.

Whenever you update any value for the Constant fields, you can track all the changes made to your model in the Audit log as shown in the image below.

How to Change Constant Field Values on the External Source Model Page in SpotlightXL?

1. In SpotlightXL, select the Model task and the External Source Model > Source Model subtask.
2. Select a model and navigate to the available Constant fields.
3. Select a Constant field and enter a new value in the Expression column.
   
   
   Note:

   You can update values in the Expression column for multiple Constant fields.
4. Click Save.

   

How to Create a New Calculation for ESM?

1. In SpotlightXL, select the Model task and the Source Mode > Calculation subtask.
2. Select any model and specify the required calculation name in the Name field.
3. In the Type column, select ExternalSourceFormula from the drop-down list.
4. In the Name column, select your source model name from the drop-down list.
5. Click Save, and then click Run.

How to Verify Changes to Constant Fields in the Source Data Load Page?

1. In SpotlightXL, select the Model task and the External Source Model > Source Data subtask.
2. Enter the required values in the Data Load fields and click Refresh.

How to Track Changes Made to the Model?

1. In SpotlightXL, select Manage > Application Administration > Audit Log.
2. Click the required cell in the Details column and navigate to the formula bar.
3. Verify all the changes made to the values of the Constant fields.

Limitations

• You cannot add new rows to the existing model or delete existing rows without clearing the model.
• You cannot change the Field column values from the existing model without clearing the model.