Jan  Feb  Mar  Apr May Jun .... Dec
 State       City        Year
 ----------  ----------
 aaaaaaaaaa  pppppppppp  2005      x    x    x    x   x   x        x
                         2006      x    x    x    x   x   x        x
                         2007      x    x    x    x   x   x        x
                         2008      x    x    x    x   x   x        x

             qqqqqqqqqq  2005      x    x    x    x   x   x        x
                         2006      x    x    x    x   x   x        x
                         2007      x    x    x    x   x   x        x
                         2008      x    x    x    x   x   x        x
             .                        .                        .
             .                        .                        .
             .                        .                        .

             zzzzzzzzzz  2005      x    x    x    x   x   x        x
                         2006      x    x    x    x   x   x        x
                         2007      x    x    x    x   x   x        x
                         2008      x    x    x    x   x   x        x

 aaaaaaaaaa  Totals      2005      x    x    x    x   x   x        x
                         2006      x    x    x    x   x   x        x
                         2007      x    x    x    x   x   x        x
                         2008      x    x    x    x   x   x        x

DEFINE FILE XXX
DUMMYCITY/A25='Totals';
END
TABLE FILE XXX
SUM SALES
BY STATE
BY DUMMYCITY
BY YEAR
ACROSS MONTH
END

	Frank
	prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7

  
-* File CarMcGuyver.fex
JOIN BLANK WITH BODYTYPE IN CAR TO BLANK IN FSEQ AS B_
-*
DEFINE FILE CAR
BLANK/A1 WITH BODYTYPE=' ';
XCOUNTRY/A10=IF COUNTER EQ 3 THEN 'Grand' ELSE COUNTRY;
XCAR/A16=IF COUNTER EQ 2 OR 3 THEN 'Total' ELSE CAR;
END
-*
TABLE FILE CAR
SUM SALES
BY COUNTER NOPRINT
BY XCOUNTRY AS COUNTRY 
BY XCAR AS CAR
BY SEATS
ACROSS MPG IN-GROUPS-OF 10 ACROSS-TOTAL
WHERE COUNTER LE 3
HEADING
"Sales by Country, Car and Bodytype"
ON TABLE NOTOTAL
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ONLINE-FMT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
ENDSTYLE
END

quote:

Originally posted by medougherty:
Danny,
I tried your example and it comes up with no records... I have a CAR file, but what is FSEQ?

- Mary Beth

  
FILENAME=FSEQ,SUFFIX=FIX,
    DATASET=C:\IBI\APPS\FOCALPOINT\FSEQ.DAT
  SEGNAME=CHAR1,SEGTYPE=S0
   FIELDNAME=BLANK,BLANK,A1,A1,$
  SEGNAME=CHARS,SEGTYPE=S0,PARENT=CHAR1,OCCURS=VARIABLE
   FIELDNAME=CHAR,CHARS,A1,A1,$
   FIELDNAME=COUNTER,ORDER,I2,I4,$

  
 FILEFORMCGUYVERFILEFORMCGUYVERFILEFORMCGUYVERFILEFORMCGUYVERFILEFORMCGUYVERFILEFORMCGUYVERFILEFORMCGUYVER

quote:

J.G.,

Thanks... you definitely have me going in the right direction with dropping the ACROSS and using variables. I'm actually using the ACROSS, holding the file, thereby making the ACROSS output into fields, and then using the DEFINE variables....see code below...

My one last issue is that I can't seem to get the variables to reset themselves when sale_chan_code changes. I've tried using the "WITH" in the DEFINE, but it doesn't seem to have any effect. Any suggestions?

