Percentage

[zajka]

Hello,
I’m using WebFOCUS Developer Studio 716.

I want to generate the report. In the report
there are columns: 1 Department (value), 2 Category (Consultant, Broker), 3 Debit (value), 4 Credit (value), 5 Sum = Debit-Credit, 6 Sum by Category Consultant (IF Category = ‘Consultant” THEN Sum ELSE 0), 7 Sum by Category Broker (IF Category = ‘Broker” THEN Sum ELSE 0), 8 Percentage of Consultant in Sum = Sum by Category Consultant*100/Sum

I need in every TOTAL Row to calculate “ Percentage of CONSULTANT in SUM” value by following formula: Sum by Category Consultant*100/Sum. Is it possible to calculate percentage only in “TOTAL” row and to show result in a new column (8)?

I tried to use RECAP function
ON Department RECAP Percentage/F6.2% = Sum by Category Consultant*100/Sum ;

But problem is, that result of this function is printing below word TOTAL. But I need to put this result into totally new column (8).

1 2 3 4 5 6 7 8
A C 10,00 5,00 5,00 5,00 0,00
C 20,00 6,00 14,00 14,00 0,00
C 30,00 7,00 23,00 23,00 0,00
C 40,00 8,00 32,00 32,00 0,00
B 50,00 9,00 41,00 0,00 41,00
B 60,00 10,00 50,00 0,00 50,00
B 70,00 11,00 59,00 0,00 59,00
Total 280,00 56,00 224,00 74,00 150,00 33,04%
B C 15,00 3,00 12,00 12,00 0,00
C 25,00 6,00 19,00 19,00 0,00
C 35,00 9,00 26,00 26,00 0,00
C 45,00 12,00 33,00 33,00 0,00
C 55,00 15,00 40,00 40,00 0,00
C 65,00 18,00 47,00 47,00 0,00
B 75,00 21,00 54,00 0,00 54,00
Total 315,00 84,00 231,00 177,00 54,00 76,62%

Thanks

 

[focwizard]

Well, there are two distinct options here.

The first, using RECAP, will work if you RECAP EVERY field, and put the results into a SUBFOOT. This can be more complex, as far as positioning goes, but will get you exactly what you want.

The second way is like this (using our CAR file to get similar fieldnames):

DEFINE FILE CAR
DEPARTMENT/A10 = COUNTRY;
CAT/A1 = IF SEATS EQ 4 THEN 'B' ELSE 'C';
DEBIT/D6 = RCOST;
CREDIT/D6 = DCOST;
END
TABLE FILE CAR
SUM DEBIT
    CREDIT
    COMPUTE SUM/D6 = DEBIT - CREDIT;
    [COLOR=red]DEBIT WITHIN DEPARTMENT NOPRINT
    CREDIT WITHIN DEPARTMENT NOPRINT[/color]
    [COLOR=green]COMPUTE XSUM/D6 = C4-C5; NOPRINT[/color]
    COMPUTE CSUM/D6 = IF CAT EQ 'C' THEN SUM ELSE 0;
    COMPUTE BSUM/D6 = IF CAT NE 'C' THEN SUM ELSE 0;
    COMPUTE CPCT/F6.2S% = CSUM*100/XSUM;
BY DEPARTMENT
ON DEPARTMENT SUB-TOTAL
BY CAT
ON TABLE NOTOTAL
END

It uses the 'WITHIN' operator to get the subtotal values on each detail line, 'C' column notation to identify the 'like-named' fields, and uses the 'S' edit option, to suppress the zero percents. It shows the percent on the detail as well, but is easier to format.