Jan 04 2010
Calculation Context Part I: Overview
One of the hardest concepts to explain when introducing new folks to Business Objects is context.
I now imagine blog readers who have been working with the tools for a while have started nodding their heads in agreement, only to stop and say, “Hey, wait a second, is Dave talking about universe contexts or report calculation contexts?”
And the answer is, of course, yes. 😆
Both concepts can be quite complicated and take a while to fully understand. Since I have already written a number of blog posts about universe contexts I thought it was time to turn my attention to the report technique of calculation contexts. Note that this concept is used in Desktop Intelligence and Web Intelligence but not in Crystal. This post is intended to be an overview only. I have plans for a detailed post on each of the various context operators where I will go into much more depth. For this post my goal is to introduce the concept and provide some basic definitions.
Introducing Calculation Context
The default calculation context is defined by the dimensions that participate in a report block. It is this feature that makes the dynamic nature of measures possible. When dimensions are removed from or added to a block the context changes and the measure amounts also change. This is controlled by the projection function set on each measure by the universe designer.
However, a report writer sometimes wants a measure to be fixed at a particular value. Percentages are a really simple example. To calculate a contribution percentage I need to divide each individual value by the overall grand total. In order to do that, I need to know how to generate the grand total anywhere I want it. In order to do that, I need to understand how to use the various calculation context operators.
Context Operators
There is a simple (but powerful) set of context operators that I can choose from. I intend to briefly define each one in this blog post and then dive into more detail in future posts. As I mentioned when I started, the default context for a measure is based on the dimension values present in the block structure. I can alter which of those dimensions impact the calculation using one of the following three context operators.
- In is used to specify exactly which dimensions to include in a context. Other dimensions in the block are ignored. Adding / removing elements from the block does not impact this calculation unless a removed dimension was specified in the context. In that case a #MULTIVALUE error is displayed.
- ForEach is used to include a dimension in a context. The calculation context is still affected by other values in the block.
- ForAll is used to exclude a dimension from a context. Other dimensions of the block will still be considered. Adding or removing values from a block might change the value, but it will always ignore the ForAll items.
The operators listed above all require one or more dimensions as part of their syntax. However, calculation context can be specified by more than a set of dimension values. There are a series of report structure keywords that can be used as well.
- In Report sets the context at the report or “grand total” level. Any formula with these keywords for the context will return an overall total. Note that the total may still be affected by report filters.
- In Block sets the context for each
blocksection. For years I have been waiting for this setting to be renamed to reflect how it really works, but it hasn’t. I suppose it’s for backwards compatibility. If there is only one block on a report then “In Block” and “In Report” are going to be the same. But when a block is broken up into sections, then this context will generate a total for each section value. For that reason I submit that a better name might have been “In Section” instead, but as long as I know what it does the specific word does not really matter. - In Body is the standard default context for each row of data.
With Desktop Intelligence we have the option of In CurrentPage which can be quite useful and unfortunately not yet available in Web Intelligence. I probably don’t have to explain how the “CurrentPage” total is derived. 🙂
Finally, these keywords can appear in several places in a formula. The most common are the “input” and “output” context settings, but certain functions (like RunningSum() for example) also have a “reset” context.
- Input context is used to determine the input values for the formula.
- Output context is used to determine the output scope of the formula.
- Reset context is used to determine when a running function starts over at zero.
Dimensions referenced in the input context do not have to appear in the block. Output and reset dimensions must appear in the block in order to function correctly. For many calculations the default input context works just fine. I will show an example where it’s crucial to understand input context later on. For now I would like to move on to a simple example.
Simple Calculation Context Example
It’s hard to fully grasp some of the subtleties of calculation context without good examples. For this post I will provide one simple but common example that I mentioned earlier: contribution percent. I will start with a simple block with one dimension (resort), one measure (revenue), and a grand total as shown here.
This block already has a total on it. If this were a typical spreadsheet I could reference the total value using a row/column address, but that doesn’t work in Web Intelligence. That means I have to use another technique to generate the grand total. I mentioned the solution earlier… all I have to do is create a simple formula that looks like this:
Here’s the formula again:
=[Revenue] / [Revenue] In Report
Remember that the In Report context projects the measure up to the overall report total. By using that calculation context operator in the denominator of my division I ensure that I will always be dividing by the grand total in this formula. The results?
What’s Next?
This post barely scratched the surface of the calculation context question. It provided a few brief definitions and one simple example. I have several other posts planned that will dive into much greater detail about this often confusing subject. First I plan to talk more about In versus ForEach versus ForAll and how they are different. Next I plan to cover input versus output context and when each should be used. As I write this post neither of those two are much more than an outline for now, so I don’t have an estimated publish date.
But they’ll get published. Eventually. 😎
Hi dave,
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
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.
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
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.
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?
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?
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)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?
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
Hi,
Can you provide me Calculation Context Part II
Hi Dave thanks for the extrordinary articles always…. 4 years had past we are eagerly waiting for the later chapters!
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.
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!
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
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
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
Ver Useful Link, It saved my day.Thanks Dave.
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
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 asSum([Balance]) Where ([Balance]>0)andSum([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]<0then 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???
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
Hi Dave,
when are you going to write about context in much more details.
awaiting your post.please make it fast.
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 % %
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.
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
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
Webi 4.1. I need to retrieve a measure in my Webi “dashboard” based on the max date for that measure. If the answer to this question has been posted elsewhere, please forgive me and point me in the right direction.
Thank you in advance.
Hi Dave , little bit different issue. I have a table displaying in report having column year/ accounts no / city .
Data is like
2003 /c01 /London
2004 /c01 / NYcity
I want to display another column which displays city value where year = max year for all rows
For example:
2003 /c01 /London / NYcity
2004 /c01 / NYcity / NYcity
Any clue how I can sort this.
Regards
Gbs
Hi Dave
I have created a cross tab report with 5 years data starting from 2001 to 2005
There is a object year (type number) in universe.
In report another variable created MAX_YEAR_NO , which is max(Year).
In cross tab I have used this report variable in the formula
=([Revnue] Where ([Year]=[ MAX_YEAR_NO]) ForAll([Year] )
Formula is summing up Revenue for all 5 years, but in this cross tab I am expecting only data for max year, which is 2005.
If I modify formula by adding constant value 2005 instead of variable ]=[ MAX_YEAR_NO] it works.
But I want to use dynamic formula rather than constant hard coded value.
Any clue ??? thanks in advance
Hi Dave,
I’m trying to create a line graph that shows the most recent 12 months of Charges and Payments. That’s the simple part. I am also trying to add two “baseline” lines that show the average charges and payments for the initial 12 month window. For example, the average charges over the initial 12 months were $18000 per month. I want to graph the monthly charges on a line, then have another line that goes across current 12 month time period at $18000. Is there a way to do this dynamically instead of hard coding??
Max(Year) if you include the Year is going to be the year itself. If you want the maximum year for the report, then your MAX_YEAR_NO should probably be something like Max([Year]) In Report.
Hi Dave,
Thanks for the blog. The information is very helpful for a young developer like me.
Has the other parts of this series is being published? I have read all your blogs but i couldn’t find it. I have searched BOB forum as well but no luck.
Regards,
Prabhakar
As mentioned, this is by far the most requested post that I have not yet written. I have actually started part of it since getting back to this site, but I don’t know if / when it will be completed. Thanks for your interest / comment!
Thanks for the update Dave. Is there any other works done by you is available to read not limited to BO?
I have been working more with Tableau recently, so my most recent post talks about similarities between Web Intelligence and Tableau as far as variables and formulas. If you’re looking for other things I write that are not even remotely BI related, well, let me know and I’ll post some links. 🙂
Have long been a fan of your blog– helpful, clear, and well written. Always useful info when I’m Googling some issue or another.
Recently, a question took me to your site, and another…
Thought I’d make you aware of your content, used elsewhere (almost entirely word for word) without credit: http://bo-reports.blogspot.com/2016/06/introducing-calculation-context-default.html?_sm_au_=iVVvvvNRJt65HBV6
Hi, thanks for the find. I have added that domain to my hotlink script.
Hi Dave,
Thank you for creating this blog, really useful what you are sharing with us. I have an issue where can’t seem to find the solution/have the wrong approach.
I need to calculate the revenue contribution of each employee with the following formula: (hours/total hours)*revenue
This works fine when i have the Customer dimension in the table, i do a break on the Employee and hide the table lines with the customer. I need to replicate the same in a chart and can’t seem to do the same trick by hiding the lines and show only the subtotal on the employee break.
Column1 Column2 Column3
Employee Customer Formula —— hide this line
Employee Total Sum(Formula)
Formula= ([Hours]/([Hours] ForAll ([Employee])))*[Revenue]
I would need to replicate the above but without the Customer dimension i.e.
Column 1 Column 2
Employee Formula
I tried with:
Sum([Hours] ForEach ([Customer])) / Sum([Hours] ForAll ([Employee]) In ([Customer]) * Sum([Revenue] ForEach ([Customer]))
but won’t take into consideration the customer dimension.
Any idea how i can get this work?
Many thanks
JJ
I have been working more with Tableau recently, so my most recent post talks about similarities between Web Intelligence and Tableau as far as variables and formulas. If you’re looking for other things I write that are not even remotely BI related, well, let me know and I’ll post some links. 🙂 – Yes i am very much interested.
Best explanation of calculation contexts I’ve found online. Many thanks for this
Hi Dave I am currently trying to Break column based on Manager Name and I want to sum Revenue, for each manager name at the end of break. The problem I am facing is that I am getting total sum of the column at breaks, instead of sum for each manager. I performed similar function on GP column and it worked fine. I am facing problem only on Revenue column.
Do you know where I am going wrong?
I would need to see the structure of your report. Can you provide a few more details?
If you need quicker response you might try posting on BOB if you have not already. There are lots of formula experts out there. 🙂
Hi Dave,
In one of my reports I am creating a bucket variable that is based on two more variables and If I bring the bucket variable to the report it is giving a multivalue error. I know multivalue errors are created when there are two or more values returned for that variable. But this is an unmerged dimension. I cannot figure out webi is behaving this way. I have created bucket variables as dimensions in other reports and they worked fine.
Here is the bucket variable
var bucket
= If([var DaysLetterVerified]<6 ) Then "0-5"
ElseIf( ([var DaysLetterVerified]<11 ) )Then "6-10"
ElseIf( ([var DaysLetterVerified]<16) ) Then "11-15"
ElseIf( ([var DaysLetterVerified]<21) ) Then "16-20"
ElseIf( ([var DaysLetterVerified]<26) ) Then "21-25"
ElseIf( ([var DaysLetterVerified]<30) ) Then "26-30"
ElseIf( ([var DaysLetterVerified]35) ) Then “36+”
[var DaysLetterVerified]
=DaysBetween([var MinLetterDt];[Verified Dt])
[var MinLetterDt]
=Min([Verification Letter Sent Dt]) ForEach ([Verification Bar Code ID])
If u can sense something very obvious please let me know. ( I am a beginner)
Hi Dave,
I’m trying to evaluate the result of a measure EXCLUDING itself. Ex. If I have 10 rows in a table. I’d like to create a measure which would calculate the result of a formula considering the other 9 rows (excluding current row), for each of the 10 rows. Is there something like “no in body” context I could use for that?
Thanks.
Not directly, that I am aware of, but this (or something like this) would work:
sum(x) In Report - sum(x) In BodyYou said if you had 10 lines you wanted to sum nine of them and leave out the current line. That’s what that formula would do.