SET PAGE-NUM=OFF
TABLE FILE GGSALES
 SUM UNITS
   BY CATEGORY AS  ''
     ACROSS REGION AS ''
      ACROSS STATE AS ''
END

SET PAGE = OFF
TABLE FILE GGSALES
HEADING
"SUM of UNITS by CATEGORY ACROSS REGION and STATE"
"With ROW-TOTALs and COLUMN-TOTALs</1>"
 SUM UNITS/D12C AS ''
   BY CATEGORY AS  ''
   ACROSS REGION AS  ''
   ACROSS ST AS  ''
ON TABLE ROW-TOTAL AS 'ROW TOTAL'
ON TABLE COLUMN-TOTAL AS 'COLUMN TOTAL'
END


SUM of UNITS by CATEGORY ACROSS REGION and STATE 
With ROW-TOTALs and COLUMN-TOTALs 
  
RESULTS:
             Midwest                 Northeast               Southeast               West 
             IL      MO      TX      CT      MA      NY      FL      GA      TN      CA      WA      ROW TOTAL 
Coffee       109,581 109,943 113,253 109,491 109,628 116,659 114,996 127,176 108,776 235,583 121,180 1,376,266 
Food         118,068 115,731 107,615 114,439 113,456 125,473 115,324 120,284 114,221 222,711 117,523 1,384,845 
Gifts         79,932  72,053  78,869  78,510  78,825  70,194  79,982  82,823  71,650 152,276  82,766   927,880 
COLUMN TOTAL 307,581 297,727 299,737 302,440 301,909 312,326 310,302 330,283 294,647 610,570 321,469 3,688,991 

In FOCUS Since 1983 ~ from FOCUS to WebFOCUS.    Current: WebFOCUS Administrator at FIS Worldpay | 8204, 8206

  
             Midwest                 Northeast               Southeast          
             IL      MO      TX      CT      MA      NY      FL      GA       TotMidWest TotNortEast TotSeast
Coffee       109,581 109,943 113,253 109,491 109,628 116,659 114,996 127,176  332,777     335778      350,948 
Food         118,068 115,731 107,615 114,439 113,456 125,473 115,324 120,284  9999999     9999999     9999999
Gifts        79,932  72,053  78,869  78,510  78,825  70,194  79,982  82,823   9999999     9999999     9999999 

  
SET PAGE-NUM=OFF

DEFINE FILE  GGSALES
REGION1/A100 = IF REGION EQ 'Midwest' THEN 'Z1Total MidWest' ELSE IF REGION EQ 'Northeast' THEN 'Z2Total NorthEast' ELSE IF REGION EQ 'Southeast' THEN 'Z3Total SouthEast' ELSE 'Z4Total West';
HLDRGN/A100  = REGION;
END

TABLE FILE GGSALES
SUM UNITS 
-*COMPUTE ST1/A02 = IF REGION EQ 'Midwest' THEN 'Z2' ELSE IF REGION EQ 'Northeast' THEN 'Z3' ELSE IF REGION EQ 'Southeast' THEN 'Z4' ELSE 'Z5';
COMPUTE ST1/A02 = '';
BY CATEGORY
BY REGION1
ON TABLE HOLD AS HLD2
END

-RUN
TABLE FILE GGSALES
 SUM UNITS
 BY CATEGORY 
 BY HLDRGN 
 BY ST 
ON TABLE HOLD AS HLD1
END
?FF HLD1
?FF HLD2

TABLE FILE HLD1
PRINT *
ON TABLE HOLD AS MAIN
MORE 
FILE HLD2
END

TABLE FILE MAIN
SUM UNITS
BY CATEGORY AS ''
ACROSS HLDRGN AS ''
ACROSS ST AS ''

END

TABLE FILE GGSALES
 SUM UNITS AS ''
 BY CATEGORY AS ''
 ACROSS REGION AS ''
 ACROSS ST AS ''
 ACROSS-TOTAL AS 'Total'
 ON TABLE SET PAGE OFF
-* ON TABLE PCHOLD FORMAT EXL2K
END

SET HOLDLIST = PRINTONLY
SET ASNAMES = ON
-*
TABLE FILE GGSALES
 COUNT DST.ST/I5
 BY REGION
 ON TABLE SAVE AS REGIONS
END
-RUN
-SET &XREGCOUNT = &LINES ;
-*
-REPEAT ENDREPEAT1 FOR &I FROM 1 TO &XREGCOUNT
-READ REGIONS &XREGION.&I.11 &STCOUNT.&I.5
-ENDREPEAT1
-*
TABLE FILE GGSALES
 SUM UNITS/D10C AS 'TOTUNITS'
 BY REGION
 BY CATEGORY
 SUM UNITS/D10C
 BY REGION
 BY CATEGORY
 BY ST
 ON TABLE HOLD AS SUMDATA
