I have search Focal Point and was able to get this far with my search results.

The following code produces the following report.

RANK2	NAME2		ITEM_COUNT	Dollar Sales 
**	Atlanta		360		4,100,107 
	Chicago		360		3,924,401 
	Houston		360		3,714,978 
	Los Angeles	359		3,772,003 
	New Haven		360		3,782,049 
	New York		360		3,902,265 
	Orlando		360		3,923,215 
	San Francisco	360		3,870,258 
	Seattle		359		4,010,685 
	St. Louis		359		3,761,286 
Top 10 Total : 1	       3,597	        38,761,247 
	 Other Cities	720		7,395,043 
Grand Total               4,317	        46,156,290 

The results that I was expecting was to have the rank appear where the rank is less than 11 only. I got it to be removed where I need it removed but I can't get it to display when I need it.

Also, it appears that the GRP value is being displayed on the subtotal line "Top 10 Total : 1", is there a way to have the GRP value not appear on the subtotal line?


Under the Grand Total line I need to produce the percentage that the Top 10 Total is of the Grand Total. I found several examples on how to do it in columns, but not in a row.
It would be someting line the following:

% of Total represented by Top 10 83.32% 83.98%
Caclulations:
(3,597/4,317) *100 = 83.32%
(38,761,247/46,156,290)* 100 = 83.98%

TABLE FILE GGSALES
SUM
     CNT.SEQ_NO AS 'ITEM_COUNT'
     DOLLARS
 RANKED BY TOTAL HIGHEST DOLLARS NOPRINT
BY  CITY
WHERE RECORDLIMIT EQ 5000
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE HOLD AS SAMPLEHOLD FORMAT XFOCUS
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END

-SET &MAXLIMIT = '10';
-SET &SUBTOTALDESCRIPTION = 'Top ' | &MAXLIMIT | ' Total :';

DEFINE FILE SAMPLEHOLD
RANKA/A2=FTOA(RANK, '(I7)', RANKA);
GRP/I1=IF RANK GT &MAXLIMIT THEN 2 ELSE 1;
RANK2/A2=IF RANK GT &MAXLIMIT THEN '  ' ELSE RANKA;
NAME2/A20=IF RANK GT &MAXLIMIT THEN 'Other Cities' ELSE CITY;
TOT_SLS/D12=DOLLARS;

END
TABLE FILE SAMPLEHOLD
SUM
     ITEM_COUNT/I5C
     DOLLARS/I8C
BY  GRP NOPRINT
BY  RANK2
BY  NAME2

ON GRP SUBTOTAL
     ITEM_COUNT
     DOLLARS MULTILINES AS '&SUBTOTALDESCRIPTION'
WHERE RECORDLIMIT EQ 5000
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE COLUMN-TOTAL AS 'Grand Total'
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END

Any help would be greatly appreciated.

Thanks,

Ralph

This message has been edited. Last edited by: <Kathryn Henning>,

Use the newer FPRINT function over FTOA, add a couple of defined "buckets" to allow a summation of the values for group 1 then add compute .. noprint to get the percentages. Add the results into a subfoot. Simple method and only one way of doing it -

DEFINE FILE SAMPLEHOLD
RANKA/A2    = FPRINT(RANK, 'I2', RANKA);
GRP/I1      = IF RANK GT &MAXLIMIT THEN 2 ELSE 1;
RANK2/A2    = IF RANK GT &MAXLIMIT THEN '  ' ELSE RANKA;
NAME2/A20   = IF RANK GT &MAXLIMIT THEN 'Other Cities' ELSE CITY;
TOT_SLS/D12 = DOLLARS;
GRP1_CNT/D12 = IF GRP EQ 1 THEN ITEM_COUNT ELSE 0;
GRP1_SLS/D12 = IF GRP EQ 1 THEN DOLLARS ELSE 0;
END
TABLE FILE SAMPLEHOLD
SUM GRP1_CNT NOPRINT
    GRP1_SLS NOPRINT
    COMPUTE GRP1_PCNT1/D6.2% = GRP1_CNT / ITEM_COUNT * 100; NOPRINT
    COMPUTE GRP1_PCNT2/D6.2% = GRP1_SLS / DOLLARS * 100; NOPRINT
SUM
     ITEM_COUNT/I5C
     DOLLARS/I8C
BY  GRP NOPRINT
BY  RANK2 AS 'Rank'
BY  NAME2 AS 'Name'

-*ON GRP SUBTOTAL
-*     ITEM_COUNT
-*     DOLLARS MULTILINES AS '&SUBTOTALDESCRIPTION'
ON GRP SUBFOOT
     " <+0>&SUBTOTALDESCRIPTION <GRP1_CNT<GRP1_SLS"
     WHEN GRP EQ 1
ON TABLE SUBFOOT
     " <+0>Grand Total <TOT.ITEM_COUNT<TOT.DOLLARS"
     "% of Total represented by Top 10 <GRP1_PCNT1<GRP1_PCNT2"
WHERE RECORDLIMIT EQ 5000
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
-*ON TABLE COLUMN-TOTAL AS 'Grand Total'
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
  type=report, headalign=body, $
  type=subfoot, size=9, justify=right, color=red, $
  type=tabfooting, line=1, size=10, justify=right, backcolor=rgb(60 60 60), color=white, $
  type=tabfooting, line=2, size=9, justify=right, color=red, $
  type=tabfooting, line=2, item=1, colspan=2, $
ENDSTYLE
END

T

Edited to improve look of output.

This message has been edited. Last edited by: Tony A,

Thanks again, Tony A. This is doing exactly what I'm looking for.