Removing the Confusion from Calculation Contexts

If you’ve read enough of my blog posts, you’re probably aware that I’ve been using BusinessObjects for a long time, and, during the early years, I was pretty clueless. One of the topics that had me completely stumped was Calculation Contexts. I attended the classes, and learned a lot, but could not grasp the concept of Calculation Contexts. So, I studied the training manuals, but it just wasn’t sinking in. So, I studied the user guides, but it still wasn’t making sense. So, I decided that I just wasn’t smart enough to understand this concept, and did my best to live without it.

It wasn’t until 2002, while attending the BusinessObjects User Conference that I finally got it. I was attending a session by my good friend Dave Rathbun. One of his topics was Calculation Contexts. Dave explained them in a slightly different way than the books. Suddenly the light bulb went on, and it hasn’t dimmed since. In 2004, I started teaching BusinessObjects classes, and I always taught Calculation Contexts the way I learned it, and it seemed to make more sense than what was in the books. So, my first piece of advice is, if you really want to understand Calculation Contexts, don’t read the books. 🙂

What is Calculation Context?

The bottom line is that all calculations in Web Intelligence, by default, happen in the context of where they appear on a report. The same formula, in different places, can give different results. Often, the default context is exactly what we want. But sometimes it isn’t, and it’s then that we need to understand how to manipulate the contexts.

Default Calculation Context

Let’s start with a simple example, using the infamous eFashion universe. Create and run the following query:

Next, section the report on Store name. Add a Sum to the Sales revenue column. Notice that, even though the formula for the Sum is the same in every section, it yields different results in each section:

=Sum([Sales revenue])

This is because the calculation takes place in the context of where it appears on the report. In this case, the formula is within a section, so it outputs a different value for each section.

Next, drag the cell with the sum, out of the block, and drop it next to the section header cell.

Notice that the results are the same as when the sum was in the block. This is because the formula is still in the context of the section, Store name, and therefore, outputs a different value for each Store name.

Next, drag the cell with the sum up and to the left, next to the Report Title cell, but hold down the Ctrl key before you drop it. This creates a copy of the cell.

This time, after dropping the cell, you will see a different number. The number you see now is the grand total for the whole report. Once you move the formula outside the context of a section, it returns results in the context of the whole report.

But what if you want to calculate the percentage that each store makes up of the grand total? You would need the following calculation: (Revenue for each store) / (Grand Total Revenue)

But how do you get the grand total to appear in the section? Well, before we answer that question, let’s talk about Calculation Contexts, and what makes them up.

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. More on defaults later. By the way, while Calculation Contexts are most often used with aggregate functions, they are sometimes used without functions.

How to use Calculation Contexts

Let’s try a couple examples to demonstrate how these contexts work. 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:

Next, we’re going to put an Alerter on the Sales revenue column, to highlight all values that fall below average. In order to reference the average of 2,799,016 from an Alerter, we will need to turn the Average into a Variable. So, select the cell that has the Average value in it, and click the Create Variable button on the Formula Toolbar.

Give the Variable the name, Average. Now we can create the Alerter. Select the Sales revenue column, and then click the Alerters button on the Reporting Toolbar.

In the Alerters box, click the New button, to create a new Alerter. Name the Alerter, Below Average. In the Sub-Alerter section, set the following conditions and formats:

Click OK, then OK in the Alerters box. At this point, the Alerter is applied to the Sales revenue column. But we don’t see any values highlighted in bold red. Why not? Well, it’s all about Calculations Contexts.

To see why this isn’t working, 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.

As I mentioned, 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. So what is the default? Well, in the body of a block, the default Input Context is the dimension(s) displayed in the block. And, 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. Click OK, and confirm that you wish to modify the variable. Now, not only is the Average variable giving us the same value on every row, but the Alerter has now started working.

OK, so now that we have this working correctly, 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. So let’s figure out why.

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. Add the following:

In([Store name];[Year])

So, the whole formula should now look like this:

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

Are these numbers correct? Well, if you want to validate the results, add another block to the right with Store name, Year, and Sales revenue, and see if the Maximum variable is showing the highest annual revenue for each Store. You will find that, yes, the results are correct.

Now, let’s take a brief look at the first report that we sectioned on Store name. If we want to display the percentage that each Store makes up of the total, we would use the following formula within the section:

=Sum([Sales revenue])/Sum([Sales revenue])In Report

