Hi,
I want the subtotal and its values to be displayed in the same line. Right now they are displayed on two lines.
A few days back some one suggested this solution that the word "TOTAL" and the column value are LONGER than the total length of the sort field values for that column name, so the SUBTOTAL values appear on a second line. To make this come in a single line, he has created another field COUNTRYL with greater length like:

TABLE FILE CAR
SUM RCOST DCOST SALES
COMPUTE COUNTRYL/A17=COUNTRY; NOPRINT
BY COUNTRY NOPRINT SUBTOTAL
BY COUNTRYL
ON TABLE SET STYLEMODE FIXED
END

Here actually COUNTRYL field is getting displayed in the output. The problem for me is I don�t want to display the sort field. My code is something like:

PRINT
SECDESC AS 'Description'
QUANTITY AS 'Quantity'
COMPUTE PDATE/MDYY = HDATE(PURCHASEDATE, 'YYMD'); AS 'Purchase, Date'
COSTBASE_TTD AS 'Total Cost'
AMRTBASE_MTD AS 'Month To, Date'
AMRTBASE_YTD AS 'Year To, Date'
AMRTBASE_TTD AS 'Total To, Date'
ADJCOSTBASE_TTD AS 'Adjusted, Cost'
BY SECTYPE SUB-TOTAL COSTBASE_TTD AMRTBASE_MTD AMRTBASE_YTD AMRTBASE_TTD ADJCOSTBASE_TTD NOPRINT
ON COSTBASE_TTD COLUMN-TOTAL AS 'TOTAL FIXED INCOME'

i dont want to display the sort field 'SECTYPE'. is there any way to bring the subtotal and its values in the same line?
thanks in advance.

Would putting the totals in a SUBFOOT work?

Putting subtotal & grand total on subfoot works. thanks for that. i have put subtotal on 'on <columnname> subfoot' and grand total on 'on table subfoot'. but the problem is 'on table subfoot' is displaying the grand total below the footing line. i want it above the footing line. i can put both of them under footing only, but i want the actual footing to be displayed at bottom and grand total below the table. any solution to this?

Try

SET PRINTPLUS=ON

before the TABLE FILE ...

Thanks Francis for your solution.
It is working only for PDF not for Excel. Please let me know if there is any solution for Excel Reports.

Dharma

Dharma,

Have you thought of trying EMR (or FML, FRL or whatever they call it now-a-days)?

That should give you control over cell level calculations - providing you extract your data in a suitable manner.

