View Formula Used for Calculation in Views and Reports

You can view how a value at a particular cell intersection is being calculated by viewing the formula in the formula bar. For example, for all cell values that are derived using a calculation, you can view the formula associated with the calculation directly in the formula bar, while all cell values that do not have any calculation associated with them, you will see the respective cell values in the formula bar.

The following image displays the formula in the formula bar of SpotlightXL.

How does the functionality work

While you are working on any Report or View, the application constantly validates each cell intersection and verifies if any formula is defined within the calculation that is mapped to the artifact. For example, if your calculation contains formula, aggregation, map, or some other step, the application validates all these steps, finds the exact dimension intersection based on the scope, and then retrieves the formula and displays it in the formula bar.

Let us take the following example to understand the details.

If you have a View or Report containing different details such as Topwear, Bottomwear, Footwear, and so on for different seasons, and you are using these details to calculate the total for the “Men” section, then you can view the formula associated with the calculation in the formula bar as shown in the image below.

In the example above, for displaying the formula associated with the calculation for the “Men” section, the application first validates the properties to check if the Enable Save property is set to Yes and whether a Calculation is mapped to this particular View or Report as shown in the image below.

Once the application identifies a Calculation, the application next verifies all steps available in the calculation and checks if a formula is available in it. In the example above, SubVarFormula is the formula associated with the calculation for the “Men” section as shown in the image below.

Next, the application validates the formula details available in the Formula property and displays the formula in the formula bar. In the example above, the application validates details such as Formula Design, Formula Members, Dimensions, and so on for SubVarFormula, and displays the formula in the formula bar.

This functionality is applicable for a View or Report where save is enabled and the artifact has a calculation associated to it. However, this functionality is not applicable for any View or Report that is for analysis or reporting purposes.

In Practice

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.

Formula Performance Optimization

We have improved the performance of formula steps calculations for all formula members. If the "Enable Formula Performance" flag is set to true, a new formula code will be applied, allowing you to calculate the formula reference for members based on specified keys. Formula creation with formula reference took longer for high-value dimension combinations. In addition, we improved the formula run-time by skipping a few overheads, significantly reducing the time and effort involved in the execution of formulas for separate formula members.

Limitations

1. 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.
2. 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.
3. If multiple formulas are configured for any intersection, then the application will display only one formula in the formula bar.
4. The way in which the formula is displayed in the SpotlightXL varies slightly with the way in which it is displayed in the Spotlight Web. The formula in the SpotlightXL starts with an additional =@SpotlightFormula in SpotlightXL.