I know this is a topic that's been around the mulberry bush a few times, and I'm sure I've seen an answer before - but I can't find the one I'm looking for.

There is this:

SET ASNAMES=ON
TABLE FILE CAR
SUM
SEATS AS ''
BY CAR
ACROSS COUNTRY
ON TABLE HOLD AS ACROSSH FORMAT ALPHA
END
TABLE FILE ACROSSH
PRINT *
END

But my situation is more complex and I need subtotals etc. I want to move the years down to the same line as the headings for the columns to the left.

This message has been edited. Last edited by: George Patton,

Perhaps get rid of the across, and create the columns yourself with some DM

George,
I ran your example and your titles (country names) are on the same line as CAR when you print your ACROSSH file.
You can then add subtotals...

George,

As per Danny, I ran your sample and got what you wanted, however, with the need to include sort fields and subtotals in the final pass, you're back to the same place again!

Try this code which does almost exactly what I can discern from your sample output.

DEFINE FILE GGSALES
  YEAR/YY         = DATE;
  YEARMNTH/MYY    = DATE;
END
TABLE FILE GGSALES
   SUM DOLLARS AS ''
    BY REGION
    BY ST      AS STATE
    BY CITY
ACROSS YEARMNTH
WHERE YEAR EQ 1997
ON TABLE SET ASNAMES ON
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS ACROSSH FORMAT ALPHA
END
-RUN

TABLE FILE GGSALES
   BY YEARMNTH
WHERE YEAR EQ 1997
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SAVE AS COLVALUE
END
-RUN

DEFINE FILE ACROSSH
  DUMMY/A40 = '';
END
TABLE FILE ACROSSH
  SUM
-READ COLVALUE NOCLOSE &Column.6
-REPEAT :Loop WHILE &IORETURN EQ 0;
      &Column
-* Read the next value
-READ COLVALUE NOCLOSE &Column.6
-:Loop
   BY DUMMY   AS 'Region Totals'
   BY REGION  AS 'Region'
   BY STATE   AS 'State'
   BY CITY    AS 'City'
HEADING
"Sales by Region, State and City for 1997"
"Report Date: &DATEDtMYY"
"Values in US Dollars"
ON REGION SUBTOTAL AS 'Total for '
-*ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET HTMLCSS ON
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
  FONT=TREBUCHET MS, $
  TYPE=HEADING, JUSTIFY=CENTER, SIZE=16, STYLE=+BOLD, BACKCOLOR=LIGHT BLUE, $
  TYPE=TITLE, JUSTIFY=CENTER, STYLE=+BOLD, BACKCOLOR=LIGHT BLUE, $
  TYPE=SUBTOTAL, BACKCOLOR=SILVER, STYLE=+BOLD, $
  TYPE=GRANDTOTAL, BACKCOLOR=SILVER, STYLE=+BOLD, $
ENDSTYLE
END
-RUN

T

Danny,

The example I gave is from an old forum post - it's not mine. And yes, it works with the simple example of a single SUM. I've got two of them:

DEFINE FILE HOLD
DUMMY/A20=' ';
END

TABLE FILE HOLD
SUM
     HOLD.HOLD.PDESC AS 'Product'
     HOLD.HOLD.CCODE AS 'Cust'
BY  DUMMY AS 'Product Totals'
BY  HOLD.HOLD.PDESC NOPRINT
BY  HOLD.HOLD.CCODE NOPRINT
SUM
     HOLD.HOLD.KGQTY AS ''
BY  DUMMY AS 'Product Totals'
BY  HOLD.HOLD.PDESC NOPRINT
BY  HOLD.HOLD.CCODE NOPRINT
ACROSS HOLD.HOLD.BL_YEAR AS ''

ON HOLD.HOLD.PDESC SUBTOTAL AS 'TOTAL'

But it seems to me that there IS a clever way to do this in a couple of lines - I searched all of Susannah's posts, because in the back of my mind it was she who provided a simple solution. Maybe I'm hallucinating ...

George,

you could get rid of the DM and just include the intermediate extract of the columns -

TABLE FILE GGSALES
   BY YEARMNTH
WHERE YEAR EQ 1997
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SAVE AS COLVALUE
END
-RUN

DEFINE FILE ACROSSH
  DUMMY/A40 = '';
END
TABLE FILE ACROSSH
  SUM REGION  AS 'Region'
      STATE   AS 'State'
      CITY    AS 'City'
   BY DUMMY   AS 'Region Totals'
   BY REGION  NOPRINT
   BY STATE   NOPRINT
  SUM