A long sample code (it's from my examples archive) - but it may assist you.

SET ASNAMES = ON
SET HOLDLIST = PRINTONLY
SET PAGE-NUM = OFF

DEFINE FILE GGSALES
SYEAR/YY = DATE;
SMNTH/M = DATE;
SQTR/Q = DATE;
EMR_TAG/A3 = EDIT(SQTR) | EDIT(SMNTH);
END

TABLE FILE GGSALES
SUM DOLLARS/D12!L AS MNTH_REV
BY SYEAR
BY SQTR
BY SMNTH
SUM EMR_TAG
DOLLARS/D12!L AS REVENUE
UNITS
BY SYEAR
BY SQTR
BY SMNTH
BY REGION
BY ST
WHERE SYEAR FROM 1990 TO 1999
ON TABLE HOLD AS EMRDATA
END
-*EXIT
TABLE FILE EMRDATA
SUM REVENUE AS 'Revenue'
MAX.MNTH_REV AS 'Total Revenue,All Regions'
COMPUTE PCT_CONT/D6.1% = REVENUE / MAX.MNTH_REV * 100; AS '% Contribution to,Monthly Total,Revenue'
BY REGION PAGE-BREAK
ACROSS HIGHEST SYEAR AS ''
FOR EMR_TAG
'101' AS 'Jan' LABEL Q1JAN OVER
'102' AS 'Feb' LABEL Q1FEB OVER
'103' AS 'Mar' LABEL Q1MAR OVER
RECAP Q1TOT = Q1JAN + Q1FEB + Q1MAR; AS 'Q1 Totals' LABEL Q1TOT OVER
RECAP Q1TOT(3) = Q1TOT(1) / Q1TOT(2) * 100; OVER
RECAP Q1TOT(6) = Q1TOT(4) / Q1TOT(5) * 100; OVER
RECAP Q1TOT(9) = Q1TOT(7) / Q1TOT(8) * 100; OVER
RECAP Q1TOT(12) = Q1TOT(10) / Q1TOT(11) * 100; OVER
-*RECAP Q1TOT(0) = 'Total'; OVER

'204' AS 'Apr' LABEL Q2APR OVER
'205' AS 'May' LABEL Q2MAY OVER
'206' AS 'Jun' LABEL Q2JUN OVER
RECAP Q2TOT = Q2APR + Q2MAY + Q2JUN; AS 'Q2 Totals' LABEL Q2TOT OVER
RECAP Q2TOT(3) = Q2TOT(1) / Q2TOT(2) * 100; OVER
RECAP Q2TOT(6) = Q2TOT(4) / Q2TOT(5) * 100; OVER
RECAP Q2TOT(9) = Q2TOT(7) / Q2TOT(8) * 100; OVER
RECAP Q2TOT(12) = Q2TOT(10) / Q2TOT(11) * 100; OVER

'307' AS 'Jul' LABEL Q3JUL OVER
'308' AS 'Aug' LABEL Q3AUG OVER
'309' AS 'Sep' LABEL Q3SEP OVER
RECAP Q3TOT = Q3JUL + Q3AUG + Q3SEP; AS 'Q3 Totals' LABEL Q3TOT OVER
RECAP Q3TOT(3) = Q3TOT(1) / Q3TOT(2) * 100; OVER
RECAP Q3TOT(6) = Q3TOT(4) / Q3TOT(5) * 100; OVER
RECAP Q3TOT(9) = Q3TOT(7) / Q3TOT(8) * 100; OVER
RECAP Q3TOT(12) = Q3TOT(10) / Q3TOT(11) * 100; OVER

'410' AS 'Oct' LABEL Q4OCT OVER
'411' AS 'Nov' LABEL Q4NOV OVER
'412' AS 'Dec' LABEL Q4DEC OVER
RECAP Q4TOT = Q4OCT + Q4NOV + Q4DEC; AS 'Q4 Totals' LABEL Q4TOT OVER
RECAP Q4TOT(3) = Q4TOT(1) / Q4TOT(2) * 100; OVER
RECAP Q4TOT(6) = Q4TOT(4) / Q4TOT(5) * 100; OVER
RECAP Q4TOT(9) = Q4TOT(7) / Q4TOT(8) * 100; OVER
RECAP Q4TOT(12) = Q4TOT(10) / Q4TOT(11) * 100; OVER

RECAP YRTOT = Q1TOT + Q2TOT + Q3TOT + Q4TOT; AS 'Year Totals' LABEL YRTOT OVER
RECAP YRTOT(3) = YRTOT(1) / YRTOT(2) * 100; OVER
RECAP YRTOT(6) = YRTOT(4) / YRTOT(5) * 100; OVER
RECAP YRTOT(9) = YRTOT(7) / YRTOT(8) * 100; OVER
RECAP YRTOT(12) = YRTOT(10) / YRTOT(11) * 100; OVER

""
END

thanks tony. but since i am new to webfocus, i havent understood what exactly FMR does, from your code. right now i have two issues in excel: 1. i want the subfoot to be displayed above footing in excel. it was working in pdf with 'set printplus=on' but not in excel. 2. some cell spaces are coming in between the total values which i have put in subfoot.<br />here's sample code:
ON SECTYPE SUBFOOT
TotalOther.COSTBASE_TTD>
<CT.AMRTBASE_MTD><CT.AMRTBASE_YTD>
<CT.AMRTBASE_TTD><CT.ADJCOSTBASE_TTD"<br /><br />In the above code there no space
between the column total values.
but in excel output a cell space is
coming between any two values.
>any help is appreciated. thanks in advance

This message has been edited. Last edited by: <Mabel>,

i got both the issues resolved which are mentioned in the previous post. for the 2nd issue, i just removed the '>' symbol like <CT.COSTBASE_TTD<+0><CT.AMRTBASE_MTD<+0>... the extra cell space is due the '>'.
for the first issue i did some calculation using 'IF' & 'THEN'. now its getting displayed accordingly but 2-3 blank lines (rows) are getting inserted. i want to either delete this extra line or merge two blank lines into one. any help is appreciated.