Hi Everyone …
I have a report where i need to make changes to specific sections of the few columns. i hope you know what i am saying with the following ex.
i have 2 columns … customer usage & supervisor usage. the formulas for these columns are as follows.
customer usage = customer transactions * 20
supervisor usage = supervisor transaction * 30
my boss asks me to keep the same formulas for few segments of the column and change the formulas like
customer usage = customer transactions * 30
supervisor usage = supervisor transaction * 56
for the other segments of the column.
if i edit the formula , its affecting all the segments of the columns. but i want to change the formula differently , according to the different segment requirements.
can you please me how i accomplish this?? please help out i have do this by today or early tomorrow.
You have to do this with if-then-else. You need a condition on what defines your “segment”. BO is not Excel. You don’t have cell-level access without using if-then-else.
Hey Stevie…
thanks for the reply . I did what you told me . I have used IF THEN ELSE to change the conditions basing upton the region.
But i have summary sections in the column , where this condition is giving me # COMPUTATION error where as its working fine for the other detail sections(rows) of the column. othe objects used in the formula are measures and they are all hidden in the report.
Can anyone please tell me why i am getting this # COMPUTATION error for the summary sections of the columns and not the detail columns .
Your help is greatly appreciated . thanks for the help in advance.
You are most likely getting a #COMPUTATION because your If - Then - Else logic is referencing a dimension object that is not present in the summary row. In order for a value to be used, it has to be in “scope”. In the body of your block you can say this:
If (<Dim1> = "Test") then 1 else 0
But in the footer of the block you won’t have a value for and therefore the If statement fails.
If you create the calculations at the detail level, and then sum the results of those calculations, then that often works. But your footer calculations cannot reference values from the body of the block.
Tommy,
It depends on what else is in your calculation. If-then-else requires that the dimensions your iffing against be in the block. If they aren’t, you need to include them and then hide them.
Your summary may need to be based on folded details. Without seeing it, I can’t tell you what’s wrong. Feel free to send it to me and I’ll take a look in the next couple of days.
Hi Everyone…
thanks for the reply , but my issue is not solved yet. when sum the details for the summary footer row and use the following calculation its working fine.TOTAL SUM=Sum( - ( + )). But when i use this if and else clause as follows for the same section of the column(TOTAL SUM)…
If = “WEST” Then
Sum( - ( + )) Else 0.
this doesnt work. i have all these objects used in the report and they are hidden. i dont understand why its not working when i sue IF THEN ELSE clause. can someone please help me as soon as possible as i am already past my deadline time.
Hi Everyone …
I used the same condition before. Its working for individual rows. but its giving me #COMPUTATION error for the TOTAL at the end of the report. something like this.
TOTAL SUM
EAST 23
12
EAST SUBTOTAL #COMPUTATION ( IT SHOULD BE 12+23 = 35)
WEST 10
20
WEST SUBTOTAL #COMPUTATION ( IT SHOULD BE 10+20 =30)
NORTH 12
13
NORTH SUBTOTAL #COMPUTATION (IT SHOULD BE 12+13 =25)
SOUTH 11
10
SOUTH SUBTOTAL #COMBPUTATION (IT SHOULD BE 11+10 + 21)
GRAND TOTAL #COMPUTATION (IT SHOULD BE 35+30+25+21= 111
I used the IF ELSE THEN ( I USED ONE CLAUSE FOR EACH REGION) clause for this , its giving me values for detail rows like 23,12,10,20,12,13,11,10 but its not giving me the subtotals for the specific regions and grand total. Do you know why this is happening??