Jan 04 2010

Calculation Context Part I: Overview

Categories: Calculation Context, Report Techniques Dave Rathbun @ 6:28 pm

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. :lol:

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 block 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.

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.

simple report block

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:

calculation context formula

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?

Finished report block

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. 8-)

44 Responses to “Calculation Context Part I: Overview”

  1. Comment by Jason

    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.

  2. Comment by Grantie

    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.

  3. Comment by Jus

    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

  4. Comment by Dave Rathbun

    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.

  5. Comment by Grantie

    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.

  6. Comment by Dave Rathbun

    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:

    A 1
    A 2
    A 3
    B 4
    B 5

    If I do a break on the dimension value, I get this:

    A 1
    A 2
    A 3
      6
    
    B 4
    B 5
      9

    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.

  7. Comment by jus

    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

  8. Comment by Dave Rathbun

    One is for input, and the other is for output. :P

    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…

  9. Comment by Yogesh

    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..

  10. Comment by Dave Rathbun

    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. :)

  11. Comment by Mathew

    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

  12. Comment by Dave Rathbun

    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.

  13. Comment by Darius Makaitis

    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!

  14. Comment by Dave Rathbun

    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.

  15. Comment by Darius Makaitis

    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… ;-)

  16. Comment by Poonam N

    I have data below

    Region Country Entity RiskID ControlID
    Asia  India     Sales 1001    2001
    Asia  India     Sales 1001    2002
    Asia  India     Sales 1002    2003
    Asia  India   Marketing 1001  2001
    Asia  India   Marketing 1001  2002
    Asia  India   Marketing 1003  2004
    Asia  India   Education 1001  2001
    Asia  India   Education 1001  2002

    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.

  17. Comment by Dave Rathbun

    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.

  18. Comment by ravi

    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.

  19. Comment by Dave Rathbun

    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.

  20. Comment by John McCulloch

    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

  21. Comment by gobinath

    Is calculation context is used only for aggregation?
    If not what are functions we can use?
    pls give clear definition for calculation context

  22. Comment by Andreas

    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).

  23. Comment by Girish Jawale

    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

  24. Comment by Sunil Patel

    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.

  25. Comment by Dave Rathbun

    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.

  26. Comment by Darren

    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

  27. Comment by 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

  28. Comment by Darsh

    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..?

  29. Comment by Geetha

    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

  30. Comment by Dave Rathbun

    Darsh, can you please try to do a sum / count rather than using the average function?

  31. Comment by Ingrid

    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

  32. Comment by Ferdie

    Dave – what can I say…..thank you :-)

  33. Comment by Mathangi

    Hi Dave, Thanks for this write up. I’ve never went empty handed after reading your blog. Always get an answer here:) Thanks again!

  34. Comment by Reddy

    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

  35. Comment by Sandhya

    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

  36. Comment by Dave Rathbun

    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.

  37. Comment by femi

    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

  38. Comment by Grantie

    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>?

  39. Comment by Isha

    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…

  40. Comment by Peter

    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!

  41. Comment by Dopple

    Has the 2nd part of this post been published yet? I’m looking forward to it.

  42. Comment by Tej

    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

  43. Comment by AB

    waiting for the next post!!

  44. Comment by Thomas

    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.

Leave a Reply

If you want to include formulas or code in your comment, please read my Tips for formatting comments first. Tags you can use are listed below.

XHTML: You can use these tags: <a href="" title=""> <acronym title=""> <blockquote cite=""> <code> <em> <strike> <strong> <sup> <sub> <u>

Confirm submission by clicking only the marked checkbox:

 **             

Please remember that comments that are not related to this blog post may be ignored or deleted without notice. If you're looking for help on a topic you have already posted on BOB then please do not repost your question here.