This is an example program that extracts data for different time periods and then displays them all in one report:

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

DEFINE FILE CENTORD
ORDER_YEAR/A4YY = ORDER_DATE;
ORDER_MT1/A6YYM = ORDER_DATE;
ORDER_MONTH/A7 = EDIT(ORDER_MT1,'9999/99');

ORDER_DT1/A8YYMD = ORDER_DATE;
ORDER_DT2/HYYMD  = HINPUT(8, ORDER_DT1, 8, 'HYYMD');
ORDER_WK1/I2     = HPART(ORDER_DT2, 'WEEK', 'I2');
ORDER_WEEK/A7    = ORDER_YEAR | ' ' | EDIT(ORDER_WK1);
END
-RUN

TABLE FILE CENTORD
SUM
ORDER_YEAR/A10 AS PERIOD
COMPUTE PERIOD_SORT/A1 = '1';
COMPUTE PERIOD_TYPE/A5 = 'YEAR';
QUANTITY
LINEPRICE

BY PLANT
BY ORDER_YEAR NOPRINT

ON TABLE HOLD AS H001

WHERE ORDER_YEAR FROM '2000' TO '2001'
END
-RUN

TABLE FILE CENTORD
SUM
ORDER_MONTH/A10 AS PERIOD
COMPUTE PERIOD_SORT/A1 = '2';
COMPUTE PERIOD_TYPE/A5 = 'MONTH';
QUANTITY
LINEPRICE

BY PLANT
BY ORDER_MONTH NOPRINT

ON TABLE HOLD AS H002

WHERE ORDER_MONTH FROM '200011' TO '200102'
END
-RUN

TABLE FILE CENTORD
SUM
ORDER_WEEK/A10 AS PERIOD
COMPUTE PERIOD_SORT/A1 = '3';
COMPUTE PERIOD_TYPE/A5 = 'WEEK';
QUANTITY
LINEPRICE

BY PLANT
BY ORDER_WEEK NOPRINT

ON TABLE HOLD AS H003

WHERE ORDER_WEEK FROM '2001 08' TO '2001 12'
END
-RUN

TABLE FILE H001
SUM
QUANTITY
LINEPRICE

BY PLANT

ACROSS PERIOD_SORT  NOPRINT
ACROSS PERIOD_TYPE  AS ''
ACROSS PERIOD       AS ''

MORE
FILE H002
MORE
FILE H003
END
-RUN

Andy, here is a program/technique that I developed for one of my users to do something similar to what you want to do. It is done using the demo file CENTURYSALES. He was able to convert it to his file with few problems. It uses a technique called alternate master with an OCCURS clause.

-* File rolling_12months.fex
-DEFAULT &ENDYYM=200606;
-SET &BEGYYM=AYM(&ENDYYM,-12,'I6YYM');
-SET &PREVYR=EDIT(&BEGYYM,'9999');
-SET &CURRYR=EDIT(&ENDYYM,'9999');
-SET &ENDOFYR=&PREVYR||'12';
-SET &BEGOFYR=&CURRYR||'01';
-SET &PREVMOCNT=YM(&BEGYYM,&ENDOFYR,'I3')+1;
-SET &CURRMOCNT=YM(&BEGOFYR,&ENDYYM,'I3')+1;
-SET &BEGDT=&BEGYYM||'01';
-SET &NXTYYM=AYM(&ENDYYM,1,'I6YYM');
-SET &NXTDT=&NXTYYM||'01';
-SET &ENDDT=AYMD(&NXTDT,-1,'I8YYMD');
-TYPE &BEGYYM &BEGDT &NXTDT &ENDDT &PREVYR &CURRYR &PREVMOCNT &CURRMOCNT
-*-EXIT
DEFINE FILE CENTURYSALES
ROLLRTN/P8C=IF YYEARMONTH EQ &BEGYYM THEN 0 ELSE RETURN;
ROLLPRF/D20=IF YYEARMONTH EQ &BEGYYM THEN 0 ELSE PROFIT;
CURRAVGRTN/P8C=IF YEAR EQ &CURRYR THEN RETURN ELSE 0;
PREVAVGRTN/P8C=IF YEAR EQ &PREVYR THEN RETURN ELSE 0;
CURRAVGPRF/P20=IF YEAR EQ &CURRYR THEN PROFIT ELSE 0;
PREVAVGPRF/P20=IF YEAR EQ &PREVYR THEN PROFIT ELSE 0;
-SET &LOOPYYM=&BEGYYM;
-REPEAT ENDLOOP FOR &I FROM 1 TO 13 STEP 1
-*-TYPE &I
-* COMPUTE BUCKETS FOR EACH MONTH
RTN&I/P8C=IF YYEARMONTH EQ &LOOPYYM THEN RETURN ELSE 0;
PRF&I/P20=IF YYEARMONTH EQ &LOOPYYM THEN PROFIT ELSE 0;
-SET &LOOPYYM=AYM(&LOOPYYM,1,'I6YYM');
-ENDLOOP
END
-*-EXIT
TABLE FILE CENTURYSALES
SUM
-SET &LOOPYYM=&BEGYYM;
-REPEAT ENDPRNT FOR &I FROM 1 TO 13 STEP 1
-*-TYPE &I
-* PRINT BUCKETS FOR EACH MONTH
-SET &MONTH=EDIT(&LOOPYYM,'$$$$99');
COMPUTE LOOPMONTH&I/A2='&MONTH'; NOPRINT
COMPUTE COLHDR&I/A20=DECODE LOOPMONTH&I('01' 'January' '02' 'February' '03' 'March'
                                        '04' 'April'   '05' 'May'      '06' 'June'
                                        '07' 'July'    '08' 'August'   '09' 'September'
                                        '10' 'October' '11' 'November' '12' 'December');
