[SOLVED] Split Total/GrandTotal with a BY

Focal Point

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

September 22, 2015, 06:49 PM

Jonathan K.

[SOLVED] Split Total/GrandTotal with a BY

Is it possible to split a Total/Grandtotal with a BY? For instance I want to split the totals by CAR in a SUBTOTAL/SUBFOOT.

TABLE FILE CAR
PRINT
CAR
MODEL
DEALER_COST
BY COUNTRY SUBFOOT
"<CAR<DEALER_COST"
WHERE COUNTRY EQ 'ITALY'
END

For example the SUBFOOT here would be split into ALFA ROMEO and MASERATI with their respective DEALER_COST totals.

Thanks.

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

September 22, 2015, 08:04 PM

Dan Satchell

Here's one solution. Maybe someone else can suggest a more elegant solution.

SET HOLDLIST = PRINTONLY
SET ASNAMES  = ON
-*
TABLE FILE CAR
 SUM DEALER_COST
 COMPUTE COUNTRYX/A25 = 'Subtotal ' | CAR ;
 COMPUTE CARX/A16     = '';
 COMPUTE MODEL/A24    = '';
 COMPUTE SORTX/A1     = 'B';
 BY COUNTRY  NOPRINT
 BY CAR      NOPRINT
 WHERE COUNTRY EQ 'ITALY';
 ON TABLE HOLD AS SUBTOTALS
END
-*
DEFINE FILE CAR
 COUNTRYX/A25 = COUNTRY ;
 SORTX/A1 = 'A';
END
-*
TABLE FILE CAR
 SUM DEALER_COST
 BY SORTX  NOPRINT
 BY COUNTRYX
 BY CAR
 BY MODEL
 WHERE COUNTRY EQ 'ITALY';
MORE
FILE SUBTOTALS
END

September 23, 2015, 05:49 AM

Wep5622

You mean like this?:

TABLE FILE CAR
PRINT
  MODEL
  DEALER_COST

BY COUNTRY SUBFOOT
"<CAR<DEALER_COST"

BY CAR SUBTOTAL AS 'Total'

WHERE COUNTRY EQ 'ITALY'
END

WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :

September 23, 2015, 10:24 AM

Jonathan K.

Thanks Dan, that's more along of the lines of what I was looking for. The only issue is that I'd like to do it for every COUNTRY.

September 23, 2015, 12:27 PM

Dan Satchell

SET HOLDLIST = PRINTONLY
SET ASNAMES  = ON
-*
TABLE FILE CAR
 SUM DEALER_COST
 COMPUTE COUNTRYX/A25 = 'Subtotal ' | CAR ;
 COMPUTE CARX/A16     = '';
 COMPUTE MODEL/A24    = '';
 COMPUTE SORTX/A16    = COUNTRY || 'B';
 BY COUNTRY
 BY CAR  NOPRINT
 ON TABLE HOLD AS SUBTOTALS
END
-*
DEFINE FILE CAR
 COUNTRYX/A25 = COUNTRY ;
 SORTX/A16 = COUNTRY || 'A';
END
-*
TABLE FILE CAR
 SUM DEALER_COST
 BY COUNTRY  NOPRINT
 BY SORTX    NOPRINT
 BY COUNTRYX
 BY CAR
 BY MODEL
 ON COUNTRY SUBFOOT
  " "
MORE
FILE SUBTOTALS
END