Calculation Context

Calculation Context: alle calculations, by default, gebeuren in een bepaalde context, dwz waar ze staan in het rapport

Een zelfde formule, die op verschillende plekken staat, kan verschillende resultaten geven.
Vaak is de default context wat we willen, maar niet altijd

Components of Calculation Contexts
Every calculation in Web Intelligence has an Input and an Output context.

• Input Context: Determines what values are taken into the calculation. Think of it as, “I need to take into the calculation, one number for every __________?
• Output Context: Determines the output level, or dimensions, by which the calculation is output. Think of it as, “I need this formula to put out one number for every __________?

The syntax for Calculation Context is as follows:

=Aggregate Function([Measure] Input Context ) Output Context


Notice that the Input Context is inside the aggregate function, and the Output Context is outside the aggregate function.

If you don’t specify an Input or Output Context, Web Intelligence will use the defaults.

Voorbeeld Output context

. Add another report to the document that we have already started, and place a block on it with Store name and Sales revenue.

Select the Sales revenue column, and add an Average calculation. In the footer of the block, you should see the following:

Averige formula:    =Average([Sales revenue])

Add the Average variable to the block, as the third column.

The value that we would expect to see, 2,799,016, isn’t showing up. Instead we’re seeing the same values as we see in the Sales revenue column. So let’s explore why.

if we don’t specify an Input or Output Context, Web Intelligence will use the default contexts. And in this case, we didn’t specify any contexts in the Average variable.

DEFAULT CONTEXT

So what is the default?

In the body of a block, the default Input Context is the dimension(s) displayed in the block.
The default Output Context is the dimension(s) displayed in the block.

If you were going to calculate the average manually, you would need to know what the numbers are for each store, sum them up, then divide by the count of stores. In other words, you would need to take into the calculation one number for each store, and you would want to output one number for the whole block of stores.

Well, that’s exactly what we need the Calculation Contexts to do. Since the default Input Context is the dimension(s) displayed in the block, and, in our case, the dimension displayed in the block is Store name, the default Input Context is to take in one value for each Store name. So, the default Input Context is fine.

So the problem must be the Output Context. Again, the default Output Context is the dimension(s) displayed in the block. In our case, therefore, the default Output Context is to output one number for each Store name.
So, what is happening here is that we’re taking into the calculation one number for each Store, and outputting one number for each Store. Clearly, that isn’t very useful. What we really want is to take in one number for each Store (the default), and output one number for the entire block (not the default). So, we need to specify the correct Output context.
So, let’s open up the Average variable, so we can modify the Output Context. Click at the end of the formula, outside the aggregate function, and enter the following:
In Block
So the entire formula should now be:

=Average([Sales revenue])In Block

The “In Block” part of the formula tells the formula to output one number for the entire block.

Dit is wat we willen

Voorbeeld Input Context

we can remove the Average column from the block. Our next step is to add another variable to the block. This time, we want to display the most revenue earned by each store in any one year. So, create another measure variable, called Maximum, with the following formula:

=Max([Sales revenue])

Add this new variable to the block as the third column. Does it look familiar? Just like the Average variable, Maximum is just repeating the Sales revenue values. This can’t possibly be correct! You’re right. It’s not correct. Since we didn’t specify an Input or Output Context, the formula is using the defaults. Therefore, it is taking in one number for each Store name, and outputting one number for each Store name. What do we want it to do? Well, in order to know the most revenue each Store earned in any one Year, we need to know what the values are for each Store, for each Year. In other words, we need to take into the calculation both Store name and Year, not just Store name. And, we want to output one number for each Store name, which is the default, so we don’t need to specify an Output Context.

So, open up the Maximum variable, and click just inside the closing parenthesis, so we can specify the Input Context.
=Max([Sales revenue]In([Store name];[Year]))

Notice that this time, we put parenthesis around the objects used in the context. When you use a keyword, such as Block or Report as the Context, you don’t put parenthesis around it. But when you use objects, like Store name or Year, you always put parenthesis around them.
Click OK, and confirm that you want to modify this variable. Now, you see very different values.

Context Operators

There is a simple (but powerful) set of context operators that I can choose from.
the default context for a measure is based on the dimension values present in the block structure. I can alter which of those dimensions impact the calculation using one of the following three context operators.

• In is used to specify exactly which dimensions to include in a context. Other dimensions in the block are ignored. Adding / removing elements from the block does not impact this calculation unless a removed dimension was specified in the context. In that case a #MULTIVALUE error is displayed.
• ForEach is used to include a dimension in a context. The calculation context is still affected by other values in the block.
• ForAll is used to exclude a dimension from a context. Other dimensions of the block will still be considered. Adding or removing values from a block might change the value, but it will always ignore the ForAll items.

The operators listed above all require one or more dimensions as part of their syntax. However, calculation context can be specified by more than a set of dimension values. There are a series of report structure keywords that can be used as well.

• In Report sets the context at the report or “grand total” level. Any formula with these keywords for the context will return an overall total. Note that the total may still be affected by report filters.
• In Block sets the context for each  section. For years I have been waiting for this setting to be renamed to reflect how it really works, but it hasn’t. I suppose it’s for backwards compatibility. If there is only one block on a report then “In Block” and “In Report” are going to be the same. But when a block is broken up into sections, then this context will generate a total for each section value. For that reason I submit that a better name might have been “In Section” instead, but as long as I know what it does the specific word does not really matter.
• In Body is the standard default context for each row of data. 

Finally, these keywords can appear in several places in a formula. The most common are the “input” and “output” context settings, but certain functions (like RunningSum() for example) also have a “reset” context.

• Input context is used to determine the input values for the formula.
• Output context is used to determine the output scope of the formula.
• Reset context is used to determine when a running function starts over at zero.

Dimensions referenced in the input context do not have to appear in the block. Output and reset dimensions must appear in the block in order to function correctly. For many calculations the default input context works just fine. I will show an example where it’s crucial to understand input context later on. For now I would like to move on to a simple example






This block already has a total on it. If this were a typical spreadsheet I could reference the total value using a row/column address, but that doesn’t work in Web Intelligence. That means I have to use another technique to generate the grand total. I mentioned the solution earlier… all I have to do is create a simple formula that looks like this:

Here’s the formula again:
=[Revenue] / [Revenue] In Report
Remember that the In Report context projects the measure up to the overall report total. By using that calculation context operator in the denominator of my division I ensure that I will always be dividing by the grand total in this formula. The results?