Developing a report that is a little different from what I have done in the past where the subtotal of the 1st BY needs to contain the Totals based on the 3rd BY as well as the sub total for the Area.

Using 'GGSALES' as an example...

I need to subtotal on 'AREA' but the subtotal portion of the report for each of the two areas should contain the subtotal for each of the Products in that area as well as the subtotal for that AREA.

The code below does as what one would expect and produces a single line subtotal for each area. How should I approach making this more of an SUMMARY section for each Area based on Product with an overall Sub Total for that Area?

The goal to include a Sub Total for each the 'East' & 'West' AREAS which contain the sub total of all distinct 'PRODUCTS' for that 'AREA' presented above the AREA Sub Total.

Direction appreciated. Thanks in advance.

DEFINE FILE GGSALES
AREA/A4V = IF GGSALES.SALES01.REGION EQ 'Northeast' OR 'Southeast' THEN 'East' ELSE 'West';
END

TABLE FILE GGSALES
SUM
GGSALES.SALES01.DOLLARS
GGSALES.SALES01.UNITS
GGSALES.SALES01.BUDDOLLARS
GGSALES.SALES01.BUDUNITS
BY GGSALES.SALES01.AREA
BY GGSALES.SALES01.REGION
BY ALL.GGSALES.SALES01.PRODUCT

ON GGSALES.SALES01.AREA SUBTOTAL AS '*TOTAL'
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
$
ENDSTYLE
END

This message has been edited. Last edited by: <Emily McAllister>,

Dave,
MacGuyver to the rescue!!!

-SET &ECHO=ALL;
-* File daveh01.fex
JOIN BLANK WITH REGION IN GGSALES TO BLANK IN FSEQ AS M_
DEFINE FILE GGSALES
BLANK/A1 WITH REGION=' ';
AREA/A4 = IF REGION EQ 'Northeast' OR 'Southeast' THEN 'East' ELSE 'West';
XREGION/A15=IF COUNTER EQ 1 THEN REGION ELSE 'For Area ' | AREA;
END
-*
TABLE FILE GGSALES
SUM
DOLLARS AS Dollars
UNITS AS Units
BUDDOLLARS AS 'Budget,Dollars'
BUDUNITS AS 'Budget,Units'
BY AREA AS Area
BY COUNTER NOPRINT
BY XREGION AS Region
BY PRODUCT AS Product
WHERE COUNTER LE 2;
ON XREGION SUBTOTAL AS '*Total' WHEN COUNTER EQ 2;
ON TABLE SET PAGE-NUM OFF
ON TABLE SET LINES 99999
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
$
ENDSTYLE
END

As for the MacGuyver master file:

  
 FILE=FSEQ, SUFFIX=FIX, DATASET=FOCALPOINT/FSEQ.TXT, $
  SEGNAME=SEG1
   FIELD=CONTROL, BLANK , A1, A1, $
  SEGNAME=SEG2, PARENT=SEG1, OCCURS=VARIABLE
   FIELD=WHATEVER, , A1, A1, $
   FIELD=COUNTER, ORDER, I4,  I4,$

and data (be sure that the first character is a space!) :

  
 FSEQFILEFORMCGUYVERFSEQFILEFORMCGUYVERFSEQFILEFORMCGUYVERFSEQFILEFORMCGUYVERFSEQFILEFORMCGUYVERFSEQFILEFORMCGUYVER

And the Output

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

Thanks. However since I normally do not work with master files I have a follow up question or two.

I created the master and text data files and placed them under the 'ibisamp' folder inside EDASERVE\Applications. Not sure if this is the correct location but this is where our 'ggsales' resides?

When I tried running the fex code you shared I got the following error message:

(FOC236) LINKED FILE DOES NOT HAVE A MATCHING KEY FIELD OR SEGMENT: BLANK
0 ERROR AT OR NEAR LINE 4 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC003) THE FIELDNAME IS NOT RECOGNIZED: REGION
(FOC236) LINKED FILE DOES NOT HAVE A MATCHING KEY FIELD OR SEGMENT: BLANK
BYPASSING TO END OF COMMAND

When I refresh the 'fseq.mas' the following error is returned.
(FOC1547) Neither MFD nor AFD exists for synonym 'IBISAMP/FSEQ'

Feel I may be missing something in my setup that you are not. Thoughts?
Dave

Dave

quote:

I created the master and text data files and placed them under the 'ibisamp' folder inside EDASERVE\Applications. Not sure if this is the correct location but this is where our 'ggsales' resides?

Since you put FSEQ in IBISAMP did you change the fseq.mas accordingly?

  
 FILE=FSEQ, SUFFIX=FIX, DATASET=IBISAMP/FSEQ.TXT, $
  SEGNAME=SEG1
   FIELD=CONTROL, BLANK , A1, A1, $
  SEGNAME=SEG2, PARENT=SEG1, OCCURS=VARIABLE
   FIELD=WHATEVER, , A1, A1, $
   FIELD=COUNTER, ORDER, I4,  I4,$

Did you create the data file FSEQ.TXT in IBISAMP? And made sure that its first character is a space?