-INCLUDE COLVALUE
   BY DUMMY   AS 'Region Totals'
   BY REGION  NOPRINT
   BY STATE   NOPRINT

T

That looks promising ! I would never have thought of using an INCLUDE. I'll give it a shot tomorrow probably.

Tony,

Wow! I never thought that a fieldname could be a number! Very interesting. Is this legal FOCUS?

If I may add a bit of aesthetics:

  
-SET &ECHO=ALL;
DEFINE FILE GGSALES
  YEAR/YY         = DATE;
  YEARMNTH/MYY    = DATE;
  AYEARMNTH/A6MYY =YEARMNTH;
  DYEARMONTH/A7=EDIT(AYEARMNTH,'99/9999');
  A_S/A4=' AS ';
END
TABLE FILE GGSALES
   SUM DOLLARS AS ''
    BY REGION
    BY ST      AS STATE
    BY CITY
ACROSS YEARMNTH
WHERE YEAR EQ 1997
ON TABLE SET ASNAMES ON
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS ACROSSH FORMAT ALPHA
END
-RUN

TABLE FILE GGSALES
   BY YEARMNTH
   BY A_S
   BY DYEARMONTH
WHERE YEAR EQ 1997
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SAVE AS COLVALUE
END
-RUN

DEFINE FILE ACROSSH
  DUMMY/A40 = '';
END
TABLE FILE ACROSSH
  SUM REGION  AS 'Region'
      STATE   AS 'State'
      CITY    AS 'City'
   BY DUMMY   AS 'Region Totals'
   BY REGION  NOPRINT
   BY STATE   NOPRINT
  SUM
-INCLUDE COLVALUE
   BY DUMMY   AS 'Region Totals'
   BY REGION  NOPRINT
   BY STATE   NOPRINT
HEADING
"Sales by Region, State and City for 1997"
"Report Date: &DATEDtMYY"
"Values in US Dollars"
ON REGION SUBTOTAL AS 'Total for '
-*ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET HTMLCSS ON
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
  FONT=TREBUCHET MS, $
  TYPE=HEADING, JUSTIFY=CENTER, SIZE=16, STYLE=+BOLD, BACKCOLOR=LIGHT BLUE, $
  TYPE=TITLE, JUSTIFY=CENTER, STYLE=+BOLD, BACKCOLOR=LIGHT BLUE, $
  TYPE=SUBTOTAL, BACKCOLOR=SILVER, STYLE=+BOLD, $
  TYPE=GRANDTOTAL, BACKCOLOR=SILVER, STYLE=+BOLD, $
ENDSTYLE
END

This message has been edited. Last edited by: Danny-SRL,

quote:

I never thought that a fieldname could be a number!

Me too.

A slight change to remove the potential failure if numerical fieldnames are not "legal FOCUS" -

Does basically the same as yours but uses the knowledge that the first three characters of the summated column are used as a column prefix - which we know, but others may not realise, is the reason for DOLLARS AS ''!

TABLE FILE GGSALES
   SUM DOLLARS
    BY REGION
    BY ST      AS STATE
    BY CITY
ACROSS &Month
WHERE YEAR EQ &Year
ON TABLE SET ASNAMES ON
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS ACROSSH FORMAT ALPHA
END
-RUN

FILEDEF MFDMAST DISK ACROSSH.MAS
EX -LINES 5 EDAPUT MASTER, MFDMAST, C, MEM, FILE=MFDMAST, SUFFIX=FIX
SEGNAME=SEG01
FIELDNAME=LINE      ,A200    ,A200   , $
DEFINE COLNAME/A20 MISSING ON = IF LINE CONTAINS 'FIELD' THEN GETTOK(GETTOK(LINE, 200, 2, '=', 50, 'A50'), 50, 1, ',', 20, 'A20') ELSE MISSING;
DEFINE COLTITL/A22 MISSING ON = IF LINE CONTAINS 'FIELD' THEN ''''||SUBSTR(20, COLNAME, 4, 20, 20, 'A20')||'''' ELSE MISSING;
-RUN

TABLEF FILE MFDMAST
PRINT COLNAME
      COMPUTE AS/A4 = ' AS ';
      COLTITL
WHERE EDIT(COLNAME,'999$') EQ 'DOL'
ON TABLE SAVE AS COLVALUE
END
-RUN

T