Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] SUBTRACTION ON SUBTOTALS
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] SUBTRACTION ON SUBTOTALS
 Login/Join
 
<msam>
posted
 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,
MSAM

This message has been edited. Last edited by: Kerry,
 
Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Reply With QuoteReport This Post
Gold member
posted Hide Post
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
 
Posts: 52 | Location: NJ,USA | Registered: May 26, 2004Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Reply With QuoteReport This Post
<msam>
posted
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

 
Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] SUBTRACTION ON SUBTOTALS

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.