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.
All Example
The cumulative sales runs from top to bottom, equivalent to the default or Auto Mode.
Deepest Example
With Deepest option selected, the cumulative sales resets for every change in value of the next hierarchy up in the nested hierarchies, in this case manufacturers.
Custom Selection Example
With Custom Selection option selected, individual hierarchies can be selected that define the window over which the Context Calculations are evaluated. In this case, Manufacturer and Product Category are selected, defining the window of cumulative calculation to be manufacturer, resetting the cumulative value to zero on each change of Manufacturer value.
Tip: The chips in the Extra Details panel can be reordered. Swapping the order of the Manufacturer and Product Category chips will still evaluate over the window of Manufacturer, but each Product Category is accumulated individually:
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.
Example: None
In this example, the cumulative values calculation was used to create a column of cumulative sales. As the grid also contains a regular hierarchy, the values in Cumulative Sales accumulate by row, without recognizing the hierarchical structure.
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.
Example: Hierarchies by Level
In the grid below we have a date hierarchy by Cost, Sales, and Cumulative Sales (created using the cumulative context calculation.
As the hierarchy context settings were set to Level, the values of the context calculation accumulate in each row of the hierarchy, flowing from each level to the next.
The total for each parent row is equal to the value of the last row of its child level; 2008 has the same value as Q4 2008, and Q2 2008 has the same value as Jun 2008.
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.
Example: Hierarchies by Parent
In this example, the hierarchy context settings were set to Parent. Now the cumulative sales will accumulate in each row that shares the same parent level.
We see that only the direct children of each level accumulate to the value of their shared parent. Thus, Q1 2008 is equal to Mar 2008, but Q2 2008 is not equal to Jun 2008.
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".
Example: Ignore Totals
In this query, a context calculation was used to add a Median Sales column to grid (green highlight below). Totals were enabled for the grid, and disabled for the context calculation (red highlight).
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.'