END
-*
TABLE FILE SUMDATA
 SUM TOTUNITS NOPRINT
     UNITS AS ''
 BY CATEGORY AS ''
 ACROSS REGION AS ''
 ACROSS ST AS ''
-SET &K = 1 ;
-REPEAT ENDREPEAT2 FOR &I FROM 1 TO &XREGCOUNT
 COMPUTE TOTREGION&I/D10C = C&K ; AS 'Total &XREGION.&I'
-SET &K = &K + ( &STCOUNT.&I * 2 ) ;
-ENDREPEAT2
 ON TABLE SET PAGE OFF
-* ON TABLE PCHOLD FORMAT EXL2K
END

SET HOLDLIST = PRINTONLY
SET ASNAMES = ON

-SET &ECHO=ALL;

SET HOLDLIST   = PRINTONLY
SET ASNAMES    = ON
SET HOLDFORMAT = ALPHA
SET PAGE       = NOLEAD
-RUN

DEFINE FILE GGSALES
STATE/A05 = ' ' | ST;
END
-RUN

-* Detail data
TABLE FILE GGSALES
SUM UNITS/D10C
BY REGION
BY CATEGORY
BY STATE
ON TABLE HOLD AS DETDATA
END
-RUN

-* Total data
TABLE FILE GGSALES
SUM UNITS/D10C
COMPUTE STATE/A05 = 'TOTAL';
BY REGION
BY CATEGORY
ON TABLE HOLD AS SUMDATA
END
-RUN

TABLE FILE DETDATA
SUM
UNITS AS ''
BY CATEGORY AS ''
ACROSS REGION AS ''
ACROSS STATE AS ''

-* ON TABLE PCHOLD FORMAT EXL2K

MORE
FILE SUMDATA
END
-RUN

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

-SET &ECHO=ALL;

SET HOLDLIST   = PRINTONLY
SET ASNAMES    = ON
SET HOLDFORMAT = ALPHA
SET PAGE       = NOLEAD
-RUN


-* Detail data
TABLE FILE GGSALES
SUM UNIT 
COMPUTE REG/A30 = ' '|REGION;
COMPUTE STA/A30 = ST; 
BY REGION
BY CATEGORY
BY ST 
ON TABLE HOLD AS DETDATA
END
-RUN

-* Total data
TABLE FILE GGSALES
SUM UNIT
COMPUTE REG/A30='Total';
COMPUTE STA/A30 = REGION;
BY REGION
BY CATEGORY
ON TABLE HOLD AS SUMDATA 
END
-RUN

TABLE FILE DETDATA
SUM
UNIT AS ''
BY CATEGORY AS ''
ACROSS REG AS ' '
ACROSS STA AS ''

-*ON TABLE PCHOLD FORMAT EXL2K

MORE
FILE SUMDATA
END
-RUN 

                   Midwest                 Northeast               Southeast          
 STORE             IL      MO      TX      CT      MA      NY      FL      GA       TotMidWest TotNortEast
 TOTAL   Coffee    109,581 109,943 113,253 109,491 109,628 116,659 114,996 127,176  332,777     335778   
 TOTAL   Food      118,068 115,731 107,615 114,439 113,456 125,473 115,324 120,284  9999999     9999999     
 TOTAL   Gifts     79,932  72,053  78,869  78,510  78,825  70,194  79,982  82,823   9999999     9999999     

SET HOLDLIST  = PRINTONLY
SET ASNAMES   = ON
SET PAGE      = NOLEAD
SET BYDISPLAY = ON

-* Detail data
TABLE FILE GGSALES
 SUM UNITS
 COMPUTE REG/A15  = ' ' | REGION ;
 COMPUTE STA/A15  = ST ; 
 COMPUTE STORE/A5 = 'TOTAL';
 BY REGION
 BY CATEGORY
 BY ST 
 ON TABLE HOLD AS DETDATA
END

-* Total data
TABLE FILE GGSALES
 SUM UNITS
 COMPUTE REG/A15  = 'Total';
 COMPUTE STA/A15  = REGION ;
 COMPUTE STORE/A5 = 'TOTAL';
 BY REGION
 BY CATEGORY
 ON TABLE HOLD AS SUMDATA 
END

-* Report
TABLE FILE DETDATA
 SUM UNITS/D10C AS ''
 BY STORE       AS ''
 BY CATEGORY    AS ''
 ACROSS REG     AS ''
 ACROSS STA     AS ''
-*ON TABLE PCHOLD FORMAT EXL2K
MORE
 FILE SUMDATA
END