Customizing Context Calculations

It is possible to alter the logic of how a context calculation is derived and executed through the calculation editor. Since context calculations are driven by the report structure they are embedded in ("context"), the calc editor will let you adjust finer details of how to handle the context. This is above and beyond cosmetic adjustments such as editing and changing the properties of a context calculation, such as its name or format,.

For example, if a cumulative context calculation has been created, it is possible to set breakpoints on the cumulative totals, resetting them to the beginning when a certain window of results closes. For this reason, this type of logic control is often referred to as Window Functions.

Use Case Example

Consider this View, where the Sales Measure is calculated as a Context Calculation cumulative total through Years and Quarters:

The cumulative total runs from the top of the Cumulative Sales column through to the bottom, resulting in a final value which is the total of all the quarters across all years.

However, it is a common requirement to reset the cumulative totals at the beginning of each new value of Date year. Adjusting the Context Calculation Logic allows the cumulative total to be reset in this way:

The cumulative totals reset back to zero on the boundary between one year and the next. In effect, the cumulative totals in this instance are operating within the "window" defined by the outermost Hierarchy values in the View, or the values of the first Hierarchy defined in the Rows Drop Zone, in this case, year.

Editing Context Calculation Logic

The Context Calculation Logic Editor is accessed from:

  • The Query ribbon:

  • The right-click context menu on the Context Calculation chip in the Drop Zone:

  • The Custom Calculation section of the Measures panel:

Any of the preceding options will bring up the Context Calculation Logic editing panel.

Understanding the Context Calculation Logic panel

The Context Calculation Logic panel will differ in its content depending upon the level of complexity of the logic to be applied. The differing views are determined by the Compute Used drop down. there are three modes available: Auto, Drop Zone, and Specific Chip.

Context type

Auto Mode

In Auto mode, the default logic of Context Calculations is preserved, defined by the layout and context of the query.

  • Click here to learn more about how Context Calculations behave in their default form.
Drop Zone Mode

In Drop Zone mode, two additional panels appear, Drop Zones and Extra Details.

Drop Zones

The window over which the window over which the Context Calculations derived is defined can be rows and / or columns. The checkbox next to each can be selected and the option in the Extra Details panel will be applied to one or the other or both, depending on what has been selected.

Extra Details

Under Extra Details, the options on the drop-down are:

  • All: the Context Calculation will be evaluated over the entire result set. Equivalent to the default.
  • Deepest: the Context Calculation will be evaluated over the window of the lowest of the nested hierarchies.
  • Custom Selection: the Context Calculation will be evaluated over the window of the specified hierarchies.
Specific Chip Mode

In Specific Chip mode one additional panel appears, allowing you to specify which chips to select to define the window over which the Context Calculations will be evaluated irrespective of the Rows and Columns. This provides the greatest degree of flexibility and a very high degree of sophistication in how the Context Calculations are evaluated.

As well as selecting which chips play a part in defining the evaluation window, the order can be changed using the grab handles to the right of the selected chip. This again will redefine how the values are calculated. With three chips in play, there are a total of 15 different combinations of selection and ordering possible.

Hierarchies

Use the Hierarchy options to define the context calculation behavior for Regular Hierarchies. These options let you determine the method used to apply the calculation to the hierarchy levels.

Note: the Hierarchy options are not relevant for Attribute or Drill Path Hierarchies.

None

Choose this option if your query doesn't contain Regular Hierarchies. If the query does contain a regular hierarchy, you will need to define how to apply the calculation to the various hierarchy levels.

By Level

Constrain the context calculation by the hierarchy level. This means that the calculation will be applied to each level of the hierarchy. If we have three levels in the hierarchy, each element in level 1 will be compared all other elements in level 1; level 2 elements will be compared with each other; and level 3 elements will be compared with each other.

By Parent

Constrain the context calculation by the parent level of each hierarchy. Each hierarchy level will be compared to its shared parent; each element in level 2 of the hierarchy will be compared to its parent in level 1; each element in level 3 will be compared to its parent in level 2.

Ignore Totals

When adding totals to the grid, you may not want to see totals for the context calculation. In this case, enable "Ignore totals".

Multiple Context Calculations

It is also possible through the Context Calculations Editor to define separate modes of operation for each Context Calculation where there are two or more being used. In this example, cumulative sales is being evaluated over the Manufacturer window, while the standard deviation sales are evaluated over the Product Category window.'