Focal Point
Displaying subtotal at the top of the row total values.

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/8701028331

December 19, 2004, 09:34 AM
<KPBHATT>
Displaying subtotal at the top of the row total values.
Hi ,
We have a peculiar problem at hand. Webfocus allows us to display the sub totals at the end of each sub section of the report. however we need to display the subtotals first (i.e the subtotal has to be the first row and the details should follow in the next few rows)and then the details for each sub total. Is there a way we can achieve this kind of display.
December 19, 2004, 01:13 PM
GCohen
You can refer to a subtotal value by the syntax of ST.fieldname and put the value into a subhead. eg
SUM val1
BY sortfield BY nextfield
ON sortfield SUBHEAD
"Value for subtotal is <ST.val1 "

This can be done in the Developer Studio gui or
by language.
November 15, 2005, 09:39 PM
<John Osborn>
I was hoping to find an easy way to get a consistent way of putting subtotals above the detail for multiple output types not using SUBHEAD where PDF and HTML have to be treated differently... The answer is the McGyver Technique:

 -*////////////////////////////////////////
-*  Creating SUBTOTALS above the detail
-*////////////////////////////////////////

-*  McGyver Technique setup...

-*    First set up the master file.  Note that the top line of the master MUST
-*    be a full line of 80 spaces.  (This is acheived by LRECL below)
-*    Do NOT delete that line or the technique will not work.

APP FI FSEQ DISK FSEQ.MAS (LRECL 80
-RUN

-WRITE FSEQ
-WRITE FSEQ FILE=FSEQ,SUFFIX=FIX
-WRITE FSEQ SEGNAME=SEG1
-WRITE FSEQ FIELD=BLANK,,A1,A1,$
-WRITE FSEQ SEGNAME=SEG2,OCCURS=80,PARENT=SEG1
-WRITE FSEQ $ <== Bigger than usual
-WRITE FSEQ FIELD=1CHAR,,A1,A1,$
-WRITE FSEQ FIELD=COUNTER,ORDER,I4,I4,$

-RUN

-*    Set up the define based join.  We join ANY field in the source file
-*    to the FSEQ master so that we replicate each row in the source file
-*    80 times without any extra i/o.

JOIN BLANK WITH PIN IN EMPDATA TO BLANK IN FSEQ AS AJ

-*    End McGyver Setup

DEFINE FILE EMPDATA 
BLANK/A1 WITH PIN = ' ' ;
DEPT1/A20 = IF COUNTER EQ 2 THEN DEPT ELSE 'TOTAL';
END

TABLE FILE EMPDATA 
COUNT PIN DST.DEPT SUM.SAL AVE.SAL
-*BY DIV
BY COUNTER
BY DEPT1 IF COUNTER LE 2
END
 

August 04, 2008, 03:54 PM
susannah
ST doesn't work in SUBHEAD, only in SUBFOOT




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
August 04, 2008, 04:11 PM
FrankDutch
maybe 4 years ago it did work Susannah... Big Grin




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

August 04, 2008, 04:15 PM
mgrackin
The following code works for both HTML and PDF output.

TABLE FILE CAR
SUM RCOST NOPRINT DCOST NOPRINT
BY COUNTRY
SUM RCOST DCOST
BY COUNTRY
BY CAR
ON COUNTRY SUBHEAD
"Total for<COUNTRY<RCOST<DCOST"
ON TABLE SET STYLE *
TYPE=REPORT, SQUEEZE=ON,$
TYPE=SUBHEAD, HEADALIGN=BODY, BACKCOLOR=NAVY, COLOR=WHITE, STYLE=BOLD,$
TYPE=SUBHEAD, LINE=1, ITEM=2, JUSTIFY=LEFT , POSITION=P2,$
TYPE=SUBHEAD, LINE=1, ITEM=3, JUSTIFY=RIGHT, POSITION=P3,$
TYPE=SUBHEAD, LINE=1, ITEM=4, JUSTIFY=RIGHT, POSITION=P4,$
ENDSTYLE
ON TABLE PCHOLD FORMAT HTML
END


Thanks!

Mickey

FOCUS/WebFOCUS 1990 - 2011
August 04, 2008, 05:13 PM
susannah
rasberries !
i'm looking for something. USING THE SEARCH !!! HA!
i understand why it didn't work back in antediluvian versions..just wanted it to be better in 766. alas.




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
August 05, 2008, 07:17 AM
Danny-SRL
Kalpesh,

As usual: please complete your signature...

Now for a further example of the use of the McGuyver techinique:
  
JOIN BLANK WITH BODYTYPE IN CAR TO BLANK IN FSEQ AS B_
-*
DEFINE FILE CAR
BLANK/A1 WITH BODYTYPE=' ';
XCAR/A16=IF COUNTER EQ 1 THEN ' Total' ELSE CAR;
END
-*
TABLE FILE CAR
SUM RETAIL DEALER SALES
BY COUNTRY
BY XCAR AS CAR
WHERE COUNTER LE 2
HEADING
"Summary and Detail"
ON TABLE NOTOTAL
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
ENDSTYLE
END

You have the FSEQ file in this thread.


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

August 05, 2008, 09:46 AM
BlueZone
If your situation does not allow for a one-step display like suggested above, you can WITHIN the total to a field on the Hold file and use that field in the subhead later.
  
TABLE FILE CAR
SUM RCOST 
    RCOST WITHIN COUNTRY AS RC
    DCOST  
    DCOST WITHIN COUNTRY AS DC 
    SALES
BY COUNTRY
BY CAR
BY MODEL
ON TABLE HOLD AS HOLD1
ON TABLE SET ASNAMES ON
END
-RUN
-*
SET BYDISPLAY=ON
TABLE FILE HOLD1
PRINT
 SALES/I5C
BY COUNTRY SUBHEAD
"Totals for <COUNTRY : Retail-Cost=$<RC & Dealer-cost=$<DC "
BY CAR
BY MODEL
HEADING
"CAR FILE SAMPLE"
ON TABLE SET STYLE *
TYPE=REPORT, SQUEEZE=ON,$
TYPE=SUBHEAD,LINE=1,COLSPAN=3,HEADALIGN=BODY, BACKCOLOR=BLUE, COLOR=WHITE, STYLE=BOLD,$
TYPE=HEADING,COLOR=WHITE,BACKCOLOR=GREEN,$
TYPE=TITLE,BACKCOLOR=YELLOW,$
ENDSTYLE
END
-EXIT



Best regards,
Sandeep Mamidenna


-------------------------------------------------------------------------------------------------
Blue Cross & Blue Shield of MS
WF.76-10 on (WS2003 + WebSphere) / EDA on z/OS + DB2 + MS-SQL
MRE, BID, Dev. Studio, Self-Service apps & a dash of fun !! Music