need to create an Excel speadsheet.
three sor columns with an across month year.

columns for the Across should be:

Jan 2008 Feb 2008 Mar 2008 April 2008 May 2007 June 2007 july 2007 Aug 2007 ...... Dec 2007

Now a staight across will give May 2007 June 2007 .....Dec 2007 Jan 2008 Feb 2008 .....April 2008

An ACROSS Month would give me Jan through Dec but I would loose the year.

Any sugestions

Thanks
Duane

Mr. Dgraff,

 -******************************************************************************************************
-* Rolling 12 months logic
-******************************************************************************************************
-SET &QT = '''';
-SET &BEG_YR   = DATECVT((DATEADD((DATECVT (&YYMD,   'I8YYMD', 'YYMD')),'M', -12)), 'YYMD','I8YYMD');
-SET &END_YR   = DATECVT((DATEADD((DATECVT (&BEG_YR, 'I8YYMD', 'YYMD')),'M',  11)), 'YYMD','I8YYMD');
-SET &LSTBOM   = DATECVT(DATEMOV(DATECVT(&BEG_YR, 'I8YYMD', 'YYMD'), 'BOM'),'YYMD', 'I8YYMD');
-SET &LSTEOM   = DATECVT(DATEMOV(DATECVT(&END_YR, 'I8YYMD', 'YYMD'), 'EOM'),'YYMD', 'I8YYMD');
-SET &FROMDATE = EDIT(&LSTBOM,'$$$$99/') | EDIT(&LSTBOM,'$$$$$$99/') | EDIT(&LSTBOM,'9999');
-SET &TODATE   = EDIT(&LSTEOM,'$$$$99/') | EDIT(&LSTEOM,'$$$$$$99/') | EDIT(&LSTEOM,'9999');
-SET &PGM_MO   = EDIT(&LSTBOM,'$$$$99');
-SET &PGM_YY   = EDIT(&LSTBOM,'9999');
-SET &XPGM_YY  = &PGM_YY + 1;
-SET &CNTR_MO  = &PGM_MO;
-SET &COLS     = 'COLUMNS ';
-SET &XCNTR    = 1;
-REPEAT MO_LOOP3 12 TIMES
-SET &MO_NAME = DECODE &CNTR_MO (
- 01 'Jan'
- 02 'Feb'
- 03 'Mar'
- 04 'Apr'
- 05 'May'
- 06 'Jun'
- 07 'Jul'
- 08 'Aug'
- 09 'Sep'
- 10 'Oct'
- 11 'Nov'
- 12 'Dec' ELSE 'XXX');
-SET &AND     = IF &XCNTR EQ 12 THEN ' ' ELSE ' AND ';
-SET &COLS    = &COLS | &QT | &MO_NAME | ' ' | &PGM_YY | &QT | ∧
-SET &XCNTR   = &XCNTR + 1;
-SET &CNTR_MO = IF &CNTR_MO EQ 12 THEN 1 ELSE &CNTR_MO + 1;
-SET &CNTR_MO = IF &CNTR_MO LE 9 THEN '0' | &CNTR_MO ELSE &CNTR_MO;
-SET &PGM_YY  = IF &CNTR_MO LT &PGM_MO THEN &XPGM_YY ELSE &PGM_YY;
-MO_LOOP3
-TYPE &COLS
-TYPE &BEG_YR TO &END_YR
-TYPE &LSTBOM TO &LSTEOM
-TYPE &FROMDATE TO &TODATE


Then, in the program, 

SQL SQLMSS SET SERVER XXXXXXXXXX
SQL SQLMSS PREPARE SQLOUTX FOR
SELECT
  a.COLUMN_NAME1,
  a.COLUMN_NAME2,
  a.COLUMN_NAME3,
  b.DATE_COLUMN,
  datename(month, b.DATE_COLUMN) as emonth
FROM
     DATABASE.dbo.TABLENAME a
JOIN DATABASE.dbo.TABLENAME b
   ON a.COLUMN_NAME = b.COLUMN_NAME
;
END
-RUN
?FF SQLOUTX
-RUN
-DO_RPT3
-******************************************************************************************************
DEFINE FILE SQLOUTX
  MON/A3         = EDIT(emonth, '999');
  XDATE1/YYMD    = HDATE(DATE_COLUMN,'YYMD');
  XDATE2/I8YYMD  = XDATE1;
  XDATE3/A8      = EDIT(XDATE2);
  MO_CCYY/A8     = MON | ' ' | EDIT(XDATE3,'9999');
END

TABLE FILE SQLOUTX
SUM
  COLUMN_NAME1
  COLUMN_NAME2
  COLUMN_NAME3
ACROSS MO_CCYY AS '' &COLS

etc.

Not easy, BUT, works OK... You can adjust the years as you see fit...

Have FUN!!!!

Tom

EDIT: Fixed the rolling year AND Gave a more complete example

This message has been edited. Last edited by: Tom Flynn,

If the across date is a smart date this works puts in a comma the version I used; Code from a table we have a date in.

DEFINE FILE MMAPTBL_UNO_PROD ADD
ADMIT/MtrYY=MM515;
END
TABLE FILE MMAPTBL_UNO_PROD
SUM
CNT.MM005_MMAP
ACROSS ADMIT
HEADING
""
FOOTING
""
WHERE ( MM301 EQ '20083' ) AND ( MM450 GT ' ' );

Duane,
Maybe this is a bit late for you, but here is a simple solution. It is based upon the assumption that your file has dates that span 1 year only. The example is based on the CAR file. I created a fictitious date.

  
-* File dgraff.fex
DEFINE FILE CAR
PROD/DMYY=
63* 365 + LENGTH * 10 + HEIGHT * 10 + WIDTH * 10;
MPROD/MtYY=PROD;
M/M=MPROD;
END
TABLE FILE CAR
SUM SALES 
ACROSS M NOPRINT
ACROSS MPROD
WHERE MPROD FROM '07/1971' TO '06/1972'
END

Duane

an easy question, so many answers...

What you want if I translate your question well is that you want the lst year first and then the normal month sequence.
If your period is a normal smartdate you can do this.

DEFINE FILE XXX
YEAR/YY=YOURDATE;
MONTH=tM/YOURDATE;
YEARMND/YYtM=YOURDATE;
END
TABLE FILE XXX
SUM whatever
BY xxxx
ACROSS HIGHEST YEAR NOPRINT
ACROSS MONTH NOPRINT
ACROSS YEARMND
END

...

duane -- see if this helps.
-* CASHFLOW is a sample ibi table
-***********************************************
-* get current month and year from system date
-SET &TODAY = &YYMD;
-SET &TODAY = '19980513';
-SET &CYR = EDIT(&TODAY,'9999$$$$');
-SET &CMO = EDIT(&TODAY,'$$$$99$$');
-SET &LYR = (&CYR - 1);
-***********************************************
DEFINE FILE CASHFLOW
CASHDATE/I8YYMD = CASH_DATE;
CASH_MM /A2 = EDIT(CASHDATE,'$$$$99$$');
CASH_YY /A4 = EDIT(CASHDATE,'9999$$$$');
SORT_DATE /A6 = IF CASH_MM LT '&CMO' THEN '&CYR' || CASH_MM ELSE
'&LYR' || CASH_MM;
END
TABLE FILE CASHFLOW
PRINT
LONG_TERM_DEBT
INTER_TERM_DEBT
SHRT_TERM_DEBT
ACROSS HIGHEST CASH_YY NOPRINT
ACROSS SORT_DATE
BY HIGHEST CASH_YY NOPRINT
BY SORT_DATE NOPRINT
BY CASH_DATE
WHERE CASH_DATE LT '05/01/1998';
END