Let's say I have this:

TABLE FILE GGSALES SUM DOLLARS UNITS BY REGION BY PRODUCT END Which produces:
Region	Product	Dollar Sales	Unit Sales
Midwest	Biscotti	1091727	86105
	Coffee Grinder	619154	50393
	Coffee Pot	599878	47156
	Croissant	1751124	139182
	Espresso	1294947	101154
	Latte	2883566	231623
	Mug	1086943	86718
	Scone	1495420	116127
	Thermos	577906	46587
Northeast	Biscotti	1802005	145242
	Capuccino	542095	44785
	Coffee Grinder	509200	40977
	Coffee Pot	590780	46185
	Croissant	1670818	137394
	Espresso	850107	68127
	Latte	2771815	222866
	Mug	1144211	91497
	...

The question is: I want to identify let's say for the Midwest Region the Product that sold more overall which in this example was Latte with 2883566 and highlight it in yellow. Any thoughts? Thanks! Paulo

This message has been edited. Last edited by: Paulo Tavares,

Well... Here's a two pass solution, anyway.

TABLE FILE GGSALES
SUM
DOLLARS
UNITS
BY REGION
BY PRODUCT
ON TABLE HOLD
END

DEFINE FILE HOLD
REG_PROD/A27 = REGION | PRODUCT;
END
TABLE FILE HOLD
PRINT
DOLLARS
UNITS
COMPUTE
FLAG/A1 = IF REGION EQ LAST REGION THEN 'N' ELSE 'Y';
BY REGION
BY HIGHEST DOLLARS NOPRINT
BY PRODUCT
ON TABLE SET STYLE *
TYPE=DATA, COLUMN=P3, COLOR=YELLOW, WHEN=FLAG EQ 'Y', $
ENDSTYLE
END

If you just want a picture, I played with visualize and conditions, In this case whereever the total dollars is greater than 2,200,000 the bar is yellow rather than black.
TABLE FILE GGSALES
SUM
DOLLARS
UNITS
BY REGION
BY PRODUCT
ON TABLE NOTOTAL
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
DEFMACRO=COND0001,
MACTYPE=RULE,
WHEN=N3 GT 2200000,
$
GRAPHTYPE=DATA,
COLUMN=N3,
GRAPHLOOK=BAR,
$
GRAPHTYPE=DATA,
COLUMN=N3,
GRAPHLOOK=BAR,
GRAPHCOLOR='YELLOW',
MACRO=COND0001,
$
TYPE=REPORT,
GRID=OFF,
FONT='TIMES NEW ROMAN',
SIZE=10,
$
ENDSTYLE
END

I would say this:

TABLE FILE CGSALES
SUM MAX.DOLLARS
BY REGION
WHERE REGION EQ 'Southeast';
ON TABLE HOLD FORMAT ALPHA
END
RUN
-READ HOLD ®.A15. &DOL.A15. (don't know the correct length)
DEFINE FILE CGSALES
YELLOW/A1=IF DOLLARS EQ &DOL THEN 'Y' ELSE 'N';
END
TABLE FILE CGSALES
PRINT DOLLARS
UNIT
YELLOW NOPRINT
BY REGION
BY PRODUCT
ON TABLE SET STYLE *
TYPE=DATA, COLUMN=N3, COLOR=YELLOW, WHEN=N5 EQ 'Y', $
ENDSTYLE
END

In the styling might be an error....

Thanks for all answers! I guess I found a way of doing it in two passes:

-* File sales1.fex
TABLE FILE GGSALES
SUM
DOLLARS
UNITS
BY REGION
BY PRODUCT
HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS HOLD1
ON TABLE SET HTMLCSS ON
END
TABLE FILE HOLD1
SUM
MAX.DOLLARS NOPRINT AS 'MAX,DOLLARS'
BY REGION
SUM
DOLLARS
UNITS
BY REGION
BY PRODUCT
HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
DEFMACRO=COND0001,
MACTYPE=RULE,
WHEN=N4 EQ N2,
$
TYPE=REPORT,
GRID=OFF,
FONT='ARIAL',
SIZE=9,
RIGHTGAP=0.125000,
$
TYPE=DATA,
COLUMN=N4,
BACKCOLOR=RGB(255 204 0),
MACRO=COND0001,
$
TYPE=TITLE,
STYLE=BOLD,
$
TYPE=TABHEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=TABFOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=HEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=FOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=SUBHEAD,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBFOOT,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBTOTAL,
BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
SIZE=9,
$
TYPE=ACROSSTITLE,
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
BACKCOLOR=RGB(210 210 210),
STYLE=BOLD,
$
ENDSTYLE
END

This message has been edited. Last edited by: Paulo Tavares,

if you do it this way, there is no need to do it in two...
the second query can also be done on the CGSALES database directly.

It needs the first pass to calculate the SUM of total sales by product, otherwise it only provides us with MAX value for an individual sale and not the total by product.

P

Hi All,

Here's another example creating a couple hold files. The resulting hold file named HLD_SUM2 can then be used to create several different reports depending on what the user might need.
-*****************************************************************************************
SET ASNAMES = ON
-*
TABLE FILE GGSALES
SUM
DOLLARS
UNITS
BY REGION
BY PRODUCT
ON TABLE HOLD AS HLD_SUM1 FORMAT ALPHA
END
-*
? HOLD HLD_SUM1
-*
TABLE FILE HLD_SUM1
SUM
DOLLARS AS 'RPT_DOLLARS'
MIN.DOLLARS WITHIN TABLE AS 'RPT_MIN_DOL'
MAX.DOLLARS WITHIN TABLE AS 'RPT_MAX_DOL'
UNITS AS 'RPT_UNITS'
SUM
DOLLARS AS 'REG_DOLLARS'
MIN.DOLLARS WITHIN REGION AS 'REG_MIN_DOL'
MAX.DOLLARS WITHIN REGION AS 'REG_MAX_DOL'
UNITS AS 'REG_UNITS'
BY REGION
SUM
DOLLARS
UNITS
BY REGION
BY PRODUCT
ON TABLE HOLD AS HLD_SUM2 FORMAT ALPHA
END
-*
? HOLD HLD_SUM2
-*
DEFINE FILE HLD_SUM2
RPT_MIN/A3 = IF DOLLARS EQ RPT_MIN_DOL THEN 'YES' ELSE ' ';
RPT_MAX/A3 = IF DOLLARS EQ RPT_MAX_DOL THEN 'YES' ELSE ' ';
REG_MIN/A3 = IF DOLLARS EQ REG_MIN_DOL THEN 'YES' ELSE ' ';
REG_MAX/A3 = IF DOLLARS EQ REG_MAX_DOL THEN 'YES' ELSE ' ';
-*
REG_PCT/D6.1 = (REG_DOLLARS / RPT_DOLLARS) * 100;
PRD_PCT/D6.1 = (DOLLARS / REG_DOLLARS) * 100;
END
-*
TABLE FILE HLD_SUM2
PRINT
DOLLARS
PRD_PCT
UNITS
RPT_MIN AS 'RPT,MIN'
RPT_MAX AS 'RPT,MAX'
REG_MIN AS 'REG,MIN'
REG_MAX AS 'REG,MAX'
BY RPT_DOLLARS
BY REGION
BY REG_DOLLARS
BY REG_PCT
BY PRODUCT
ON REGION SUBFOOT
" "
END
-****************************************************************************************

Jim