RTN&I/P8C
PRF&I/P20
-SET &LOOPYYM=AYM(&LOOPYYM,1,'I6YYM');
-ENDPRNT
COMPUTE COLHDR14/A20='12 Mo. Rolling Avg';
ROLLRTN NOPRINT
COMPUTE RTNAVG/P8C=ROLLRTN/12;
ROLLPRF NOPRINT
COMPUTE PRFAVG/P20=ROLLPRF/12;
COMPUTE COLHDR15/A20='&CURRYR YTD Avg';
CURRAVGRTN NOPRINT
COMPUTE CURRRTNAVG/P8C=CURRAVGRTN/&CURRMOCNT;
CURRAVGPRF NOPRINT
COMPUTE CURRPRFAVG/P20=CURRAVGPRF/&CURRMOCNT;
COMPUTE COLHDR15/A20='&PREVYR Avg';
PREVAVGRTN NOPRINT
COMPUTE PREVRTNAVG/P8C=PREVAVGRTN/&PREVMOCNT;
PREVAVGPRF NOPRINT
COMPUTE PREVPRFAVG/P20=PREVAVGPRF/&PREVMOCNT;
BY PLANTNAME
WHERE ORDERDATE GE &BEGDT AND ORDERDATE LE &ENDDT;
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD FORMAT ALPHA
END
-*? HOLD HOLD
-*-EXIT
-RUN
APP FI ALTMAS DISK altmas.mas
-RUN
-WRITE ALTMAS FILENAME=HOLD,SUFFIX=FIX
-WRITE ALTMAS SEGNAME=PLANT,SEGTYPE=S0
-WRITE ALTMAS FIELDNAME=PLANTNAME,ALIAS=PLNTNM,FORMAT=A15,ACTUAL=A15,$
-WRITE ALTMAS SEGNAME=MEASURES,SEGTYPE=S0,PARENT=PLANT,OCCURS=16,$
-WRITE ALTMAS FIELDNAME=COLHDR,ALIAS=CH,FORMAT=A20,ACTUAL=A20,$
-WRITE ALTMAS FIELDNAME=RETURNS,ALIAS=RTN,FORMAT=P8C,ACTUAL=A8,$
-WRITE ALTMAS FIELDNAME=PROFIT,ALIAS=PRF,FORMAT=P20C,ACTUAL=A20,$
-WRITE ALTMAS FIELDNAME=COLCNTR,ALIAS=ORDER,FORMAT=I4,ACTUAL=I4,$
APP FI ALTMAS DISK hold.ftm
DEFINE FILE ALTMAS
BLANK/A5=' ';
END
TABLE FILE ALTMAS
SUM  RETURNS AS 'Returns'
OVER PCT.RETURNS/F5.1% AS '% of Total'
OVER PROFIT AS 'Profit'
OVER PCT.PROFIT/F5.1% AS '% of Total'
BY PLANTNAME NOPRINT
BY BLANK AS ''
ACROSS COLCNTR NOPRINT
ACROSS COLHDR AS ''
ON PLANTNAME SUBHEAD
"<PLANTNAME"
ON TABLE SET PAGE NOPAGE
END

