ONE TWO THREE FOUR
--------------------
A 10 10 10 10
B 5 5 5 5
C 3 3 3 3
SUBTOT_1 8 8 8 8
E 10 10 10 10
F 8 8 8 8
SUBTO_2 18 18 18 18
SUB_CALC= SUBTOT_2 -SUBTOT_1
Can this be done without using the FML .. without the RECAP...?
Regards, MSAMThis message has been edited. Last edited by: Kerry,
June 03, 2010, 04:45 PM
njsden
Attempting to do inter-row calculations is very difficult without FML.
You can attempt to do it the hard-way, that is, not using SUBTOTAL nor SUBFOOT but instead create your totals as new data records and append them to your source. You can then print the whole information sorted by a special field you'd define to make sure each line appears where it's supposed to.
Most frequent than not, you'll find that attempting to do this could be even more complex than FML itself but whatever approach you take will be up to how comfortable you feel with the tool and either of the techniques.
It may also be possible to carry some values without actually printing them (NOPRINT) and then attempting to use them in a RECAP structure to perform the subtractions and print them in a SUBFOOT. As I said previously, it may be easier and more "natural" to use FML which at the end of the day was designed precisely to solve this kind of challenges.
Create Another two Colomns like subt1, subt2 and Assign value 'Y' or 'N' depends on the colomn. in Print Statement for subt1, subt2 Columns use NOPRINT
COl1 ONE TWO THREE FOUR subt1 subt2 ---- ---- --- ----- ------ A 10 10 10 10 N N B 5 5 5 5 Y N C 3 3 3 3 Y N
E 10 9 8 6 N Y F 8 8 8 8 N N SUB_CALC 10 9 8 6
Ex: subt1/A1 = IF COL1 EQ 'B' THEN 'Y' ELSE IF COL1 EQ 'C' THEN 'Y' ELSE 'N'; Ex: subt2/A1 = IF COL1 EQ 'E' THEN 'Y' ELSE IF COL1 EQ 'F' THEN 'Y' ELSE 'N';
ON RECAP SUB_CALC= subt2 subt2
ON SUBFOOT SUB_CALC
Env Prod:WebFOCUS 7702 ,Windows xp on 64, SQL Server 2008, IRF Tool Env 1 Local: DevStudio 7702 - MS Windows XP SP2 - Apache Tomcat 5.0.28 Output: HTML, Excel and PDF
June 03, 2010, 06:47 PM
Dan Satchell
Your sample output doesn't make much sense to me, but here goes using the CAR file ...
DEFINE FILE CAR
CATEGORY/A20 = IF COUNTRY EQ 'ENGLAND' THEN 'England' ELSE
IF COUNTRY EQ 'FRANCE' OR 'ITALY' THEN 'France/Italy' ELSE
IF COUNTRY EQ 'JAPAN' OR 'W GERMANY' THEN 'Japan/W Germany' ELSE 'Other';
END
-*
TABLE FILE CAR
SUM SEATS DEALER_COST RETAIL_COST SALES/D8
COMPUTE SUBCALC_SEATS/I5 = IF COUNTRY EQ 'FRANCE' OR 'ITALY' THEN (-1 * SEATS) ELSE
IF COUNTRY EQ 'JAPAN' OR 'W GERMANY' THEN SEATS ELSE 0 ; NOPRINT
COMPUTE SUBCALC_DCOST/D8 = IF COUNTRY EQ 'FRANCE' OR 'ITALY' THEN (-1 * DEALER_COST) ELSE
IF COUNTRY EQ 'JAPAN' OR 'W GERMANY' THEN DEALER_COST ELSE 0 ; NOPRINT
COMPUTE SUBCALC_RCOST/D8 = IF COUNTRY EQ 'FRANCE' OR 'ITALY' THEN (-1 * RETAIL_COST) ELSE
IF COUNTRY EQ 'JAPAN' OR 'W GERMANY' THEN RETAIL_COST ELSE 0 ; NOPRINT
COMPUTE SUBCALC_SALES/D8 = IF COUNTRY EQ 'FRANCE' OR 'ITALY' THEN (-1 * SALES) ELSE
IF COUNTRY EQ 'JAPAN' OR 'W GERMANY' THEN SALES ELSE 0 ; NOPRINT
BY CATEGORY NOPRINT
BY COUNTRY
ON CATEGORY SUBTOTAL MULTILINES AS 'SUBTOTAL:<br>' AND UNDER-LINE
ON TABLE SUBFOOT
"</1 SUBCALC=<ST.SUBCALC_SEATS<ST.SUBCALC_DCOST<ST.SUBCALC_RCOST<ST.SUBCALC_SALES "
ON TABLE NOTOTAL
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF, $
TYPE=SUBTOTAL, STYLE=BOLD, $
TYPE=TABFOOTING, STYLE=BOLD, HEADALIGN=BODY, JUSTIFY=RIGHT, $
ENDSTYLE
END
WebFOCUS 7.7.05
June 04, 2010, 09:26 AM
<msam>
Dan, Can we do the same thing if we have a ROWTOTAL and do the same subtraction
DEFINE FILE CAR
CATEGORY/A20 = IF COUNTRY EQ 'ENGLAND' THEN 'England' ELSE
IF COUNTRY EQ 'FRANCE' OR 'ITALY' THEN 'France/Italy' ELSE
IF COUNTRY EQ 'JAPAN' OR 'W GERMANY' THEN 'Japan/W Germany' ELSE 'Other';
END
-*
TABLE FILE CAR
SUM SEATS DEALER_COST RETAIL_COST SALES/D8
COMPUTE SUBCALC_SEATS/I5 = IF COUNTRY EQ 'FRANCE' OR 'ITALY' THEN (-1 * SEATS) ELSE
IF COUNTRY EQ 'JAPAN' OR 'W GERMANY' THEN SEATS ELSE 0 ; NOPRINT
COMPUTE SUBCALC_DCOST/D8 = IF COUNTRY EQ 'FRANCE' OR 'ITALY' THEN (-1 * DEALER_COST) ELSE
IF COUNTRY EQ 'JAPAN' OR 'W GERMANY' THEN DEALER_COST ELSE 0 ; NOPRINT
COMPUTE SUBCALC_RCOST/D8 = IF COUNTRY EQ 'FRANCE' OR 'ITALY' THEN (-1 * RETAIL_COST) ELSE
IF COUNTRY EQ 'JAPAN' OR 'W GERMANY' THEN RETAIL_COST ELSE 0 ; NOPRINT
COMPUTE SUBCALC_SALES/D8 = IF COUNTRY EQ 'FRANCE' OR 'ITALY' THEN (-1 * SALES) ELSE
IF COUNTRY EQ 'JAPAN' OR 'W GERMANY' THEN SALES ELSE 0 ; NOPRINT
BY CATEGORY NOPRINT
BY COUNTRY
ON TABLE ROW-TOTAL AS 'TOTALS'
ON CATEGORY SUBTOTAL MULTILINES AS 'SUBTOTAL:<br>' AND UNDER-LINE
ON TABLE SUBFOOT
"</1 SUBCALC=<ST.SUBCALC_SEATS<ST.SUBCALC_DCOST<ST.SUBCALC_RCOST<ST.SUBCALC_SALES "
ON TABLE NOTOTAL
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF, $
-*TYPE=SUBTOTAL, STYLE=BOLD, $
TYPE=TABFOOTING, STYLE=BOLD, HEADALIGN=BODY, JUSTIFY=RIGHT, $
ENDSTYLE
END