DEFINE FILE SALEDATA
Y1M1_SC_GALS/D20.0 WITH sale_chan_code = IF business_year EQ 2005 THEN Y1M1_SC_GALS + sal1;
Y1M1_SC_SPOTC/D20.0 WITH sale_chan_code= IF business_year EQ 2005 THEN Y1M1_SC_SPOTC + spo1;
Y2M1_SC_GALS/D20.0 WITH sale_chan_code= IF business_year EQ 2006 THEN Y2M1_SC_GALS + sal2;
Y2M1_SC_SPOTC/D20.0 WITH sale_chan_code= IF business_year EQ 2006 THEN Y2M1_SC_SPOTC + spo2;
END
TABLE FILE SALEDATA
PRINT
spo1
sal1
MAR1
Y1M1_SC_GALS
Y1M1_SC_SPOTC
spo2
sal2
MAR2
Y2M1_SC_GALS
Y2M1_SC_SPOTC
BY sos_code
BY fuel_type_code
BY deal_type_code
BY HIGHEST sale_chan_code
BY pricing_category
BY business_year
ON sale_chan_code RECAP
Y1M1_SC_MARGIN/D6.4 = IF Y1M1_SC_GALS NE 0 THEN Y1M1_SC_SPOTC/Y1M1_SC_GALS;
Y2M1_SC_MARGIN/D6.4 = IF Y2M1_SC_GALS NE 0 THEN Y2M1_SC_SPOTC/Y2M1_SC_GALS;
ON sale_chan_code SUBFOOT
"--- YEAR 1 (2005) ---> <+0> "--- YEAR 2 (2006) ---> <+0> END

Thanks again for all your help!
Mary Beth

DEFINE FILE XXX
SEQCODE/I1=1;
END
TABLE FILE XXX
SUM SALES
BY STATE
BY CITY
BY YEAR
BY MONTH
BY SEQCODE
ON TABLE HOLD AS HOLD1
END
-RUN
DEFINE FILE HOLD1
DUMMYCITY/A25='Totals'; 
-* the dummycity field should have the same format as the CITY field.
SEQCODE/I2=2;
END
SET ASNAMES = ON
-*
TABLE FILE HOLD1
SUM SALES
BY STATE
BY DUMMYCITY AS CITY
BY YEAR
BY MONTH
BY SEQCODE
ON TABLE HOLD AS HOLD2
END
-RUN
TABLE FILE HOLD1
PRINT *
ON TABLE HOLD AS HOLD3
MORE
FILE HOLD2
END
-RUN
TABLE FILE HOLD3
SUM
SALES
BY SEQCODE NOPRINT
BY STATE
BY CITY
BY YEAR
ACROSS MONTH
END

	Frank
	prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7

  
Y1M1_SC_GALS/D20.0 WITH sale_chan_code = IF business_year EQ 2005 THEN  Y1M1_SC_GALS + sal1 ELSE Y1M1_SC_GALS + 0;
Y1M1_SC_SPOTC/D20.0 WITH sale_chan_code= IF business_year EQ 2005 THEN Y1M1_SC_SPOTC + spo1 ELSE Y1M1_SC_SPOTC + 0;
Y2M1_SC_GALS/D20.0 WITH sale_chan_code= IF business_year EQ 2006 THEN Y2M1_SC_GALS + sal2 ELSE Y2M1_SC_GALS + 0;
Y2M1_SC_SPOTC/D20.0 WITH sale_chan_code= IF business_year EQ 2006 THEN Y2M1_SC_SPOTC + spo2 ELSE Y2M1_SC_SPOTC + 0;

  
Y1M1_SC_GALS/D20.0 WITH sale_chan_code = IF business_year EQ 2005 THEN  Y1M1_SC_GALS + sal1 ELSE 0; 
Y1M1_SC_SPOTC/D20.0 WITH sale_chan_code= IF business_year EQ 2005 THEN Y1M1_SC_SPOTC + spo1 ELSE 0; 
Y2M1_SC_GALS/D20.0 WITH sale_chan_code= IF business_year EQ 2006 THEN Y2M1_SC_GALS + sal2 ELSE 0;
Y2M1_SC_SPOTC/D20.0 WITH sale_chan_code= IF business_year EQ 2006 THEN Y2M1_SC_SPOTC + spo2 ELSE 0; 

  
-DEFAULTH &ECHO=OFF
-****************************************** GET SAMPLE DATA
JOIN CLEAR *
USE
 ibisamp/ggorder.foc AS ggorder
END
DEFINE FILE ggorder
 YEAR/YY=ORDER_DATE;
 MONTH/MT  =ORDER_DATE;