The second part of the formula, with “In Report” as the Output Context, specifies that we want it to output one number for the whole report. Therefore, we are dividing the Sales revenue for each section, by the Sales revenue for the whole report. Format the cell as a percentage, and you’re done.

Using Other Context Keywords

So far, we have explored the Input and Output context using the “In” keyword. But there are more than just one keyword that can be used in Contexts. Let’s take a quick look at ForEach and ForAll.

ForEach can be used to add dimensions to the default context. For example, in the Maximum variable we just created, we specified both Store name and Year for the Input Context. If we had used “ForEach” instead of “In”, we would not have needed to specify Store name in the Context, as Store name is the default. Test this by creating another variable, called Max ForEach. Enter the following formula:

=Max([Sales revenue]ForEach([Year]))

Go back to the second report in our document, and add the new variable to the block as the fourth column. Notice that it gives the same results as Maximum. Use ForEach when you need the formula to be dynamic. If you add additional dimensions to the block, they will automatically be added to the Input Context.

ForAll is the opposite of ForEach. While ForEach adds dimensions to the defaults context, ForAll removes dimensions from the default Context.

OK, so let’s take a quick look at one more Context keyword: Where

The Where keyword allows you to add a filter to a formula. For example, if we wanted to split the revenue for each year into separate columns, we could create a variable for each year, using the Where keyword. Let’s try one.

Create a variable called Revenue 2001 as a measure, with the following formula:

=Sum([Sales revenue])Where([Year]=”2001″)

Add this variable to the existing block. In this way, you can easily filter a formula for one or more values of a dimension. Of course, this could also allow you to calculate the variance between two years. For example, if you wanted the variance between the 2002 revenue and the 2001 revenue, you would use the following formula:

=Sum([Sales revenue])Where([Year]=”2002″) – Sum([Sales revenue])Where([Year]=”2001″)

There are a couple rules when using the Where keyword:

  • Following the Where keyword, always use parenthesis for the condition.
  • In the condition, the value to the right of the equal sign must always be a constant, not a variable.

Conclusion

This post turned out to be much longer than I expected when I started it. I guess I got carried away. But I think that it’s very important to understand Calculation Contexts. Just remember, the Input Context says, “I need to take into the calculation, one number for every what?” And the Output Context says, “I want to put out one number for every what?” Of course, the more you practice it, the easier it will get over time. So, practice much, and let me know how it goes.

Advertisements

