woensdag, oktober 05, 2005

Context operators in Business Objects (part 4)

Foreach and Forall revisited
=Average(<Sales revenue> ForEach <Quarter>) ForAll <Year>
What does this formula do ?
To understand it, first consider the following tables :
Table Year and Sales Revenue
Table with Year quarter and Sales Revenue

Imagine, we want to show the number 2,023,954 in the first table  (so we can compare the Sales revenue per year to the Average Sales Revenue per quarter for that year. With the standard calculations in Business Objects, such is impossible. This is the situation where you need Context Operators. When adding the formula =Average(<Sales revenue) to the table to the left, the result would be the same number as the column with the Sales Revenue itself, since in that table Business Objects can only consider that value to calculate the average. So we say the context of the average in that case is just the “sales revenue”. In case we wish to include the <Quarter> also into that calculation, we would need to include an input context : Foreach or In. Both will do the same in this case, the only difference is the way they work. Foreach is able to add dimensions to the calculation (key word is ADD) This means that in a Foreach, the dimensions already in the table are always taken into account. The formula would then become : =Average(<Sales revenue> foreach <Quarter>) This tells the average function to include the Quarter in the calculation together with Year which is already in the table. The “In” Operator can do the same, but then the function would have to be : =Average(<Sales Revenue> in (<Year>,<Quarter>)) this needs to be done because In ignores the dimensions already in the table. The resulting table :
Year Quarter and average with contextThis is what we call an Input Context. This kind of context operator is added inside the brackets.
If we needed to show the number at the bottom  of the above table, (3,032,267) then we would need an output context, since we want to show the average for the entire Year. the formula would then be the one I stated at the beginning of this article : =Average(<Sales revenue> ForEach <Quarter>) ForAll <Year>
This second operator is used in this case as the Output Context operator The same is possible with “In Report” as the output context. The resulting table :
Table Year Sales Revenue with Output context

That should be the end of this topic.

Did this information help you ? Look.. a 'Donate' button on the right side :)

3 opmerkingen:

MattyRobots zei

Binabik, I know you did this 5 years ago, but because you properly documented the syntax you have helped me greatly. Thank you.

Binabik zei

Glad that this information served you well :)


Guha zei

6 years later!! Excellent Explanation! Thanks!!