BusinessObjects Board

Computing problem

building reports desktopintelligence
#1

We are implementing deski reports which run on Free hand sql. Now there is a certain calculation which i want to be done in the front end.
Eg
Col A Col B ColC ColD ColE
CHX 1 45 20 05
CHX 1 15 35 45
CHX 0 10 10 05

Total ---- 50 45 45

ie Total (for column C) ---- (Sum of Column C where Column B is 1) - (Sum of Column C where Column B is 0)
where col A is
Col B is a
ColC, Col D, Col E are measures

Similarly for other columns too .
How do i do this in BO. Also I am breaking on ColumnA ie Totals are obtained for each in Column A.

Prompt help would be very much appreciated,
Thanks,
M

Thanks

marym (BOB member since 2006-09-21)

#2

Moderator note:
Please help keep BOB friendly! Review this topic for some suggestions, thanks.

In particular:

Andreas :de: (BOB member since 2002-06-20)

#3

Thanks, will keep in mind !!!

marym (BOB member since 2006-09-21)

#4

Moderator note; word “urgent” removed from subject.

jac :australia: (BOB member since 2005-05-10)

#5 
=Sum(<ColumnC>) - Sum(If <ColumnB>=1 Then <ColumnC> Else 0) - Sum(If <ColumnB>=0 Then <ColumnC> Else 0)

jac :australia: (BOB member since 2005-05-10)

#6

Appreciate the prompt reply jac,
but i tried putting it in and i get the Overall Total when i put the formula, not the difference Ie for ColC rather than getting 50 (45+15 - 10) I actually get 60 ( 45+15+10).
THe reason could be that i am breaking on the Column A and column C,D,E are deduped in the Free hand sql and this does not get accounted for when i am putting in the formula.

Now I found that i can get the SubTotal Col C when ColB = 1 and SubTotal Col C when ColB = 0 but these occur at the previous rows of Col C…ie

             <city><Flag><measure>
           ColA      ColB     ColC      
            CHX      0            2
            CHX      1            15
             CHX     1            25
            CHX      0            2
            CHX      0            4

Subtotal CHX 1 40
Subtotal CHX 0 8
Total CHX – 48
Change CHX – 32

             AUS     1             3
             AUS      1            6

Subtotal AUS 1 9
Total AUS — 9
Change AUS — 9 —same as total flag is only one value

             CAL      0           2
             CAL      0           1

Subtotal CAL 0 3
Total CAL – 3
Change CAL – 3—same as total if only one flag value

“Change” value is what i require .
Further colC is deduped at the Total and Subtotal ,so now when i put in the formula i get 48 not 32 as required. Is there anyway to solve this?

Thanks,
M

marym (BOB member since 2006-09-21)

#7

if i have understood this correctly what you need to do is the following:-

       

                ColA        ColB        ColC 
                CHX           0             2 
                CHX           1            15 
                CHX           1            25 
                CHX           0             2 
                CHX           0             4 

Sub1         CHX           1            40  (if ColB =1 then sum(ColC) Else 0)
Sub2         CHX           0             8   (if ColB =0 then sum(ColC) Else 0)Total         CHX                         48  (=<sub1>+<sub2>)
Change     CHX                         32  (=<sub1>-<sub2>)

Is that what you have already tried?

komoshon :uk: (BOB member since 2003-07-14)