END
TABLE FILE ggorder
 SUM QUANTITY
 BY STORE_CODE
 BY PRODUCT_CODE
 BY YEAR
 BY MONTH
 IF STORE_CODE   CONTAINS '00'
 IF PRODUCT_CODE CONTAINS '00' OR '11' OR '44'
ON TABLE HOLD
END
-RUN
-********************************************************************************************************
-DEFAULTH &SORT1='STORE_CODE',    &NAME1='Store'
-DEFAULTH &SORT2='PRODUCT_CODE',  &NAME2='Prod.'
EX MAKESEQ 1,3 , ECHO=&ECHO
JOIN CLEAR *
JOIN BLANK WITH QUANTITY IN HOLD TO BLANK IN FSEQ AS A:
DEFINE FILE HOLD
 BLANK/A1 WITH QUANTITY = ' ';
 Z1/A1    = DECODE COUNTER(      1 1,        2 1,        3 3,         ELSE '?');
 Z2/A1    = DECODE COUNTER(      1 1,        2 2,        3 3,         ELSE '?');
 Sort1/A10=IF COUNTER EQ 1 THEN    &SORT1
      ELSE IF COUNTER EQ 2 THEN                &SORT1
      ELSE IF COUNTER EQ 3 THEN                            '*TOTAL'   ELSE '?';
 Sort2/A10=IF COUNTER EQ 1 THEN    &SORT2
      ELSE IF COUNTER EQ 2 THEN               '*TOTAL'
      ELSE IF COUNTER EQ 3 THEN                            ' '        ELSE '?';
END
TABLE FILE HOLD
 SUM QUANTITY
   BY Z1   NOPRINT    BY Sort1 AS '&NAME1' UNDER-LINE
   BY Z2   NOPRINT    BY Sort2 AS '&NAME2'
   BY YEAR AS Year
   ACROSS MONTH AS Mo.
 IF COUNTER FROM 1 TO 3
END
-********************************************************************************************************

  
-* File MakeSeq.fex
-DEFAULTH &1=' '  , &2=' ', &ECHO=OFF
-DEFAULTH &FIRST=1, &LAST=100,
-DEFAULTH &FILE=FSEQ, &KEY=BLANK, &COUNTER=COUNTER
-SET &FIRST = IF @|&1 NE @ THEN &1 ELSE &FIRST ;
-SET &LAST  = IF @|&2 NE @ THEN &2 ELSE &LAST  ;

 EX -LINES 7
 EDAPUT MASTER, &FILE, CV, MEM
  FILE=&FILE, SUFFIX=FOC, $
    SEGNAME=FSEQ1, SEGTYPE=S1, $
      FIELD=&KEY, , A1, ACCEPT=' ', INDEX=I, $
    SEGNAME=FSEQ2, SEGTYPE=S1, PARENT=FSEQ1, $
      FIELD=&COUNTER, , I4, $
 END

 CREATE FILE &FILE

 MODIFY FILE &FILE
  FREEFORM &KEY &COUNTER
  MATCH &KEY &COUNTER
    ON NOMATCH INCLUDE
    ON MATCH REJECT
 DATA
-SET &echo=&ECHO;
-REPEAT SEQ.LOOP FOR &I FROM &FIRST TO &LAST ;
 ' ', &I, $
-SET &ECHO=OFF;
-SEQ.LOOP
-SET &ECHO=&echo;
 END

  
FILEDEF MCGMAS DISK FSEQ.MAS
FILEDEF MCGDAT DISK FSEQ.DAT
-RUN
-WRITE MCGMAS FILENAME=FSEQ,SUFFIX=FIX
-WRITE MCGMAS  SEGNAME=CHAR1,SEGTYPE=S0
-WRITE MCGMAS   FIELDNAME=BLANK,BLANK,A1,A1,$
-WRITE MCGMAS  SEGNAME=CHARS,SEGTYPE=S0,PARENT=CHAR1,OCCURS=VARIABLE
-WRITE MCGMAS   FIELDNAME=CHAR,CHARS,A1,A1,$
-WRITE MCGMAS   FIELDNAME=COUNTER,ORDER,I2,I4,$
-RUN
-WRITE MCGDAT  FILEFORMCGUYVER
-RUN
FILEDEF FSEQ DISK FSEQ.DAT
-RUN