91 Responses to Removing the Confusion from Calculation Contexts

  1. ahmed says:

    awesome article, thansk for the tips

  2. Raj Namineni says:

    Michael,

    Good one. Really I never felt so comfortable with Input and Output contexts until this day.

  3. Alex says:

    Hello Michel,
    Please help newest Web Inelegance user.
    I have a WEBI report, that has 24 hour columns a cross with values for each day (separate row) in reporting period and for individual customer (separate row)
    Requirement is to add another column with average value for all hours that are not = 0.
    and highlight the greatest and lowest value in a row.
    The report is based on universe with Oracle 11g table with all source data.
    Thank you,
    Alex.

    • Hi, Alex. Web Inelegance? OK, that made me laugh.

      You issue can certainly be solved with Calculation Contexts. Of course, it’s too complex to try to resolve in a comment to a blog. However, for each calculation, remember to think of what values need to go into the calculation, and how you want the values output from the calculation. You might try posting your question on BOB (http://www.forumtopics.com/busobj/). You can have assistance from many experts there.

  4. goulart says:

    Passing a value for use within a report
    Using the function UserResponse you can pass a value for use within the report.
    In my company we are implementing a balance scorecard, and most of the measures used comes out of the legacy systems which are mapped into Webi Universes.
    The challenge was to put the goals into the reports so we could calculate the results.
    But, as the goals changes in a monthly or quarterly basis, to hardcode the goals into the reports would be a burden.
    So we came out with a simple solution that could help webi users in many other situations.
    We create a query prompt that returns the values of the desired goals. Responding to the prompt with something like” xxx;90.30;70.50” would give us two values : 90.30 and 70.50. The initial xxx stands as a comparison string that would be greater than any value existent that may exist for the dimension to be tested against.
    To clarify, I will give the example using the efashion universe.
    Create a query filter that goes like [Store Name] less than Goal were Goal is the name of a prompt. This filter will always evaluate true for the prompt value “xxx;90.30;70.50”, since all stores names begins with “e-fashion” which are lesser than “xxx”
    When you run the query, answer the prompt with the desired values. Remembering to start the prompt with xxx, and separating the values with some special character (In my case ;).
    Inside the report, create two sets of variables : One to receive the full value entered for the prompt, and the second the variables that would receive the needed values.
    FullText = UserResponse(“Goal”) and value1= SubStr(FullText;5;5), value2=(FullText;11;5), use FormatNumber to convert the result of value1 and value2.

    Note that this can be used to enter dates values too. Just use FormatDate instead.
    Hope it’s not very confusing, but I thing that I gave you the big picture. If there’s any doubts, please feel free to contact me.
    Sincerely,

    Rogerio Goulart

  5. Jason says:

    You just ended days of anguish for me. Thanks!

  6. mike says:

    Hi there, by any chance is there a way for us to sort of “view” the calculation context behind the scene applied on any measure in a report.

    thanks..

    • Hi, Mike. There’s currently no way to view the default calculation context in a Webi formula. However, if you know the rules, it’s easy enough to figure it out. The default context is the dimensions and/or details that are displayed in the body of the block.

  7. PN says:

    Excellent post Michael.

    I’m coming back to BO after not using it for some time. The description of context in the books did not help much but this post made things very clear.

    I come from an SQL background so I find comparing it to a ‘group by’ query helps me understand.

    I imagine how would I get the number doing such a query, then:
    Output context = would be the equivalent of the fields in the group by clause
    Input context = would be the bare minimum fields/dimensions I need in the table/query I’m querying

    So in your example where we want the yearly sales by store, a query would be grouped by store name and the table/query would have to contain (at a bare minimum) Store & Year (plus of course the measure sales revenue)
    So:
    Input context = Store Name & Year
    Output Context = Store Name

    Thanks again for this post! It was very informative.

  8. Dippy says:

    Thanks Michael,

    Thanks for this most informative and descriptive post on context operators.

    I was using Hit and trial methods to get my formulas work in the reports. My formula work after 4-5 attempts .. 🙂 .
    It seems difficult to understand these concepts which are the most important for a webi report developer.

    Surely your post cleared almost 80% of the confusion related to the context operater.
    I generally use combination of foreach and In operater, or forall and In operater. Could you please let me know if we can acheive complex formula only with Foreach/Forall…. Is the practice I am doing is wrong?

    • Dippy, thanks for the kind words. Foreach and Forall are certainly valid ways to create complex formulas, but they are not the only ways. You can create complex formulas using the In or When keywords as well. Foreach and Forall are useful when you want a dynamic formula. In is useful when you don’t want a dynamic formula.

  9. Noel Scheaffer says:

    I certainly have a ways to go before I feel confident working with this concept. However, this post helped me work through an issue I was having with a report.

    Looking forward to attending some of your sessions at BI2013.

    Thanks,

    Noel

  10. Natalie Stroud says:

    I’m kinda new to BO, so please pardon me if this is newbie question. Is there a standard naming convention around the difference between a report as in a WebI file and a report as in an individual tab within that file? I’m curious about the output context “In Report” and which of those two it refers to. Also, if I had, say, a tab for a summary of my data and a tab for the detail that the Summary is based on, could I create a variable with a aggregate function having a calculation context based on the fields on the Detail tab and call that out on the Summary tab? Or can I only use calculation contexts on the tab where the data is actually located?

    Thank you for any guidance you can offer.

    Natalie

    • Caralie Ringholz says:

      To further explain, there is no one field that is unique on this document. The identifers are in the tables (sql and universe) but not on this document as it’s not important.

  11. Caralie Ringholz says:

    I also would like to find the answer to the Summary / Detail pages. Here is an example of where I am getting the wrong counts on a sumary report tab:

    I have a Webi document that has several report tabs using data from 3 queries – mtd data, ytd data, and prior ytd data..

    The detail report tab ‘Counts’ the number of transactions – obtained in several ways and all three return the same value, which is correct:

    =Count([All Ytd].[Contribution Donor ID];All;IncludeEmpty) = 2282
    =Sum([v_All YTD Txn Counter 1]) =2282 (where [v_All YTD Txn Counter 1] = 1)
    =Count([All Ytd].[Contribution ID];All) =2282
    =Count([All Ytd].[Contribution ID]) =2251 (This is incorrect because there are some ID’s duplicated in the report).

    The summary report uses the same calculations but with different results:

    =Count([All Ytd].[Contribution Donor ID];All;IncludeEmpty) = 4566
    =Sum([v_All YTD Txn Counter 1]) = 1
    =Count([All Ytd].[Contribution ID];All) = 4566
    =Count([All Ytd].[Contribution ID]) =2251 (This is the same incorrect because there are some ID’s duplicated in the report).

    I’ve tried using calculation contexts but with no luck.

    • Hi, Caralie;

      I think I would need to see the report in order to figure this one out. Have you tried posting this on BOB?

      • Caralie Ringholz says:

        MIchael,

        Sorry, I can’t figure out how to post a screen print on your website so I am attaching a document here explaining the problem.

        Yes I have posted this issue on BOB but have received no responses.

        Thanks for your time, I really appreciate it.

  12. Caralie Ringholz says:

    Michael,

    Yes I have posted this issue on BOB but have received no responses.

    I can’t see how to post screen shots to this reply so I sent you an email with the document.

    Thanks for your help,

  13. Natalie Stroud says:

    For whatever it may be worth, we did find an answer to our question about creating a variable that would reproduce a calculation from a Detail report on a Summary tab. We needed to calculate the mean, median, and mode of the amount of time to resolve trouble tickets resolved within a certain timeframe. We have a field in the universe that tells us the number of business seconds it took to resolve each ticket, so we can convert that with a variable in the report to minutes, hours, or days as needed. On the Summary tab, our variable for Median ended up being set as follows:
    Median([Bus Hrs to Resolve] ForEach([Incident ID])) ForAll ([Incident ID]). Mode was the same using the mode function. I ended up skipping the Average function for Mean and just did a straight calculation there. I think I’m still a little unclear on why ForEach([Incident ID]) ForAll ([Incident ID]) is the correct calculation context for my Summary tab, but I’m glad to have at least found it.

    HTH,
    Natalie

    • Natalie, your formula is adding [Incident ID] to the default Input Context, but removing it from the default Output Context.

      • Natalie Stroud says:

        I’m still new at this, but I gather that you think that formula doesn’t make sense. Like I said above, it didn’t really make sense to me either – all I can tell you is that when I compared the results I got when using that formula in my Summary table, it matched exactly the results I got when I set up the median and mode calculations in my Detail table. If you can tell me *WHY* it gave correct results, now that would be useful. I hate having the right answer and not knowing WHY it’s the right answer, because then there’s nothing I can build on the next time around.

      • Natalie, I’m glad you got the correct results. However, without seeing the document, and understanding the data, I would have a hard time explaining why you correct results. I can explain what your formula does, but not why it works without more information.

      • Caralie Ringholz says:

        I’m sorry to say it didn’t help my report. I tried ‘=Count([All Ytd].[Contribution ID] ForEach([All Ytd].[Contribution ID])) ForAll ([All Ytd].[Contribution ID])’ and I’m still getting the count of only the unique Contribution IDs, not the total number (2251vs 2282)

      • Natalie Stroud says:

        Caralie: Have you tried doing a Count([All Ytd].[Contribution ID];All)? I believe that count by itself in your context does a *distinct* count (count of all distinct Contribution IDs where each ID is counted only once regardless of how many times it appears in the query.) Whether you might also have to bring in the For Each/For All syntax as well to pull the number onto your Summary page, I don’t know.

        Natalie

      • Caralie, try this: =Count([All Ytd].[Contribution ID] ForEach([All Ytd].[Contribution ID]);All) ForAll ([All Ytd].[Contribution ID])
        See if that helps.

      • Caralie Ringholz says:

        Thank you so much Michael! I tried the All in several places but not that one I guess! Now I need to re-read your article above to make sense of it. because that was just the beginning, there are 5 other sums / counts on the same page.

        I’ve been working with B.O. for a year now but with no formal training so I haven’t learned the tricks. If you know of any great manuals on the subject I would love to hear about it.

        Thanks again!

      • The best book I’ve seen on BOBJ is written By Cindi Howson, call BusinessObjects: The Complete Reference. It’s probably a lot more information than you need, but very well written.

      • Natalie Stroud says:

        Caralie:

        Another good BO book is from Schmidt Ink, Inc and written by Robert D. Schmidt. The one I like is called Creating Documents with Business Objects, which is geared for report writers working in WebI. They have at least one other book geared toward universe designers, and they’re good about keeping current with the latest releases of BO. With the report creation book, apparently you can access a web site that contains the sample data from the eFashion universe and work through some exercises. If you learn best by doing and you want a book that focuses only on report creation, it’s worth checking out. Other than that, Google is my friend, and I spend a good chunk of time searching the BOBJ board. I found the ForEach/ForAll trick in an article online, though not on BOBJ.

        We have the Cindi Howson book Michael referred to as well, it’s just for a slightly older version of BO than we have here. I have to pay a little more attention when I read that one because I only create reports, so if the section I’m looking at is about universe design, that’s not so relevant for me. But I’m sure you work with someone who maintains the universe if you don’t do that yourself, so it might make a good acquisition for your department.

        Good luck to you!

        Natalie Stroud

      • Caralie Ringholz says:

        Thanks Natalie I will check out those books too.

        Michael and Natalie –

        I got my first count to work using the formula you provided (ytd contributions):
        =Count([All Ytd].[Contribution ID] ForEach([All Ytd].[Contribution ID]);All) ForAll ([All Ytd].[Contribution ID])

        Now I have to count the previous year’s contributions (ytd2) and I used the same formula but am not getting the duplicate contribution ID’s included again.

        Here is the formula I am using for ytd2:
        =Count([All Ytd2].[Contribution ID] ForEach([All Ytd2].[Contribution ID]);All) ForAll ([All Ytd2].[Contribution ID])
        I am getting 2,169 instead of 2,695 which includes all duplicates.

        This is so confusing!

      • Natalie Stroud says:

        Caralie:

        Well, you have to understand the underlying structure of your data to make sense of this stuff, though it I find it can be challenging even when I *do* understand that part!

        To have a shot at answering your question, I’d have to know something about how your data is structured. Does the data come from the same table, but the only difference is what year the contribution date is? Or is there a separate archive table where you pull the ytd2 data from? Can I assume that all contributions have a Contribution ID?

        Then I’d want to know if you are using a single query or a separate queries to pull the data for the ytd and ytd2 calculations. If you are using separate queries, are you pulling the contribution ID for the ytd2 query? I know these are fairly basic questions, but I find it helps if I verify my assumptions!

        Let’s start with that…

        Natalie

      • Caralie Ringholz says:

        Natalie,

        I will try to answer as best I can.
        The data all comes from the same universe – not a separate archive table.

        Yes, all contributions have a Contribution ID. Each Contribution ID may have multiple records / transactions.

        There are separate queries – ytd and ytd2 where ytd is the current year and ytd2 is the previous year (although there is no requirement that they use the current year or even the previous year – the user can enter any year they want). The only thing different between the 2 queries is the date filter, all other filters are identical.

        I have detail report tabs – named the same as the queries – ytd and ytd2.
        There is also a summary tab which is the tab returning the incorrect count (although as I said earlier the ytd count is working but the ytd2 count is not).

      • Natalie Stroud says:

        Caralie:

        So if each contribution ID can have several contributions/records, what is the name of the field there can be several of?

        Natalie

      • Caralie Ringholz says:

        Natalie,
        Contribution ID can be duplicated. The different records can be things like multiple payments against a single pledge (EX: I pledge to give $5000 and I make 5 $1000 payments. There will be 6 records against the Contribution ID, 1 for the pledge and 5 for the payments).

        There are other examples but this is the most common. I need to return 6 – 1 for each record, not 1 for the contribution ID.

      • Caralie Ringholz says:

        I’m not sure how to answer this. In each of the two queries there are 13 fields, all of which can be duplicated. On the detail report tabs there are 10 fields all of which can be duplicated (I’m not showing all 13 fields from the queries).
        Contribution ID, Donor ID, and Hard Credit Amt are the only three fields that will always have data, but they can each be duplicated.

        I can’t see how to include a file or screen shot to show you.

      • Natalie Stroud says:

        Caralie:

        I can see enough to see that Calculation Contexts are definitely what you need here to get the number you’re looking for onto a Summary page, but I’m totally in the dark about why your ytd2 count isn’t working – seems like doing a Count All should do it. The closest we have to something like what you’re trying to calculate is that we have Incident tickets that have an Incident ID and they can have multiple Work Infos (notes the person working the request enters about what they’ve done so far to complete the request or what its current status is), but we’d never need to count those in a report. (At least, I HOPE we’ll never need to count those in a report, LOL) We mostly just need to count the number of unique incident IDs, either in a detail tab or on a summary tab.

        The only suggestion I’d have is to try different things – remove the ForAll statement, try adding one of your fields that is always populated to the Input Context (ForEach statement) in addition to the one for Contribution ID, etc. That’s how I learn sometimes – trying things until I get a different result, then trying to understand how what I did translated to the result I got.

        And just because a given field isn’t something your users want to see in the report output doesn’t mean you shouldn’t include it in your query – I add universe dimensions to queries all the time that I don’t show in the report output but do manipulate in variables I calculate, usually because the variable in question pertains to something the user DOES want to see.

        Other than that, I can only send vibes your way that Michael has an idea for you the next time he checks his blog. Sorry I don’t have more help for you. 😦

        Natalie

  14. Sujit says:

    Thanks Michael, you really made calculation context simple for me…like you even I have given up on it….but this post makes me try again.

    I am clear with the concepts now, but still bit unsure of which objects to include as context…guess that will come with practice and some hands on. Thanks 🙂

    Sujit

  15. Sorin Dudui says:

    Hi Michael,

    I am wondering if you can define contexts to variables used in Input Controls. Lets say that I have the variable

    Concatenation([Product_Group];Sum([No. of machines]) In ([Product_Group])).

    I create a ListBox Input Control from it but instead of the SUM of machines of each Product_Group, I get the sum of machines of the whole report.

    How does ReportFilter works? It creates contexts outside the report.

    Thanks,
    Sorin

    • Hi, Sorin. Sorry for the slow response. Try converting the sum to a string. Concatenation always works best with strings:
      =[Product_Group]+FormatNumber(Sum([No. of machines]) In ([Product_Group]);”#,##0″)

  16. Chris says:

    Thanks Michael,
    This was helpful for understanding calculation contexts a little easier!

    I was curious if there was a way to extend the ForAll context?

    For example If you had a table of year > Month > Measure
    Is there a method to calculate Sum(Measure “ForAll (Months < row month?))

    So ideally it would give me the sum of the measure for January in the january row, Jan + Feb in the Feb Row, … Jan/Feb/Mar/April sum in the April row etc?

    I haven't been able to do that myself but was curious if there is such a way using these context operators?

    Chris

  17. Mark Duffill says:

    Great explanation – I use calculation contexts a lot and this clarified a couple of things for me. Like, I never properly understood the difference between In ([Dimension]) and ForEach ([Dimension]), but I completely get that now and can see where ForEach would be useful.

    I’m also grateful to Rogerio Goulart for his trick to enter a user-defined parameter. I’ve often wanted to do this and thought it should be a standard feature. For example, for entering a value that is compared with or used by a variable.

    Very good – thanks.

  18. Bharad says:

    Hi, Quick question, what if we dont involve that key column in the report, its functioning only when the column is pulled into the report. For ex consider this, we have year, order_num and amount. I have to display year, count(order_num) and median(Amount) how can I do this without having the order_num column in the report, the in/foreach contexts are not helping here. Please share your ideas on this ??

  19. Thank you for removing the confusion. I’ve always used the trial and error method as well, now I hope I’ll get the right function a bit faster 😉 Very nice article.

  20. Gourav Mahto says:

    Wow.. Great post ..!! .. Thanks for removing this known confusion for almost every webi develoer . 😉 … Hatzz off.. 😀

  21. Jayesh says:

    Excellent post Michael.I had confusion since long about this..

  22. yhunal says:

    These informations are very usefull.. Thank you.
    I want to learn another thing. I wanna remove “All Values” section in every filters and i just want to show and list whatever I choose. And also, if I dont choose any data, no values will be listed.. Is it possible?

    Sorry for my english. 🙂

  23. Kirti says:

    Hi,
    I have a very silly query regarding an issue I am facing. For example my report data is as follows:

    Name Age Salary
    Adam 25 2500

    And if I add any other dimension which has more than one value then the salary gets repeated. For ex:

    Name Age Phone No Salary
    Adam 25 21212387 2500
    Adam 25 21212389 2500
    Adam 25 21212380 2500

    I want to display it like below:

    Name Age Phone No Salary
    Adam 25 21212387 2500
    Adam 25 21212389 0
    Adam 25 21212380 0

    I tried using break but it dsn’t add 0 value and keeps the last two salaries blank. Kindly help, I am new in webi reporting.

  24. Jeff W says:

    Michael,
    This page has saved me from insanity multiple times. Thanks for the info. You were our instructor years ago, and you are still teaching.

    Jeff

  25. Ben G says:

    Thank you so much for this post. You helped me find the solution to my problem after banging my head against my monitor for hours.

  26. Simon Bates says:

    Michael,

    This article has FINALLY helped it click for me – well done and many thanks!

    I am trying to see if I can use this method to help me figure out a report where I want to show if an order line item exists *earlier* in a report, in order that I can see how many line items are being delivered for the first time in a given order (if any). So what I want to do is something like

    count ([line items] in [(order)] where min [order date] in [line item] < [order date]

    however in this article you say the RHS of a WHERE cant be a variable?

    Can you think of a way of doing this?

    regards

    Simon

  27. erik says:

    im kinda blonde,so,

    =Sum([Sales revenue])Where([Year]=”2002″) – Sum([Sales revenue])Where([Year]=”2001″) works fine, but the same wouldnt work when you use [Store name] instead of [Year]?

  28. JessysBlessings says:

    I’m (relatively new) to BO, but been struggling with this concept for a year. Now i *GET* it. Thank you.

  29. Dan Anderson says:

    Hi Michael,
    Thanks for the informative article; funny how much easier things can be when explained correctly. I have a problem that I think can be solved with the proper use of context calculations, but I’m struggling to find that right solution. I’m merging 2 universes (one for SQL Server and the other for Oracle) in a WebI report. In my result set, one column (Query2.CPKLimit) gets the MULTIVALUE error (which actually I expect to see).

    Query1 returns a Tagname, SampleValue and Date when sample was recorded.
    Query2 returns a value for CPK Limit that is effective between a Begin and End Date for the respective Tagname (note: Tagname is a Revisionable object, meaning Revison A is in effect from 1/1/15 – 12/31/2015 and Revision B is in effect from 1/1/16 – 12/31/2018).
    What I want to do is apply the following logic:
    Only display one value for Query2.CPK Limit where Query1.SampleDate is between Query2.BeginDate and Query2.EndDate.
    Hope this makes sense, matters like this can be challenging to explain via writing.

    • Hi, Dan. If I understand correctly, you have the two queries merged on Tagname. So, you can display Tagname (merged dimension), SampleValue (Query1), Date (Query1), BeginDate (Query2), and EndDate (Query 2), all in one block. So, I’m thinking that a flag variable could do the trick:

      = If(Date Between BeginDate And EndDate, 1, 0)

      Then apply a filter to the block on the variable to only display rows where the variable = 1.

      • Dan Anderson says:

        Hi Michael,
        Yes I have 2 queries merged on Tagname, but I am not able to display data from Query 1 in the same block as data from Query 2. When I try to display the CPK value from Query 2 in the block with the results from Query 1, I get the MULTIVALUE error in that column. I recreated your variable, but it also throws the MV error (since there is more than one value in that field). Is it possible to wrap the variable in some sort of logic that evaluates the condition individually for each row from the database? If this helps to visualize the problem, I can export the database results to Excel so that you can recreate on your side. I can also send you the wid file based on those Excel files so you don’t have to create from scratch.
        Thanks for your assistance,
        Dan

    • Try creating a variable for CPK that makes it a Detail of Tagname. See if that helps.

    • Well, that’s better than #MULTIVALUE. LOL

      Feel free to send it to me. I can’t promise anything, but I’m willing to try. Send it to: Michael dot Welter at Claraview dot com (I’m trying to avoid bots that scrape email addresses from web sites).

  30. Steve says:

    Brilliant. Just Brilliant.

  31. Steve says:

    Simliarly a complete exlanation of the concept of “block” is lacking in all books. Specifically what is defined as a block once you have a master.

  32. Claire says:

    Hopefully I can get some help with this… Referring to the example in the article where there is a table for the e-fashion stores and their respective sales (https://michaelwelter.files.wordpress.com/2011/07/context_storename_block.jpg). How do you add a 3rd column that would return the average sale for each store? For example, suppose out of all the sales that occurred in the Austin store on average people spent $200. How can I get the 3rd column to return that $200 in the Austin store row?

    The article shows how to return the average off all the store’s grand totals. My question is requiring something a bit more granular.

    Thanks!

    • Hi, Claire;

      In order for Web Intelligence to calculate something at a finer granularity, it needs to have the granular data. So you would need to return in your query the amount of each sale in each store. For example, if there is an Order ID number, simply add that to your query, along with Sales Revenue, and you’ll get the total per sale. Then, to calculate the average sale per store, you would do something like this:

      Average( In )In

      In this way, you’re specifying that the calculation should take in one number for each order ID, and output one number per store.

  33. Abhishek says:

    Hi Michael,

    This article explains the context so clearly that a newbie can understand in the first attempt,but could you explain a lot more for “Where”.

    Regards,
    Abhishek

    • The “Where” operator works the same as it does in SQL. It simply allows you to put a filter in a formula. The filter, which follows the “Where” operator, is always wrapped in parenthesis. For example: Where ( = 2016)

      • David says:

        Hi Michael, Is there anyway to make the where dynamic? Example I have a crosstab with 3 years as columns and the shops as rows. The years are the results of 3 filters (CurrentYear, Year-1 and Year-2). I would like to use the filters in the where so I can calculate the increase pct between each year.
        Many thanks
        David

      • Hi, David;

        It used to be that the Where operator of a formula could only use a constant to the right of the equal sign. But that is no longer true. You can create a variable for each of those years, and use that variable in the Where of a formula. For example:

        [Sales Revenue] Where (Year([Sale Date]) = [CurrentYear])

  34. Joey says:

    Can my newly created variable be used to exclude data from the entire report?

    It’s still rolling up when I try to use it in a filter or input control.

    Formula:
    =Count([PartNumbers;Distinct) IN ([Sales Order])

    The variable works only when I put it into a block, not when I try to exclude all Sales Orders with part number count less than X

    • Hi, Joey;

      The way your formula is written, it has no Input Context, which means that it will use the default Input Context, which is the dimensions displayed with the formula in a block. But since you aren’t displaying it in a block, it will have no input context at all. That’s why it works in a block, but not outside a block. Try including the needed dimensions in the Input Context, and see if you can get that to work.

      • Joey says:

        Darn, still can’t figure it out. What is the “input context” part of this formula? I thought I was defining the input context with “In([Sales Order])”

        =Max([Sales revenue]In([Store name];[Year]))

  35. Joey says:

    Also, I really appreciate your help.

  36. The “In([Sales Order]) is the output context, since it is located outside the aggregate function. You can add it to the Input context ass follows:

    =Count([PartNumbers;Distinct IN ([Sales Order])) IN ([Sales Order])

    • Joey says:

      =Count(([PartNumbers];Distinct)In([Sales Order])In([Sales Order])

      This provides the correct calculation when placed into a column but as soon as I try to use it as an input control or filter for charts and the entire report, it rolls up again

  37. Jorge says:

    Hi, I have a measure call “year of service” and I want to create a new variable to calculate the average of year of service depending on the dimension user add to the block. Like said the user add organization unit so now the average of service will be by OU then they add country now the avg will be based on OU and country, in other worlds the average have to be dynamic based on the dimension added to the block. Thanks for your anticipated respond.

    • Hi, Jorge;

      Keep in mind that, by default, the Input and Output contexts are based on the dimensions included in the block. So, if you don’t specify an input context, for example, the input context will be dynamic, based on whatever dimensions are added to the block.

  38. Christine says:

    Hi Everyone – I am calculating commissions based on weekly targets on a store level. So I have a variable to calculate the different percentages based on target

    (STORE COMMISSION)=If[% to Plan]=1.1 And[% to Plan]=1.2 And[% to Plan]=1.5 Then 0.05

    But when I pull in the associates at the store and calculate their individual commission at the same store % for the week, I’m getting just 2% across all weeks even if the store commission shows a higher commission rate. Not sure what field I need to tie one report from the other (same query)

  39. Pete says:

    This has been of ernomous help. I am currently on a project which has to produc calculations and percentages of sections and breaks and totals. Thanks so much..

  40. Christine says:

    Thank you Michael! Fixed the issue!

    Have another question, I’m trying to a sumif basically where I have multiple weeks displayed in different stores but need to sum sales per week by employee and can’t figure out how to enter two criterias

    =Sum([Net Sales]) ForEach([Fiscal Week]);([Employee #)

    • Keep in mind, Christine, that if you want to output a value for each Week and Employee, you have to input those same dimensions, If they’re in the block, then they are input by default. But if they’re not, you will need to specify them in the Input Context.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: