Conference ilbbak::ibi_focus

340.0. "Q. ROW-TOTALs & SUBTOTALling RPCT.field" by UNTADI::WHELAN (Quick ! Where's the ambulance .. ?) Fri May 11 1990 09:24

 Hello,

 I, being a novice user, need to ask some, what are probably very simple,
 questions. However, if somebody could answer them for me and/or offer some
 workarounds, where applicable, and before I loose what's left of my hair,
 I would be most grateful....

 ROW-TOTAL:   Is is possible, when SUMming more than 1 value ACROSS fields,
 ''''''''''   to specify that only 1 of the values is to be row-totalled?
 It is documented that 'If you don't need to specify which field to row total,
 use the syntax - ON TABLE ROW-TOTAL'. I would have imagined, therefore, that
 there is a way in which you can specify exactly which field you want to be
 row-totalled. Am I wrong in this assumption ?

 In other words, I want to produce something like this -
 ------------------------------------------------------------------------------
               Branch 1       Branch 2       Branch 3		Total
    	     Value %Total   Value %Total   Value %Total		Value
 ------------------------------------------------------------------------------
     Type a    50    25      100    50       50    25		 200
 ------------------------------------------------------------------------------
 but using various combinations of -

     SUM value AND ROW-TOTAL AND COMPUTE percent/D3=RPCT.value
          ACROSS BRANCH
          BY     TYPE

 all I manage to produce is the following -
 ------------------------------------------------------------------------------
               Branch 1       Branch 2       Branch 3		Total
    	     Value %Total   Value %Total   Value %Total		Value %Total
 ------------------------------------------------------------------------------
     Type a    50    25      100    50       50    25		 200    100
 ------------------------------------------------------------------------------

 SUBTOTAL & RPCT:   Following on from the above I am running into another
 ''''''''''''''''   little problem, namely, in my SUBTOTALs I can not manage
 to get the RPCT function to function as desired. That is to say, I cannot
 get the correct percentages of the values across the row. In fact, all I
 get is a column total of percentages. Is there a workaround ?

 What I want to achieve is the following -
 ------------------------------------------------------------------------------
               Branch 1       Branch 2       Branch 3		Total
    	     Value %Total   Value %Total   Value %Total		Value
 ------------------------------------------------------------------------------
     Type a    50    25      100    50       50    25		 200
     Type b    10     5        .     .      190    95            200
 ------------------------------------------------------------------------------
 District 1    60    15      100    25      240    60            400
 ------------------------------------------------------------------------------
 but using various combinations of -

     SUM value AND ROW-TOTAL AND COMPUTE percent/D3=RPCT.value
          ACROSS BRANCH   AS ''
    	  BY     DISTRICT AS '' NOPRINT SUBTOTAL
          BY     TYPE     AS ''

 all I manage to produce is the following -
 ------------------------------------------------------------------------------
               Branch 1       Branch 2       Branch 3		Total
    	     Value %Total   Value %Total   Value %Total		Value %Total
 ------------------------------------------------------------------------------
     Type a    50    25      100    50       50    25		 200    100
     Type b    10     5        .     .      190    95            200    100
 ------------------------------------------------------------------------------
 District 1    60    30      100    50      240   120            400    200
 ------------------------------------------------------------------------------

 All help will be gratefully received.....

 Regards,
 Se�n.

 Hello again,

 Before it's suggested, I have also tried the following (fairly obvious)
 variation of SUBTOTAL when wanting to SUBTOTAL COMPUTEd values -

     SUM value AND ROW-TOTAL AND COMPUTE percent/D3=RPCT.value
          ACROSS BRANCH   AS ''
    	  BY     DISTRICT AS '' NOPRINT
          BY     TYPE     AS ''
	  ON     DISTRICT AS '' RECOMPUTE

 This produces the same result, ie.

 ------------------------------------------------------------------------------
               Branch 1       Branch 2       Branch 3		Total
    	     Value %Total   Value %Total   Value %Total		Value %Total
 ------------------------------------------------------------------------------
     Type a    50    25      100    50       50    25		 200    100
     Type b    10     5        .     .      190    95            200    100
 ------------------------------------------------------------------------------
 District 1    60    30      100    50      240   120            400    200
 ------------------------------------------------------------------------------

 Other suggestions ... ?

 Regards,
 Se�n.

 For your information (should anybody be reading this conference), concerning
 my query regarding SUBTOTAL/RECOMMPUTE and RPCT, and how to get it to perform
 as documented and as expected - my colleague has been in touch with the IBI
 FOCUS Support Desk here in Germany and it seems that we have, in fact,
 discovered that this is a bug and the expected result can not be achieved!

 Still outstanding, is my query concerning ROW-TOTAL-ling selected columns
 in a matrix type report. Any ideas ?

 Regards,
 Se�n.

    I am not too familar with using across but it is possible to do
    the sub-totals by using recap and subfoot. If the number of branches
    is fixed there is an extremely inflexible way to do it as follows.
    
    SUM A IN 10
    B IN 30
    C IN 50
    COMPUTE TOT/I3 = A + B + C; IN 70
    COMPUTE PERA/I2 = A / TOT * 100; IN 20
    COMPUTE PERB/I2 = B / TOT * 100; IN 40
    COMPUTE PERC/I2 = C / TOT * 100; IN 60
    ON TABLE RECAP
    TOTA/I3 = A;
    TOTB/I3 = B;
    TOTC/I3 = C;
    TOTALL/I3 = TOT;
    TOTPERA/I2 = TOTA / TOTALL * 100;
    TOTPERB/I2 = TOTB / TOTALL * 100;
    TOTPERC/I2 = TOTC / TOTALL * 100;
    ON TABLE SUBFOOT
    "<10 <TOTA <20 <TOTPERA <30 <TOTB <40 TOTPERB <50 <TOTC <60 <TOTPERC
    <70 TOTALL"
    
    
    Hope this helps in some way,
    
    			Derek.

    Derek,

    Thank you for your input. Your example would, indeed, do the necessary
    if there was a fixed number of columns. Alas, just to keep things
    difficult, in my application I have to deal with a variable number of
    columns each time. Maybe, if I think more about your method, it might
    be possible to adapt it crudely to cope with variable columns.....

    Se�n.