Redefining Calculation Contexts

The calculation context is the data that a calculation takes into account to generate a result.

This means that the value given by a measure is determined by the dimensions used to calculate the measure.

A report contains two kinds of objects:

• Dimensions represent business data that generate figures. Store outlets, years or regions are examples of dimension data. For example, a store outlet, a year or a region can generate revenue: we can talk about revenue by store, revenue by year or revenue by region.
• Measures are numerical data generated by dimension data. Examples of measure are revenue and number of sales. For example, we can talk about the number of sales made in a particular store.

A calculation has an input context and an output context. You can specify neither, either, or both explicitly.

• The Input Context is the set of dimensions used to make the calculation.
• The Output Context is a set of dimensions that functions like a break in the calculation. It causes the calculation to output values as if they are calculated in the footer of a table break.

Determining the set of dimensions used to generate a measure is called defining the calculation context (or forcing the calculation context).

The input context of a measure or formula is the list of dimensions that feed into the calculation.

The list of dimensions in an input context appears inside the parentheses of the function that returns the value. The list of dimensions must also be enclosed in parentheses (even if it contains only one dimension) and the dimensions must be separated by semicolons.

Suppose that you have a report showing the Sales revenue by State and Year, with a section on State.

What if you want to show the maximum revenue by state?

You can do this by specifying the input context in a formula.

In this case, the formula is as follows:

 =Max([Sales revenue] In ([State];[Year])) 

The dimensions in the input context appear inside the parentheses of the function (in this case, Max), whose input context is specified.

The output context causes the formula to output a value as if it is placed in the footer of a block containing a break.

The report shown in the figure, Specifying an Output Context, shows Sales revenue by Year and Quarter, with a break on year, and the minimum revenue calculated by Year.

​What if you want to show the minimum revenue by Year in a block with no break? You can do this by specifying the output context in a formula. In this case, the formula is as follows:

=Min([Sales revenue]) In ([Year])

That is, the output context appears after the parentheses of the function whose output context you are specifying. In this case, the output context calculates the minimum revenue by Year. If you add an additional column containing this formula to the block, the result is as shown in the figure Minimum Sales Revenue by Year.

Output Context

You can see that the Min by year column contains the minimum revenues that appear in the break footers in the previous report.

Notice that in this example, the input context is not specified because it is the default context (Year, Quarter) for the block. In other words, the output context determines which revenue by year and quarter to output. In full, with both input and output formulas explicitly specified, the formula is as follows:

=Min([Sales revenue] In ([Year] ; [Quarter])) In ([Year])

Explained in words, this formula calculates revenues by year by quarter, then outputs the smallest of these revenues that occurs in each year.

What would happen if you did not specify the output context in the Min by year column? In this case, these figures would be identical to the figures in the Sales Revenue column. Why? Remember that the default context in a block includes the dimensions in that block. The minimum revenue by year by quarter is the same as the revenue by year by quarter simply because there is only one revenue for each year/quarter combination. ​