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.

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.

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
 

ST doesn't work in SUBHEAD, only in SUBFOOT

maybe 4 years ago it did work Susannah...

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

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.

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.

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