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

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

  45. Comment by Arjun

    Hi Dave,

    I restared to read all your blog posts since wake up.

    I should complete, infact grasp all the BO things effectively.

    Thanks.

  46. Comment by John

    Hi Dave,

    I’ve recently published a few reports using the ForceMerge() function which works great in the column. The column I have this applied to has a filter on the first report and a different filter on the second report. The third report combines the two. The third report total is correct, but the first and second report column totals are not … it displays the whole report total for the ForceMerge() field.

    What am I missing? I tried: =If([Provider Type Group] = “Physician”) Then Sum([fmRTS]) Else 0 but this still gives the whole report total, not just the total where = ‘Physician’.

    Thoughts? Any help is appreciated.

    John

  47. Comment by Indrani

    Hi Dave,

    Your blogs are very helpful and making me understand the concepts well.
    This blog is also well written. I am eagerly waiting for your detailed blogs on for each and for all.

    Thanks

  48. Comment by Karen

    Hi Dave, I’m in desperate need of your expertise. This #multivalue has me stumped.

    I have a report where I am calculating the amount of time that it takes to resolve a service request, in weeks.

    So I have a variable that calculates weeks. the example is for those that take 0-4 weeks.

    Variable name Count0-4
    =If[Weeks]<5 Then Count([CompletedInstallTurnaround].[SR Number])

    I then also have a variable that counts how many records were in that count.

    Variable name Total0-4
    =RunningSum([Count0-4])

    This gives me a correct total of how many service requests took between 0-4 weeks.

    I'm trying to display the variable Total0-4 on a second report and I get #MULTIVALUE

    I have then tried to use the max function and the last function

    =Max([Total0-4])
    =Last([Total0-4])

    I've also tried adding "in Report" to the variable above.

    I still get the error #MULTIVALUE on each one of them.

    How to I get the total from the first query to display on the second query?

  49. Comment by Dave Rathbun

    Hi, Karen, your issue is probably because you’re referencing a dimension object in an “If” statement. If that dimension object – [Weeks] in your case – is not in the block, then the measures roll up (project) beyond the level of Weeks and therefore are no longer in the same calculation context. There are too many weeks, thus you get a #MULTIVALUE error.

    You might try creating a couple of variables to address this. First, create a flag variable with a syntax something like this:

    =If [Weeks] < 5 Then 1 else 0

    Then use this flag in the “Where” clause of another variable, like this:

    =Count([...]) Where ([Flag] = 1)

    That’s not the exact code, but you might see where I’m going. By using the Where clause you can reference something that isn’t in the block, which sometimes allows you to work around a multiple value error.

  50. Comment by Karen

    Dave, that worked perfect. I’m so happy I found your site. I’ve found so much helpful information. I had resorted to putting the calculations in the universe which is not what I wanted to do. I can pull those back out now and do it the proper way.

    Thanks Again.

  51. Comment by Sarah Phillips

    Hi Dave,
    As an ex BOBJ employee and BOBJ trainer I do find your explanations very easy to understand – and although a lot of what you have here i know, as you say – there is always something!!

    Thank you for your sharing!!

  52. Comment by OS

    Hi sir Dave Rathbun
    can you please demonstrate how to create dynamic measures?
    i want to calculate difference between revenue of any two year from available 3years with only 1 measure and display the difference in the other column.

    Thanks in advance for giving your precious time.

  53. Comment by Dave Rathbun

    Hi, this isn’t really a calculation context issue, and I have seen you have posted the same question on BOB which is probably the best place. Thanks.

  54. Comment by OS

    Hi sir
    That isn’t me , i saw that post but did not work.

    please help.
    waiting for your reply.

    Thanks

  55. Comment by Dave Rathbun

    As I said, it’s not a calculation context issue, and therefore isn’t really appropriate to address in comments on this blog post. If you can provide additional details in the topic listed, what you have tried, how your situation is different, and what you ultimately need, I suspect you’ll get better results. Thanks.

  56. Comment by Mick Arundell

    Hi Dave,
    I always find that your posts improve my knowledge and often my product and this post, about calculation contexts is one that I’ve found most useful.

    However I have a continuing problem with contexts for median and other percentile functions. I am easily able to test the results by pushing the source out to excel, sorting and then finding the middle row so I know what I should see and can compare with what I get.
    When I use median function the results for each row in a block are correct but when I try to get the median for the whole block in a table footer the result has never been correct. What context should I use to fix my mistakes?
    Thanks in advance.

  57. Comment by Dave Rathbun

    I have never experimented with the median function that much, and I don’t think I’ve ever tried to use it with a context operator. Normally the footer context is whatever the break (or breaks, if multiple) are. If I have a block with Resort, Year, and Revenue, and I break on Resort, then the footer context is In ([Resort]) as you probably know.

  58. Comment by Glenn

    Dave, you come highly recommended by members of the Bus Obj Boards. I desperately need your expertise.

    I need to create a report that is a top 10 ranking (which has been completed) but the report is too large to export to Excel or CSV.

    The crosstab report contains items on the left and individual store numbers across the top (each store number also belongs to a “group”). The data is the number of cases each store has sold since the beginning of the year.

    My real problem is that I can get the report reduced down to an exportable (manageable) amount of data by using a filter that will limit the results for that tab to show only the results for a particular “group” of stores, but each time I use the filter, the order re-sorts based on the sales of that “group” of stores selected instead of using the grand total.

    I need to measure the items against the grand total because we are trying to show distribution voids. I can not get it to reflect the grand total, only the individual group total.

    Any help on this would be appreciated.

  59. Comment by Mani

    Hey Dave ,
    If I want to calculate the grand total of revenue for three years where there is section break in a year and break in a state how can I do it

    Thank you

  60. Comment by Dave Rathbun

    A grand total can always be generated using the context “In Report” in the formula.

  61. Comment by Dave Rathbun

    Glenn, you can use the NoFilter() function as part of the formula to get the grand total even after you have applied filters to the report.

  62. Comment by Chakri

    Awaiting for the sequel of “Calculation Context Part I: Overview” :)

  63. Comment by Edwin

    Hi Dave,
    I’m triying to implement a margin of error and raise a flag if that margin is pass. I implemented this within Webi but the user will want to filter by flag ( Y / N). I created a variable that will hold that flag but when I use it as a filter, it will only display one value Y, not N; When the user selects that only value, Y, there’s no data to display. I think Im missing something but cant figure what it is…Any advise will be greatly appreciated. (Thanks)

  64. Comment by Dave Rathbun

    Can you post a few details about the data and the formulas you are using? I suspect that what is happening is your numbers – without context – are being rolled up. The “Y” is then being calculated at the top “grand total” level, resulting in a single value. More details would help clarify your question.

  65. Comment by Edwin

    X & Y are columns that sum up values. Those columns are in a table with the flag variable.
    Flag variable : If(Abs(x – y) > Abs(Margin of Error) Then “Y” Else “N”

    The above variable is working fine but when I set the Flag variable as a filter in the report. It would only show Y and when selected, no data is displayed. I hope this helped.

  66. Comment by Edwin

    Data exaple:
    Error Margin: 0.03

    X Y F
    1.1 1.2 N
    1.3 1.9 Y

  67. Comment by Edwin

    Any thoughts? Webi’s filters cant use contexts even if you set them as dimension.

  68. Comment by Satya

    Hi Dave,
    I have a report which will display top five projects and their revenue percentage. Percentage is of a project revenue is calculated as project revenue/All projects revs.
    Here i m not able get the All project revenue in each record to calculate percentage. Could you pls suggest.

    Sample data:
    project rev percentage
    1 500 (500/2000)*100=25%
    2 400 (400/2000)*100=20%
    3 300 …..
    4 200 ……
    5 100 ……
    Total of top five= 1500
    Total of all projects = 2000 (i calculate using formula: sum(nofilter([proj_rev])))

    thanks in advance
    Satya

  69. Comment by Dave Rathbun

    You need an “In Report” somewhere to get the overall total. Also sometimes I have to play with the NoFilter() function and decide if it works better as Sum(NoFilter()) or NoFilter(Sum()) instead.

  70. Comment by Chakri

    @Satya,You can use this formula “[Measure]/(Sum([Measure]) In Report)”

  71. Comment by Maarten

    Hi Dave,
    I would like to exclude a particular object in the context (file) and than take the sum with a different object as output (cons) and than sum again on file level. I’ve tried every combination of ForAll and ForEach and even tried something with In, but it doesn’t seem to work. I’ve also considered using 2 different queries such that the cost is gathered on cons-level, but when using forcemerge() the cost of a consignment is only used for one file that the cons is on. I hope you understand my question. Below an overview of the data I have in my report and the results I would like to retrieve.

    Data overview

    Cons 	file	Cost	Cons cost
    1	A	10	15 =(sum([Cost] forall ([file])) foreach ([Cons])
    2	B	20	45
    3	B	30	30
    1	B	5	15
    2	C	25	45

    Required summary overview:

    file	Cons costs
    A	15
    B	90
    C	45

    Could you please help me with this problem? Or is this not possible?

  72. Comment by Dave Rathbun

    I thought I followed your question, but then when I looked at your sample data I got confused again. How many data providers do you currently have? Is your final output going to have 3 rows (as in your second example) or 5 (as in the first)? Because based on the 3 rows, it seems like you can simply create a block with “file” and “Cons costs” and based on the 5 rows of raw data you showed, the cost should naturally roll up when you remove the “Cons” object. Or is this the final output you’re looking for?

    Cons 	file	Cost	Cons cost
    1	A	10	15
    2	B	20	90
    3	B	30	90
    1	B	5	90
    2	C	25	45

    If what you’re looking for is what you posted, I created your sample data in a spreadsheet, created a Webi report, and generated this output:

    Cons	File	Cost	Test
    1	A	10	15
    1	B	5	15
    2	B	20	45
    3	B	30	30
    2	C	25	45	

    My formula was this:
    =Sum([Cost] ForAll([File])) In ([Cons])

  73. Comment by Maarten

    Hi Dave,
    Thanks for your quick response. I would like to solve my problem without using an extra data provider. Meaning that all information is available in one data provider. Currently, my raw data looks like:

    Cons file Cost
    1 A 10
    2 B 20
    3 B 30
    1 B 5
    2 C 25

    After creating a variable called “Cons cost” I can created the following table (same as raw data but including the new created variable. This variable calculates the total cost for each consignment.

    Cons file Cost Cons cost
    1 A 10 15
    2 B 20 45
    3 B 30 30
    1 B 5 15
    2 C 25 45

    Now, what I would like to do is create a block that provides me the below overview. This overview should sum all Cons Costs of each Cons on a particular file. For file B: Cost(cons 1)+Cost(cons 2)+cost(cons 3)=15+45+30=90.

    file Cons costs
    A 15
    B 90
    C 45

    What I get as output is the below block which is not what I need. For some reason I get the total cost if I remove the cons Object.
    file Cons costs
    A 90
    B 90
    C 90

    I hope this clearifies my question.

  74. Comment by Dave Rathbun

    You get that because the “file” value is no longer available, which apparently in this case makes the “In [File]” act the same as “In Report” and you get the overall total.

    Basically it works this way: anything in the input context does not have to be in the block. Anything in the output context has to be in the block. You can’t sum by “file” and use it to drive a calculation unless it’s in the output block in some fashion.

    I also tried to create a break and fold the results, but the break total doesn’t work because of the #MULTIVALUE error that occurs.

    File	Cost	Test
    A		15
    B		#MULTIVALUE
    C		45	
  75. Comment by Charki

    Maarten, i think your SQL should be as below to get the desired result.

    select B.file,sum(A.CONS_COST)
    from
    (select cons,sum(COST) as CONS_COST
    from Table_A
    group by CONS
    ) A,Table_A B
    where A.cons=B.cons
    group by B.file

    Table_A has the data as below

    Cons,file,Cost
    1,A,10
    2,B,20
    3,B,30
    1,B,5
    2,C,25

    I am not sure whether we can achieve that at report level.

  76. Comment by Rob

    Hi Dave,

    Thanks a lot for this page – I’ve learnt a lot from it. There is something I’m currently stuck on though and was hoping you might be able to give me a pointer:

    I have a report sectioned by a date dimension, but I need to have all the dates of the dimension available in each section. I’ve tried nofilter(date) but this doesn’t help and ‘in report’ gives me a #multivalue error. Is there a context operator that could help me with this please?

  77. Comment by Dave Rathbun

    Hi, Rob, that doesn’t sound like a context problem because you’re missing dimension values rather than adjusting how measures work. There are a couple of other blog posts here that talk about making up data, perhaps one of those will help.

    Making Up Data Part I: Personal Data Providers
    Making Up Data Part II: Using Universe Data

  78. Comment by Judy

    Hi Dave,

    I am currently using webi 4.0.

    I have two reports in one file. First one contains the overall sales of all regions and the second one is broken down by region.

    In the region report I need to calculate some expenses where i need to find the ratio by taking the region expenses divide by overall expenses, my measure give me #UNAVAILABLE error..

    This is the how my measure roughly look like :

    (if[Expenses]=1 Then [Region Expenses]* [earned profit])/ (if[Expenses]=1 Then [overall Expenses]* [earned profit]) in report

    I used flag to avoid Multivalue error

    Appreciate your assistance.

    Thanks

  79. Comment by Chakri

    Hi Judy,to understand the problem clearly could you please provide a small data set and the desired result you wish to achieve?

  80. Comment by jgrodrigueza

    Hi, I need some ideas to accomplish something. I am working on a table that needs to show the Top 10 Incidents by Configuration Item (not complicated), where I am stuck is in the column that needs to show the percentage, as I need it to show the percentage based on the total Incidents and not on the ones shown in the table:

    Example:

    CI01 | 116 | 11.42%
    CI02 | 86 | 8.46%
    CI03 | 59 | 5.81%
    CI04 | 47 | 4.63%
    CI05 | 47 | 4.63%
    CI06 | 47 | 4.63%
    CI07 | 43 | 4.23%
    CI08 | 38 | 3.74%
    CI09 | 35 | 3.44%
    CI10 | 29 | 2.85%

    The total Incidents for this example is 1,016 and if I show the table with all data, all percentages are calculated properly. When I add a ranking or somehow filter the table, the percentages are calculated based on the information shown by the table, like this, which represents the percentage of the Top 10 only:

    CI01 | 116 | 21.21%
    CI02 | 86 | 15.72%
    CI03 | 59 | 10.79%
    CI04 | 47 | 8.59%
    CI05 | 47 | 8.59%
    CI06 | 47 | 8.59%
    CI07 | 43 | 7.86%
    CI08 | 38 | 6.95%
    CI09 | 35 | 6.40%
    CI10 | 29 | 5.30%

    The percentage is being calculated by this formula: [CR - Ticket Count]/(Sum([CR - Ticket Count]) In Report)
    *CR – Ticket Count is constant “1″ sent by the database for each ticket and is defined as SUM measure in the Universe.

    Any ideas are appreciated. Thanks a lot.

  81. Comment by Dave Rathbun

    A rank or filter are two different ways that data can be reduced. If you want the overall total ignoring all filters then the NoFilter() function does that for you. A good initial guess might be:

    [CR - Ticket Count]/NoFilter((Sum([CR - Ticket Count]) In Report))

  82. Comment by jgrodrigueza

    Dave Rathbun, you are a genius!!! I did not know that one of the “NoFilter()”. I got exactly what I was looking for. Thanks.

  83. Comment by Vikram

    Hi Dave,
    I need to see sum of all rows for one of my measure column even when a dynamic report level filter is applied in webi.i tried using noFilter() function but that is not working.

    Please assist.

    Thanks,
    Vikram

  84. Comment by Dave Rathbun

    You’ll have to provide more details than “not working” in order to get any useful replies. :)

  85. Comment by Avanthi

    Hello Dave,

    Thanks a ton for the post. It really helped me accomplish a very detailed level report. I am hoping you could suggest a solution for one detail of my report.
    I have a Region- Location – Status – #of applications table in my report. I have a Horizontal stacked Chart(x-axis Location, Y-axis % of the count) by status. The problem is the % is being calculated for the entire report or any value given through input control whereas I want a % for each Location by Status.
    Example: location = Bay Area I want my % to be count for each status/count of Bay Area. Whereas I get % to be Count of each Status for Bay Area for the entire report or any filter I select which changes constantly. Is there a way I missed where the calculation context can be applied to the Charts?

    Thanks much
    Avanthi

  86. Comment by Rajiv

    Hi Dave,

    Just waiting for your detailed post on imput vs output context.
    Could you please post it as early as possible.

    Thanks

  87. Comment by dee

    Hi Dave,

    Please post the part II for calculation context, waiting eagerly to see it from you.

  88. Comment by wendy

    As always, you seem to explain things where I finally have the “ah ha” moment I’ve been missing for a very long time. Thanks!

  89. Comment by James

    Once again this one page has helped me get past a problem point. I kept trying to use ForEach and ForAll and what I need was IN! Delivered 100%!

  90. Comment by Mike

    Hi Dave,

    I am working on a report where I need to use calculation contexts…I have the following dimensions : Users, Dates and Comments. And my table looks like this:

    U1 D1 C1
    U2 D2 C2
    U3 D3 C3
    U3 D3 C4
    U4 D4 C5
    U4 D4 C6
    U4 D4 C7
    U4 D4 C8

    But the Comments with the same date and username are actually the same comment…and needs to be counted for only once. I used calculation contexts using Count([Comment]In([User];[Date])). Its not working and its is giving me the following results…

    U1 D1 1
    U2 D2 1
    U3 D3 0
    U4 D4 0

    Please help ASAP!

  91. Comment by Susan

    Hi Dave

    Is a section considered to be a filter? I have report with sections and I need to calculate a rolling sum for the previous 3 months, but I need to filter the resulting table so that it shows months 4-6 but calculates the running sum using months 1-3. Because of the version that I am running I need to calculate the sum as hours + previous(hours) + previous(previous(hours)). I have added the NoFilter() function around the sum calculation. When I look at the first section, the sum is correct, but when I look at the next section, the sum is being calculated using the hours figure from the first section also. So I can only assume the NoFilter() function is removing the section in the calculation.

    Is there any way of using NoFilter() but resetting the calculation for each section?

    Thanks

  92. Comment by Gaurav

    hey Dave..

    Your post is really helpful..Inspired by all your work..I myself wrote one here:
    http://gauravkalra.wordpress.com/

    Let me know how does that look. I have included very less detail in my first post..and tried to keep it interesting too:). I hope to follow up with more of them.

    Regards,
    Gaurav

  93. Comment by Dave Rathbun

    Glad to have helped.

  94. Comment by Dave Rathbun

    Yes, a section is definitely a filter. But all is not lost. :) When I have needed to do this in the past, I have done something along these lines. Assume that our section is on the Resort object, and there are three years of data within each section. I want a running total of 3 years, but only where the resort is the same. I might build three objects. The first is called “Previous Revenue” and it has this formula:

    =If Previous([Resort]) = [Resort] Then Previous([Revenue]) else 0

    The next is called Previous Previous Revnue:

    =If Previous(Previous([Resort])) = [Resort] Then Previous(Previous([Revenue])) else 0

    Finally the running 3 years of revenue:

    =[Revenue] + [Previous Revenue] + [Previous Previous Revenue]

    The total will then compare the current Resort value to the previous two rows, and only include the revenue when the resort values are the same.

    At the same time, you could use the RunningSum() function which already has a reset function built in.

  95. Comment by Dave Rathbun

    So what you really have is this:

    U1 D1 C1
    U1 D2 C2
    U3 D3 C3
    U3 D3 C3 (this should be C3, not C4, as it’s the same comment)
    U4 D4 C4
    U4 D4 C4
    U4 D4 C4
    U4 D4 C4 (the last four are the same)

    What is the desired output from this?

  96. Comment by Shaohua Ye

    Hi Dave, I keep reading your blog to find a solution for a data calculation/analysis problem.
    I have a measure called [result value] for A=4,3,2,<2,<2 and for B=4,3,<2,<2,<2. The system proveds a measure called [numeric value] for A=4,3,2,2,2 and for B=4,3,2,2,2 by removing "<" sign.The system also give a measure called[calculated value] for A=4,3,2,1,1 and B=4,3,1,1,1 by using half the value after "<". I need to use a rule for my calculation. The rule is: if total count of "<" is less than the total count of data number, use half of the value after "<". if total "<" is more or equal to total count of data, put the data with "<" to 0. I create a measure [calculated value_new]in tab "report 1" which gives A=4,3,2,1,1 and B=4,3,0,0,0. When I calculate average and percentile for [calculated value] and [calculated value_new]for A and B, there is no problem. But when I create another tab "report2" to summarise the average and percentile, there is no problem for the average and percentile for [calculated value] but gives #multivalue for the [calculated value_new]. I try everything and not working. Please help. Regards, Shaohua

  97. Comment by Mario

    Hello, I’m having the next problem and I want to see if you can give me some advice about it, let me explain.
    I have two data providers, this because the data comes from distinct databases.

    TABLE1 TABLE2
    Date id detail Detail Cost
    29/4/13 A A1 A1 5
    29/4/13 A A2 A2 1
    29/4/13 A A3 A3 3
    29/4/13 B B1 B1 4
    29/4/13 B B2 B2 2
    29/4/13 C C1 C1 7
    29/4/13 C C2 C2 4
    29/4/13 C C3 C3 3

    The report I need to launch must look like this:

    REPORT
    Date id Cost
    29/4/13 A 9
    29/4/13 B 6
    29/4/13 C 14

    But what I get is something like this one:

    REPORT
    Date id Cost
    29/4/13 A 29
    29/4/13 B 29
    29/4/13 C 29

    I tried to use the next combinations:

    Sum(cost) ForEach(Date,Id)
    RunningSum(cost;Row)
    RunningSum(cost;Date,Id)
    RunningSum(cost) ForEach(Date,Id)

    Can you help me with this calculation?

    Thanks

  98. Comment by Mani

    Hi Dave,
    When we use Foreach in aggregation functions with Where clause then do we need to follow specific order for specifying Foreach clause?
    PLease see below example:
    My Formula is
    =Average(
    [Session Average]
    Where([KP1]=”1″ And([Session Status]=”C”) And([Year]=”2013″) )
    ForEach([Course Code];[Session Number])
    )

    This above formula gives right rsults but if i specify Foreach clause before “where” clause then it gives wrong result.

    =Average(
    [Session Average] ForEach([Course Code];[Session Number])
    Where([Kp1]=”1″ And([Session Status]=”C”) And([Year]=”2013″) )
    )

    PLease help me to understand the difference.
    Thank you,
    Mani

  99. Comment by Cely

    Hi Dave,

    Your posts are so informative and I’m a big fan. I’m sure this is simple and I’m missing something, but I’ve tried every variation of calculation context to no avail. I have four columns (shown in ‘table’ below). I want the percentages to calculate correctly as shown in “COLUMN4″, but for some reason, they will not calculate as needed. So basically, I want to determine how many times a particular car feature is being used in unique models within the subset of unique Makes in the table. I then want to rank the Top 3 Car Features based on the USAGE RATIO% column. It seems simple enough in theory, but I’ve literally spent hours on this and cannot figure it out. Please let me know what I’m missing. I would TRULY appreciate it!!!

    For the USAGE RATIO% column, I’ve used the following calculation contexts (in every variation):
    COLUMN3/COLUMN2 Foreach([MAKE];[MODEL])in Block
    COLUMN3/COLUMN2 In([MAKE];[MODEL])
    COLUMN3/COLUMN2 In([CAR FEATURE];[MAKE];[MODEL])

    COLUMN1 COLUMN2 COLUMN3 COLUMN4
    CAR FEATURE | #MAKE (Unique) | #CAR FEATURES IN UNIQUE MODELS| USAGE RATIO% (Column3/Column2)
    Navigation System | 50 | 15 | 30%
    Rear backup Camera | 15 | 6 | 40%
    Heated Seats | 30 | 12 | 40%
    Moon Roof | 45 | 30 | 67%
    Top Air Bags | 20 | 10 | 50%

  100. Comment by Cely

    Hi Dave,

    I posted a calculation context dilemma earlier, but it hasn’t shown up yet on the site (hope it went through! Please let me know if you did not receive it.) I wanted to provide a cleaner screenshot of the table in question, but can’t figure out how to attach one to this forum. Basically, I’m just trying to get Column 4 (USAGE RATIO). It’s so simple on paper, in Excel, but for some reason, not in BO due to, I believe, calculation contexts, but none work for me. For some background, if you look at the data hierarchically, ‘Make’ is at the top level, Unique ‘Model’ is a subset of Make, and ‘Car Feature’, the most detailed level, is offered in some ‘Models’ by the associated ‘Makes’. I want to show how many times a Car Feature is used in the unique Models of a given Make (which I have), and then divide that number of Models by the number of Makes for each Car Feature (which I can’t figure out how to do and render correct results). I literally have no other folks to consult who have any BO knowledge, and BOB has produced no working suggestions so far. Hoping you can help… Thanks.

  101. Comment by av

    Hi dave,

    say if I have a measure object in my report and I create a variable out of it(with no context defined) Example categorizing the revenue measure(1-100 million,100-200 million) .

    what will be the default context on that measure? would it consider all the dimensions within the report while creating the variable?

    Thanks,
    AV

  102. Comment by Dave Rathbun

    The default context is based on the dimensions that are included in the block (or section) containing the new variable. For example, if the measure is in a stand-alone cell the output context would be “In Report.” If the measure is within a block, then the dimensions in the block determine the output context. If you include the specific context “In Report” within the formula, and place that measure inside of a block, then the specified context will override the dimension structure and you’ll get the overall report total instead.

  103. Comment by av

    Thanks, Dave, for the quick response. But the problem for me is, I have to create a section on the new variable.

    Just to let u know, when I used only rev in the formula ‘rev =1mill’ then ‘1-100mill’..same Id is appearing in different categories(investigating this i found that the id has two rows in the table(database) because of a column(which is not available in my report) which has different values).

    So for the formula I used something like this ‘rev in (id)=1mill’ then ‘1-100mill’…thus defining the context for the measure limiting it to only the id.

    I was able to set up the section on this variable but ran into issues when trying to create an input control on this variable.

    I understand this blog is not intended for personnel issues…please ignore this if am wasting your time.

    Really appreciate your contribution to Business Intelligence world..especially for starters like me.

    BIG FAN,
    AV

  104. Comment by Dave Rathbun

    You generally cannot use a measure (or a variable derived from a measure) as a section. The reason for that is the measure scope (or context) is defined by the section, not the other way around. When I have had to create a break down by range (10-19, 20-29, 30-39 and so on for ages, for example) then I have created that as a dimension object in my universe instead of trying to do it on the report.

  105. Comment by Tracy

    I am new to BOB webi reporting and have started at a new company where the universe is set up differently than I have worked with before, we are trying to create a simple TY vs. LY rolling 12 month report (month to date plus last 11 months compared with same time LY) I am able to get the rolling using (year*100)+ month Nbr for each block. (block1 has LY data and block2 has TY data) the issue I am having is creating a variable that can calculate data from each block. The report is displaying Month, week of month, Returns, Sales and RA% Sales for each 12 month block.

    Last Year (Block1) This Year (Block2)
    Month Week RRA LY Sales LY Month Week RRA TY Sales TY
    Aug 1 $50 $6,000 Aug 1 $20 $7,000

    What I am wanting to do is add a 3rd block or incorporate the totals into the 2nd block that calculates RRA TY – RRA LY (created variable RRA TY-LY Change) and another calculation that shows the % difference (=[RRA TY-LY Change]/[RRA LY])
    When I try and add these to the report I get a single value duplicated in each row or a multivalue error.
    Any suggestions?

  106. Comment by Marcus

    Hi Dave,

    Hope you can help a complete newbie. I’m working in Webi Intelligence (BOXI XI R2) with no access universe design.

    I have created a report measure variable in web intelligence which returns 1 if a record is current and 0 if not. I have a table on the report with a section break on case manager. If I do a sum on this measure in the table footer it returns the correct value for that section.

    However, I’ve been trying to do a summary table in a seperate block and when I try exactly the same formula in that, i get the #multivalue error. Any ideas?

  107. Comment by Dave Rathbun

    Marcus, the problem is probably related to the fact that your 0/1 indicator is only valid at the row level. Once you try to project it to a higher level you get a #MULTIVALUE error because there are multiple values (two different states, in this case) to pick from. What you might try instead is to use your current indicator as part of a “Where” clause on a sum, as the values used there do not have to appear in the block. Something like:

    sum([Measure]) where ([Current Flag] = 1)

  108. Comment by Dave Rathbun

    Do you have separate objects in the universe for this year (TY) and last year (LY) or are you breaking them apart on the report?

  109. Comment by Karthik

    Hi Dave,

    I have a problem in calculating the time taken for a particular status alone involved in a service request , #multivalue error.One service request will have multiple no of status such as ( pending, work in progress , closed etc).Now i want to calculate the time taken for the status ” pending” which should in the business days calculation.

    Request Status Start Time Stop Time Total Time Taken
    101 draft 1-Jul-13 5-Jul-13 4
    101 review 6-Jul-13 7-Jul-13 1
    101 WIP 7-Jul-13 8-Jul-13 1
    101 Pending 8-Jul-13 9-Jul-13 1
    101 Pending 10-Jul-13 11-Jul-13 1

    Expected output
    Request No Total Time Taken(Pending)
    101 2

  110. Comment by Riaz

    Hi,
    Can you provide me Calculation Context Part II

  111. Comment by Dawei

    Hi Dave thanks for the extrordinary articles always…. 4 years had past we are eagerly waiting for the later chapters!

  112. Comment by Dave Rathbun

    Me too! When am I going to get around to writing them, does anybody know? ;)

    In all seriousness, I have dusted off my draft for part two of this article and hopefully will have it ready to post without too much delay.

  113. Comment by Tietje

    Hi Dave,

    I love your blogs and am so glad they come up frequently when I am searching for something. I’m a little stumped here… I have a situation where I am pulling in a couple of dimensions (customer and fiscal periods) and the sales $ associated. What I want to do is show the sales $ for each customer for ONLY the latest fiscal period.

    So for example I am showing:

    Cust 1 — 201401 — $1,000
    Cust 1 — 201402 — $2,000
    Cust 1 — 201403 — $1,500
    Cust 2 — 201401 — $1,700
    Cust 2 — 201402 — $2,000
    Cust 2 — 201403 — $2,500

    What I WANT to show is:

    Cust 1 — 201403 — $1,500
    Cust 2 — 201403 — $2,500

    I’ve gotten it pretty close, but I can’t seem to get it just right. Is there any way to get a measure for the max (dimension)? If there is, I can’t seem to find it anywhere or guess at what the syntax might be. So I created a variable to give me the max fiscal period and since they are chosen via prompt, the varialbe is =Max([Main Query].[Fiscal Period]) In Report. I called it MaxUserResponse.

    Then I came up with this formula: =([Sales $] ForEach ([MaxUserResponse]))

    The result I am getting is the total of Sales $ across the fiscal periods, which makes sense, since that is what ForEach does. But I tried =([Sales $] In([MaxUserResponse])) also. I got the total for all customers for all fiscal periods. when I tried =([Sales $] In([Customer];[MaxUserResponse])) I got the total across all fiscal periods per customer.

    What am I missing? I know I am close… Please help? Thank you!

  114. Comment by Tietje

    Hi Dave,

    I think I figured it out. I just added a where clause to my formula. So now it looks like:

    ([Sales $] In ([Main Query].[Customer Name])) Where ([Main Query].[Fiscal Period] = [MaxUserResponse])

    Thanks for reading…
    Tietje

  115. Comment by puneet

    Hey Dave,

    excellent post… Were you able to publish rest of the context operators as per suggested by you… I am sure reading your posts will surely help clarify lods of my doubts.. Moreover do you have a link or website where in i can access other posts of yours to get better insight of BO

  116. Comment by Poorni

    Hi, I need to implement a similar solution.I need to split the data into three category , based on the percent to total value, the top 70% into catagory 1 , next 20% to category 2, last 10% to category 3. Any idea how i can do that ?

    Thanks

  117. Comment by Baby

    Ver Useful Link, It saved my day.Thanks Dave.

  118. Comment by Srikar

    Hi Dave,

    I have a requirement to display an average number of beds in a hospital, but each hospital has a validity start and end date. I need to display the number of beds per hospital at a summary level, which is an average number for each hospital irrespective of how many entries there are for validity dates per hospital.

    Detail level data:

    Hospital- Validity start - Validity End - No of Beds
    ————————————————————————–
    ABC - 01/01/2000 - 31/01/2000 - 10
    ABC - 01/02/2000 - 28/02/2000 - 20
    DEF - 01/01/2000 - 31/01/2000 - 20
    GHI - 01/01/2000 - 31/01/2000 - 30
    GHI - 01/02/2000 - 28/02/2000 - 30

    I need the above data summarised as follows:

    Hospital - Avg. No of Beds per hospital
    —————————————————
    ABC - 15
    DEF - 20
    GHI - 30

    I have tried to average the measure ForAll “Validity Start”, in an effort to exclude the date dimension from the calculation. I have also tried to average the measure “In Hospital” in an effort to only include Hospital in the calculation context. In both cases, I get the following output, where it adds up the measure for every entry of date, per hospital:

    Hospital - Avg. No of Beds per hospital
    —————————————————
    ABC - 30
    DEF - 20
    GHI - 60

    Can you please help?

    Thanks

  119. Comment by Vineet

    Hi Dave,

    I have a weird situation. I have Account nos and balance coming from one query. The BO report seems to neglecting the negative balance. So when I sum up the balance only positive values are considered.
    That is to say Sum([Balance]) gives the same result as Sum([Balance]) Where ([Balance]>0) and Sum([Balance]) Where ([Balance]<0) returns null.
    But there is negative balance. If I pull account no and balance into a separate report and apply a report filter for [Balance]<0 then I can see the negative balances against the accounts. But if I remove the accounts then it displays blank cell for balance.
    Any idea what is going on???

  120. Comment by Brenda

    Hi Dave,

    I used the example from this link http://bi.srivatsakr.com/2011/08/converting-rows-into-single-cell-comma.html to get multiple text values corresponding to an id into one cell, but as I need to do this for two type of multiple values, and my main query has already too much information, I did everything exactly as in the example in my main query and worked beautifully, but for the second one I am attempting to create it in a second query (as if I include this one also in the main query it takes forever), I tested in a second report and it worked fine, but then I tried to include it in the main report and I get #multivalue, I merged the main ids, I made the variables as detail variables, I created an indicator variable (Boolean) for the comparison to the maximum value and it still doesn’t work.

    In the second query and report I have something like this

    ID – type of contact
    123 – regular mail
    123 – email
    123 – phone
    234 – email

    when I do the link example in a second report I’m able to get it to look like this:

    ID – Type of contact
    123 – mail, email, phone
    234 – email

    but when I try to move it to the main report it looks like this:

    ID – Type of contact
    123 – #multivalue
    234 – email

    my variables look like this:
    This one I also tried it as a Boolean and it still didn’t work.
    Max Type Contact=Max([Type of Contact]) In ([Query2].[ID])

    A detail variable link to the main id of Query1
    ID Type of Contact = =[Type of Contact]

    A detail variable link to the main id of Query1
    Concat Type Contact = If IsNull(Previous(Self;([ID])))Then [ID Type of Contact] Else ([ID Type of Contact]+”, “+Previous(Self;([ID])))

    A measure variable:
    All Type of Contact = = [Concat Type Contact] Where ([ID Type of Contact] = [Max Type Contact])

    What am I missing? how can I properly link it to the main report and get it to work?

    Thanks a lot in advance,

    Brenda

  121. Comment by Adhish

    Hi Dave,
    when are you going to write about context in much more details.
    awaiting your post.please make it fast.

  122. Comment by RoyG

    Was wondering if you could help me calculate the percentages (G) of inlist values (V/X)% and (Y/X)% within the same dimension(V,X,Y) on a crosstabs.

    V X Y G F
    A 6 100 25 % %
    B 12 200 50 % %
    B 18 300 75 % %
    C 24 400 100 % %
    D 30 500 125 % %
    E 36 600 150 % %

  123. Comment by Nanda Kishore

    Hi,

    I have a requirement in a Webi Report as below.I have everything except the YTD Planned cost.So now I need to calculate the YTD Planned cost based on the Planned cost,Portfolio,Project Name,Month and Frequency.
    Can some body help me how to achieve this in BO Webi or through SQL query is also fine.

    Portfolio Project Emp Name PlannedCost	Month	Frequency	YTD PlannedCost
    BO	  ABC1	  John	   10000	Aug-15	    Monthly	10000
    BO	  ABC1	  John	   8000	        Sep-15	    Monthly	18000
    BO	  ABC1	  John	   5000	        Oct-15	    Monthly	23000
  124. Comment by Arup Chakraborty

    Hi Dave,
    I became a big fan of you. But I am not able to find the next blogs on input and output contexts as promised by you in this blog.
    Is it that you have not published those yet or I am not finding the proper link?
    Eagerly waiting for your posts.
    Regards,
    Arup

  125. Comment by Store_query

    Hi Dave,

    I am having a lot of trouble getting a simple query to change to what i want it to – I believe i am inputting the formula correctly as i have not have problems before using something like this:.

    query 1 (Product / Store / Stock)
    Query 2 (Store) Distinct list of store’s set by myself in a different universe.

    Merge: query1.store = query2.store
    Source dimension: Query2.store

    Currently query 1 will give me a total stock figure by store / product level.
    I require the store’s from query 2 to be the one i sum up against so i’ve tried:

    =Sum([stock].[Total Branch Stock Unit] ForEach ([Query 2].[STORE]))

    This still gives me the stock figure from query 1 (it should be lower as i have tested this on another tab to see what the output is).

    I’ve tried many variations of the above formula but all seem to get me the sum of product from query 1 when infact i need it to sum against query 2 store.

    Please advise, help would be much appreciated.

    AR

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.