Formula changes to specific segments of Columns

system · September 29, 2004, 11:00pm

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.

thanks one and all in advance for the help.
Tom

tom_2004 (BOB member since 2004-07-10)

system · September 29, 2004, 11:05pm

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.

Steve Krandel (BOB member since 2002-06-25)

system · September 30, 2004, 4:18pm

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.

Tom

tom_2004 (BOB member since 2004-07-10)

system · September 30, 2004, 4:30pm

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.

Dave Rathbun (BOB member since 2002-06-06)

system · September 30, 2004, 4:31pm

I don’t do “Stevie”.

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.

Steve Krandel (BOB member since 2002-06-25)

system · October 1, 2004, 2:15pm

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.

thanks everyone,
Tom.

tom_2004 (BOB member since 2004-07-10)

system · October 1, 2004, 4:42pm

What exactly is not working (wrong data, syntax error)?

Try:

= SUM 
(
If <region> = "WEST" Then 
<Total Calls> - <personal> - <local>) 
Else 0
)

Andreas (BOB member since 2002-06-20)

system · October 1, 2004, 6:20pm

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

Thanks ,
Tom.

tom_2004 (BOB member since 2004-07-10)

system · October 1, 2004, 6:25pm

In brief:
In Reporter create a dimension variable “Flag” defined along the lines of:

= <Region> = " EAST"

Then use a WHERE clause for your measure along the lines of:

= <Measure> WHERE ( <Flag> = 1)