Jan 04 2010
Calculation Context Part I: Overview
One of the hardest concepts to explain when introducing new folks to Business Objects is context.
I now imagine blog readers who have been working with the tools for a while have started nodding their heads in agreement, only to stop and say, “Hey, wait a second, is Dave talking about universe contexts or report calculation contexts?”
And the answer is, of course, yes. 
Both concepts can be quite complicated and take a while to fully understand. Since I have already written a number of blog posts about universe contexts I thought it was time to turn my attention to the report technique of calculation contexts. Note that this concept is used in Desktop Intelligence and Web Intelligence but not in Crystal. This post is intended to be an overview only. I have plans for a detailed post on each of the various context operators where I will go into much more depth. For this post my goal is to introduce the concept and provide some basic definitions.
Introducing Calculation Context
The default calculation context is defined by the dimensions that participate in a report block. It is this feature that makes the dynamic nature of measures possible. When dimensions are removed from or added to a block the context changes and the measure amounts also change. This is controlled by the projection function set on each measure by the universe designer.
However, a report writer sometimes wants a measure to be fixed at a particular value. Percentages are a really simple example. To calculate a contribution percentage I need to divide each individual value by the overall grand total. In order to do that, I need to know how to generate the grand total anywhere I want it. In order to do that, I need to understand how to use the various calculation context operators.
Context Operators
There is a simple (but powerful) set of context operators that I can choose from. I intend to briefly define each one in this blog post and then dive into more detail in future posts. As I mentioned when I started, 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
blocksection. 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.
With Desktop Intelligence we have the option of In CurrentPage which can be quite useful and unfortunately not yet available in Web Intelligence. I probably don’t have to explain how the “CurrentPage” total is derived. 
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.
Simple Calculation Context Example
It’s hard to fully grasp some of the subtleties of calculation context without good examples. For this post I will provide one simple but common example that I mentioned earlier: contribution percent. I will start with a simple block with one dimension (resort), one measure (revenue), and a grand total as shown here.
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?
What’s Next?
This post barely scratched the surface of the calculation context question. It provided a few brief definitions and one simple example. I have several other posts planned that will dive into much greater detail about this often confusing subject. First I plan to talk more about In versus ForEach versus ForAll and how they are different. Next I plan to cover input versus output context and when each should be used. As I write this post neither of those two are much more than an outline for now, so I don’t have an estimated publish date.
But they’ll get published. Eventually. 
Hi Dave,
Thanks for this excellent post, I am really looking forward to future posts on this topic.
Calculation contexts are often misunderstood and guessed at, even after years of experience with using them. Hopefully this will increase the understanding of all webi and deski users.
Hi Dave. If you were a DJ I’d be saying love the show!! Every time I need to use calculation contexts I have to go back to first principles and work through examples to aid my understanding. So more in-depth posts will be great.
Anyway, in your example above, you have no aggregate function and no brackets. My simple way of remembering syntax is that input contexts are within the brackets e.g. sum(measure in context) and output contexts are outside the brackets e.g sum(measure) in context. With this in mind, is the IN REPORT in the denominator technically the input or output context?
Thanks.
Hi Dave,
Thanks for choosing Calculation contexts as subject to this post.I just can’t stop myself mentioning that I was asked the difference between For Each and For All in my previous interview.I answered but will see how much of it is right by reading your next post…
I am looking for some stuff on Integra solutions but couldn’t find them, Quoram is popping up. I understand that Integra merged with Quoram but where can i find the materials.
-Jus
Hi, Grantie, thanks for your comment. The truth is you don’t have to use aggregate functions for the formula if the projection function is defined correctly. In the case above I left it out for simplicity. Input context is always inside parenthesis, so to answer your question the “In Report” is the output context.
Jus, the company Integra Solutions and the associated web site no longer exists. When I went to get a link to the downloads page on the Quorum site, it seems they have redesigned the site since I last looked. I searched, and there are pdf files still available, but they don’t seem to be indexed on a page anywhere. I don’t know what to suggest, other than to try using their contact page.
Is there a specific item you were looking for? If it’s something I created it’s possible that I could post it here on my blog for reference.
Hi Dave. Yes I understand the projection and that’s what I was getting at I suppose. Without it explicitly being there, I never know whether the SUM function (as defined by your object’s projection) would encompass the In Report within the input or output context i So I always end up explicitly putting the aggregate function in just so that I can understand whether I’m referring to the Input or Output context.
Sorry to drivvle on. I know in this example it’s neither here nor there, but it’s something that has bothered me and I’ve never got an answer to. So from what you’re saying your [Revenue] In Report is the same as Sum([Revenue]) In Report.
Goodness me. See why I struggle. And I’m trying to get my head around a fan trap problem at the same time!!
Great site. Thanks for sharing your brains.
Grantie, on any given row of a block, the default input and output context values are always “In Body”. If a value is on a footer of a break, then the output context is based on the structure of the break. To make matters more confusing when you do a Sum() projection the input rarely makes a difference. Suppose you have detailed row values of 1, 2, 3, 4, and 5. Suppose that 1, 2, and 3 are detailed values for “A”, and 4 and 5 belong to B, something like this:
If I do a break on the dimension value, I get this:
If I want a report total, it doesn’t matter if my input context is the dimension value (1 + 2 + 3 + 4 + 5) or the break value (6 + 9) as both results are still 15. Things get more interesting when averages or other operations are used though.
Hi Dave,
I always have to check with the notes when I have to deal with Input and output contexts. Could you tell me the difference between Input and Output contexts.
Thanks,
Jus
One is for input, and the other is for output.
In all seriousness, that’s a topic for the next blog post. It’s a bit much to cover as a comment, so stay tuned for more…
Hi Dave, I need to use calculation context in my report. I need the data into one report from four different universes which are at different grains/hierarchy..It would be great if your could post more details with examples about In ,For Each,For All,In Report,In Block and In Body calculation contexts..thanks in advance..
Hi, Yogesh, I do have plans to write more about ForEach and ForAll and so on. I have a post in draft status now that I need to finish. I hope you can be patient as sometimes things move slow.
Hi Dave,
You are always good with your post and it helps me a lot. I would love to see more posting on calculation context. A particular question I have is, Can we use dimensions which are not there in the block but available in the query to alter the calculation of the measure using extended calculation context? further if we can how we should be careful when there are multiple queries (dataproviders).
Thank you
Hi, Mathew, I have more posts in “draft mode” on this subject. I hope to get some of them completed soon. To answer your question, any dimension that participates in the Output context has to be on the block. A dimension that participates on the Input context does not have to be present.
Hi, Dave. Thanks for the post. I’m finding it really helpful. I have encountered a problem, though, which I believe requires the use of calculation context that I’ve been wracking my brain on for the past week or so with no luck.
Imagine we’re breaking down revenue by customers, but each customer could live in multiple states. The universe is built with one context containing our customers and measures, and a second context that joins customers to states. When I bring them into a block together, everything works out fine (imagine the first column is state, second is customer, and third is revenue):
A X 10
A Y 5
A Z 12
B Y 5
C Z 12
total: 27 (the sum of the three customers)
Now, if I remove the customer from the block, I get:
A 27 (which is correct)
B 27 (we would want this to be 5)
C 27 (we would want this to be 12)
t: 27
Likewise, if I put breaks on the states, I would get:
A X 10
A Y 5
A Z 12
total: 27
B Y 5
total: 27
C Z 12
total: 27
I’ve tried every combination if input and output calculation contexts that I could think of, and invariably get the above results. Any thoughts?
BTW, if it makes a difference, we’re on BOE XI r2 (hopefully moving to 3.1 later this year).
Thanks!
Hi, Darius, and welcome. What you have described is what I call “unbalanced data providers” as you have dimension values at different levels. State is at a higher level than Customer, and you are using customer to pull the two data sets together. I think if you read the following post you will see an exact description of your problem along with a solution.
http://www.dagira.com/2009/09/03/using-forcemerge-to-fix-unbalanced-data-providers/
Now for the bad news: The ForceMerge() function does not exist until XI 3.0. If you are working in Web Intelligence then you’re out of luck until you upgrade. If you are using Desktop Intelligence then you can try using the MultiCube() function in a similar solution.
Thanks. From what I read, that looks like exactly what I’m looking for. The only hiccup is everything in my example is coming from a single data providor (albeit with multiple contexts), but I’ll give it a try when we move to XI 3.0. In theory, I think it’ll do the trick.
Luckily my customers haven’t asked about it yet, and I don’t anticipate them trying to build a report using the data that would cause this issue. I’m just trying to stay one step ahead of them…
I have data below
Now in my report I want below dimensions and count of unique RiskIDs at the Entity level. as below. i.e. I want to extend the Count() function to include Entity in the input context.
Region Country Count(RiskIDs)
Asia India 5
Kindly let me know how to write the formula for above.
Thank you,
Poonam N.
Hi, and thanks for your question. From what I can see, this issue doesn’t require context. Simply create a new variable that combines Entity with Risk ID and then count the results.
while merging a vendor name only one value is duplicated .
example: vendor contains hfc, uhc ,and aetna from table A.claim vendor contains uhc aetna and cobra
when merging i got hfc
uhc
aetna
aetna
cobra
aetna is repeating twice in the merged dimension.could you please help me in resolving it.
Hi, this really isn’t a context issue. As mentioned elsewhere, this blog isn’t intended to be a source of personal support. You might want to try BOB or contacting technical support with your questions if they are not related to a post here. Thanks.
Hi Dave,
We were finding problems with %age calculation on a drilled report XI R2 SP4. The total we were dividing by always got filtered. XI 3 feature NOFILTER( [object]; drill) works in SP4 – this ‘drill option’ is undocumented in SP4 help – so previously we had settled for two data sources, but this is far better.
John
Is calculation context is used only for aggregation?
If not what are functions we can use?
pls give clear definition for calculation context
I taught last week Web Intelligence courses and among the most important things I think are input/output contexts and track data (and the new formulas).
I wish we had the capability in Web Intelligence to evaluate a formula in its contexts (as we could with old DesktopIntelligence).
Hey Dave,
I really appreciate your style of explaining the concepts.
This blog has really helped me a lot.
Cab you please throw more light on input and output contexts?
Thanks & Regards,
Girish Jawale
Dave,
I am trying to create formula context,
I have 3 measures to come up with.
measure 1 — PY Total Spend
measure 2 — Cy Total Spend
measure 3 —-CY Total Spend Total (till the end of the previous month)
I have been a ble to create the first 2, my problem is getting the 3rd one created.
Thanks for your help.
Hi, Sunil, you don’t have a context issue in your scenario. It seems you need a calculation that includes everything except for the current month. I would experiment with code that looks something like this:
=Sum(Spend) - Sum(Spend) Where (Month = Current Month)That will get you an overall total and then “back out” the amount for the current month, leaving a current year total up to the end of the previous month. That’s just an idea and not the actual syntax, as I am sure you realize.
Hi Dave
A nice, clear and concise summary of the context operators. My ques. is in regards to calculations using details of the dimensions? e.g.
qryData_dim userFile_dim
Date
Area
Zone Zone
GeographicId (detail of city)
Site Site
——————————–
NumCalls
DenCalls
Two calculations required are Date and GeographicID. However, calculation for Date is dependant on GeographicID as two Sites share a common GeographicID. e.g.
goodCalls = if (numCalls/denCalls) IN geographicID < 2% then numCalls
While the above example calculates correctly, the values do not transfer correctly upwards to Date level.
Any thoughts?
BR
Darren
Re-posting the dimensions…
qryData_dim–userFile_dim
Date——–none
none——–Area
Zone——–Zone
none——–GeographicId (detail of city)
Site——–Site
——————
numCalls—-none
denCalls—-none
——————————–
NumCalls
DenCalls
/Darren
Hi Dave,
I have a specific situation wherein you might be able to guide me. I have a webi report wherein I have breaks and section set on certain columns. As a part of calculation I am required to do average on certain columns and produce the report average by break, section and grand total average. Now when I apply average function inside the section on break column and section column the averages turns out to be right…..but when I need to display grand average of all the column values outside the section in another block ….the value just gets summed up….no average calculation takes place…as recommended I tried applying context operators In Report, In Section ……but to no success ….could you let me know if there is a way to achieve this..?
Hi, Thanks for the topic and usefull information about the context, i am having one simple requirement that need to calcuate the total ouside the section , say i have a section on the peoduct and inside the section there wile subproduct count and those subproduct will be dynamic based on the prompt values. once they are generated i need to calucated the count of product out side the section, i am able to get the cout inside the section, but i am not able to get the cout ouside the section, do you have any idea’s in this issue.
Thanks
Geetha
Darsh, can you please try to do a sum / count rather than using the average function?
Hi Dave.
I often enjoy bumping into your site whenever I’m googling along for some BO questions.
Are you planning of writing your next blog on forEach and forAll soon ?
These topics are as you pointed out quite difficult to explain, and also get familiar with.
Best Regards
Ingrid
Dave – what can I say…..thank you
Hi Dave, Thanks for this write up. I’ve never went empty handed after reading your blog. Always get an answer here:) Thanks again!
Resending it seems few of the formulas didn’t published correct..
Hi Dave,
I have hierarchy Division->Market->Center. I created section on Market dimension with sales$ as measure. I wanted to see sales$ at Division level not for each Market even though it is sectioned by Market. I tried using input and ouput contexts still doesn’t work. I tried using
=Sales$ ForEach Division
I get multivalue error, If I use
=Max(Sales$ in Division)
I get max of Sales$ out of all Divisions.
Any help on what calculation contexts or syntax to use would be appreciated.
Thanks,
Reddy
Hi Dave,
Can you please explain in detail about the issue which poonam has posted
“Comment by Poonam N April 15th, 2010 at 6:40 am “.
I wanted all the dimesions in the report and want 5 to be displayed in the total.
Can you please explain how to do it.
Thanks,
Sandhya
Create a variable that combines Risk ID and Entity and count the unique results. You would get
Sales-1001
Sales-1001
Sales-1002
Marketing-1001
Marketing-1001
Marketing-1003
Education-1001
Education-1001
When you count the unique values, you will be counting this:
Sales-1001
Sales-1002
Marketing-1001
Marketing-1003
Education-1001
That will get you 5.
Hi Dave,
You have explained it clearly.Would like to know more about BO.
Since I am new to this tool could to please advice me a few steps to get start with BO.
While reading your blogs always it gives some idea about BO.
Thanks a lot!
Regards
Femi
Hi Dave. Just wanted to extend the discussion to ranking. I’ve successfully used calculation contexts and ranking to show the top x customers, with the remaining customers grouped into “Others”. But when I place a break on this dimension, the ranking is reset within the break, meaning that each customer is now ranked as 1. I can’t see a way of telling my variables to ignore the reset on break. Have you cracked this one>?
This blog helped me a lot in understanding the basic concepts of Calculation Contexts. Thank you, Dave.
I so wish you had published some more blogs on this topic that were in drafts…
Thanks for this post and blog in general. It has really helped me with some tricky concepts that don’t seemed to be covered in books (especially the post on unbalanced data providers!).
After reading this I seemed to be using contexts well for most things but have hit a brick wall when using counts, in some circumstances. In particular using CountAll on a dimnesion object.
I wrote up the issue on BOB:
If you have any comments on that issue it would be great but in general I’d love to see part 2 of this post!
Has the 2nd part of this post been published yet? I’m looking forward to it.
Hi Dave,
Eagerly waiting for your next post on this topic. I hope you will take some to write on In,ForALL,ForEach contexts soon
waiting for the next post!!
Hi Dave,
I read your blogs very frequently. They are very informative and easy to understand. The way you explain the complex things, really make our lives very easy.
Eagerly waiting for your next posts on Calculation Contexts.