You should be able to run this at your site. The trick is to create a bucket for each column on the report. It also uses -REPEAT loops and Dialogue Manager indexing to get the right column headers.

Thanks to all for your responses! I will give these try.

Andy

This is also a way to do it...
( just a sample, needs some work, but you'll get the idea )

-* first get all the data
TABLE FILE whatever
SUM data
BY dimension1
BY dimension2
BY period
WHERE period [select period that holds *all* other desired periodes ]
ON TABLE HOLD AS H_1 FORMAT FOCUS
END

-* smart matching per period
MATCH FILE H_1
SUM data AS 'data_a'
BY dimension1
BY dimension2
WHERE period [select period a ]
RUN
FILE H_1
SUM data AS 'data_b'
BY dimension1
BY dimension2
WHERE period [select period b ]
AFTER MATCH HOLD OLD-OR-NEW
RUN
FILE H_1
SUM data AS 'data_c'
BY dimension1
BY dimension2
WHERE period [ select period c]
AFTER MATCH HOLD OLD-OR-NEW
END

pro's:

Now you have a file with any desired dimensions and 3 seperate data fields:
• data_a
• data_b
• data_c

Good thing about different fields is that you can easily use them in computed to compare them.
i.e. compute DIFF/D5.2 = ( data_b - data_a )

con's:
depending on your database the first query might take more time collecting the total data.
but in our peticular case this is faster than 3 seperated queries and then matching them together.
( depends on which index / statistics you have on which dimensions )

Good luck,

One key thing to remember, I do not know what the limit is for the # of across items are, If you hit that limit you might have to create a temporary focus database and store the values in it to build you complete list of values. Then you could report off the temporary focus database.

-DEFAULTS &CURRDATE  = &YYMD.EVAL
-SET &DOW_NAME       = DOWK(&CURRDATE,'A3');
-SET &BACK_DAYS      = DECODE &DOW_NAME ('MON' 1 'TUE' 2 'WED' 3 'THU' 4 'FRI' 5 'SAT' 6 'SUN' 0 ELSE 0 );
-SET &PRIOR_SUNDAY_1 = AYMD(&CURRDATE,-&BACK_DAYS,'I8');
-SET &PRIOR_MONDAY_1 = AYMD(&PRIOR_SUNDAY_1,-6,'I8');
-SET &PRIOR_SUNDAY_2 = AYMD(&PRIOR_SUNDAY_1,-7,'I8');
-SET &PRIOR_MONDAY_2 = AYMD(&PRIOR_MONDAY_1,-7,'I8');
-SET &PRIOR_SUNDAY_3 = AYMD(&PRIOR_SUNDAY_1,-14,'I8');
-SET &PRIOR_MONDAY_3 = AYMD(&PRIOR_MONDAY_1,-14,'I8');
-SET &WEEK_52_SUNDAY = AYMD(&PRIOR_SUNDAY_1,-364,'I8');
-SET &WEEK_52_MONDAY = AYMD(&PRIOR_MONDAY_1,-364,'I8');
-SET &CURRYEAR       = EDIT(&CURRDATE,'9999$$$$');
-SET &FIRST_OF_YEAR  = &CURRYEAR || '0101';
-*
DEFINE FILE <filename>
 DUMMY/A1 = ' ';
-*
 WEIGHT1/D12.2 = IF <date col> FROM '&PRIOR_MONDAY_1' TO '&PRIOR_SUNDAY_1' THEN WEIGHT ELSE 0 ;
 WEIGHT2/D12.2 = IF <date col> FROM '&PRIOR_MONDAY_2' TO '&PRIOR_SUNDAY_2' THEN WEIGHT ELSE 0 ;
 WEIGHT3/D12.2 = IF <date col> FROM '&PRIOR_MONDAY_3' TO '&PRIOR_SUNDAY_3' THEN WEIGHT ELSE 0 ;
 WEIGHT4/D12.2 = IF <date col> FROM '&WEEK_52_MONDAY' TO '&WEEK_52_SUNDAY' THEN WEIGHT ELSE 0 ;
 WEIGHT5/D12.2 = IF <date col> FROM '&FIRST_OF_YEAR'  TO '&PRIOR_SUNDAY_1' THEN WEIGHT ELSE 0 ;
-*
 MILES1/D12.2  = IF <date col> FROM '&PRIOR_MONDAY_1' TO '&PRIOR_SUNDAY_1' THEN MILES ELSE 0 ;
 MILES2/D12.2  = IF <date col> FROM '&PRIOR_MONDAY_2' TO '&PRIOR_SUNDAY_2' THEN MILES ELSE 0 ;
 MILES3/D12.2  = IF <date col> FROM '&PRIOR_MONDAY_3' TO '&PRIOR_SUNDAY_3' THEN MILES ELSE 0 ;
 MILES4/D12.2  = IF <date col> FROM '&WEEK_52_MONDAY' TO '&WEEK_52_SUNDAY' THEN MILES ELSE 0 ;
 MILES5/D12.2  = IF <date col> FROM '&FIRST_OF_YEAR'  TO '&PRIOR_SUNDAY_1' THEN MILES ELSE 0 ;
-*
 COST1/D12.2   = IF <date col> FROM '&PRIOR_MONDAY_1' TO '&PRIOR_SUNDAY_1' THEN COST ELSE 0 ;
 COST2/D12.2   = IF <date col> FROM '&PRIOR_MONDAY_2' TO '&PRIOR_SUNDAY_2' THEN COST ELSE 0 ;
 COST3/D12.2   = IF <date col> FROM '&PRIOR_MONDAY_3' TO '&PRIOR_SUNDAY_3' THEN COST ELSE 0 ;
 COST4/D12.2   = IF <date col> FROM '&WEEK_52_MONDAY' TO '&WEEK_52_SUNDAY' THEN COST ELSE 0 ;
 COST5/D12.2   = IF <date col> FROM '&FIRST_OF_YEAR'  TO '&PRIOR_SUNDAY_1' THEN COST ELSE 0 ;
-*
 TITLE1/A25 = '<BR>Previous Week';
 TITLE2/A25 = '<BR>Two Weeks Ago';
 TITLE3/A25 = '<BR>Three Weeks Ago';
 TITLE4/A25 = 'Same Week<BR>Last Year';
 TITLE5/A25 = '<BR>YTD Total';
END
-*
TABLE FILE <filename>
 SUM WEIGHT1 AS 'Weight'
     WEIGHT2 AS ''
     WEIGHT3 AS ''
     WEIGHT4 AS ''
     WEIGHT5 AS ''
     OVER
     MILES1 AS 'Miles'
     MILES2 AS ''
     MILES3 AS ''
     MILES4 AS ''
     MILES5 AS ''
     OVER
     COST1 AS 'Cost'
     COST2 AS ''
     COST3 AS ''
     COST4 AS ''
     COST5 AS ''
 BY DUMMY NOPRINT
 ON DUMMY SUBHEAD
  " <TITLE1<TITLE2<TITLE3<TITLE4<TITLE5"
 ON TABLE SET HTMLCSS ON
 ON TABLE SET STYLE *
  TYPE=REPORT, GRID=ON, $
  TYPE=TITLE, STYLE=BOLD, $
  TYPE=SUBHEAD, HEADALIGN=BODY, STYLE=BOLD, $
 ENDSTYLE
END

-DEFAULTS &CURRDATE  = &YYMD.EVAL
-SET &DOW_NAME       = DOWK(&CURRDATE,'A3');
-SET &BACK_DAYS      = DECODE &DOW_NAME ('MON' 1 'TUE' 2 'WED' 3 'THU' 4 'FRI' 5 'SAT' 6 'SUN' 0 ELSE 0 );
-SET &PRIOR_SUNDAY_1 = AYMD(&CURRDATE,-&BACK_DAYS,'I8');

-SET &PRIOR_SUNDAY_1 = AYMD(&YYMD,-&DATEW,'I8');