Hi everyone,

I've found bits and pieces on this forum with regards to ranking, but nothing seems to fit exactly what I need. I have a report that it taking the top 100 customers per division based on the customer's sales $. I now need to add a line at the bottom that gives me a TOTAL for ALL customers (not just the Top 100 that are displayed). I've tried the PLUS OTHERS, but that totals columns that I don't need. Here is the simplified code - if anyone can help me with this, I would appreciate it!!

DEFINE FILE SALES_CURR_PREV_FY_US
DIVISION/A25=
IF SALES_CURR_PREV_FY_US.SALES_CURR_PREV_FY_US.PR_BR EQ '3' THEN 'Hercules Division'
ELSE IF SALES_CURR_PREV_FY_US.SALES_CURR_PREV_FY_US.PR_BR EQ '7' OR '8' OR '9' OR '10' OR '11' OR '12' OR '13' OR '14' OR '15' OR '16' OR '17' OR '18' OR '19' THEN 'TDW Division'
ELSE IF SALES_CURR_PREV_FY_US.SALES_CURR_PREV_FY_US.PR_BR EQ '20' OR '21' OR '22' OR '24' OR '26' OR '28' THEN 'Hercules Canada'
ELSE 'Hercules International';
END
TABLE FILE SALES_CURR_PREV_FY_US
SUM 
     'SALES_CURR_PREV_FY_US.SALES_CURR_PREV_FY_US.QTY'
     'SALES_CURR_PREV_FY_US.SALES_CURR_PREV_FY_US.EXTENDED_FET_PRICE'
     COMPUTE GP/D12.2 = ( SALES_CURR_PREV_FY_US.SALES_CURR_PREV_FY_US.EXTENDED_FET_GP_DLRS / SALES_CURR_PREV_FY_US.SALES_CURR_PREV_FY_US.EXTENDED_FET_PRICE ) * 100;
BY DIVISION
BY 'SALES_CURR_PREV_FY_US.SALES_CURR_PREV_FY_US.CUSTOMER_ID'
BY 'J3.CUSTOMER_US.NAME'
BY 'J3.CUSTOMER_US.CITY'
BY 'J3.CUSTOMER_US.STATE'
ON TABLE SET PAGE-NUM OFF 
ON TABLE SET BYDISPLAY ON 
ON TABLE NOTOTAL
ON TABLE HOLD AS RANKHOLD FORMAT ALPHA
ON TABLE SET HTMLCSS ON
END


TABLE FILE RANKHOLD
-*ON TABLE PCHOLD FORMAT EXL2K BYTOC 1
-*ON 'RANKHOLD.RANKHOLD.DIVISION' SUBFOOT
-*""
-*"<'RANKHOLD.RANKHOLD.DIVISION' Total Sales: <CT.RANKHOLD.RANKHOLD.EXTENDED_FET_PRICE"
PRINT 
     'RANKHOLD.RANKHOLD.CUSTOMER_ID' AS 'Account ID'
     'RANKHOLD.RANKHOLD.NAME' AS 'Customer Name'
     'RANKHOLD.RANKHOLD.CITY'
     'RANKHOLD.RANKHOLD.STATE' AS 'ST/PROV'
     'RANKHOLD.RANKHOLD.QTY'
     'RANKHOLD.RANKHOLD.EXTENDED_FET_PRICE/P12.2CM' AS 'Sales'
     'RANKHOLD.RANKHOLD.GP'
BY 'RANKHOLD.RANKHOLD.DIVISION' NOPRINT
BY HIGHEST 100 'RANKHOLD.RANKHOLD.EXTENDED_FET_PRICE' NOPRINT
HEADING
"<RANKHOLD.RANKHOLD.DIVISION Sales for &StartDate through &EndDate "
" "
" "
ON TABLE SET PAGE-NUM OFF 
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET COMPOUND 'BYTOC 1'
ON TABLE SET HTMLCSS ON
ENDSTYLE
END
  

This message has been edited. Last edited by: jseaburn,

Look into the DB_LOOKUP function (DB_LOOKUP: Retrieving a Value From a Lookup Data Source).

Here's a working example. I first create a hold file - HSTATE1 - containing the total values. Then I use DB_LOOKUP in a DEFINE and put that total in a SUBFOOT. (For some reason I had to create an intermediary HOLD file - HSTATE2 - otherwise the WF agent crashed, might have something to do wiith the demo file I used for this example, and you shouldn't have to do this).

SET ASNAMES=ON
-RUN

TABLE FILE CENTORD
SUM
LINEPRICE AS TOTSTATEPRICE
BY STATE
ON TABLE HOLD AS HSTATE1 FORMAT ALPHA
END
-RUN

?FF HSTATE1
-RUN

TABLE FILE CENTORD
SUM
LINEPRICE
BY STATE
BY LINEPRICE NOPRINT
ON TABLE HOLD AS HSTATE2 FORMAT FOCUS INDEX STATE
END
-RUN

DEFINE FILE HSTATE2
TOTSTATEPRICE/D12.2MC = DB_LOOKUP(HSTATE1, STATE, STATE, TOTSTATEPRICE);
END
-RUN

TABLE FILE HSTATE2
SUM
LINEPRICE AS 'LINEPRICE'
BY STATE  AS 'STATE'
BY HIGHEST 5 LINEPRICE NOPRINT

ON STATE SUBFOOT
"TOTAL FOR THE STATE <MIN.TOTSTATEPRICE"

HEADING
"TOP 5 BY STATE"

ON TABLE SET PAGE NOLEAD
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
TYPE=REPORT, SQUEEZE=ON, FONT='ARIAL', SIZE=8, BORDER=1, BORDER-COLOR=SILVER, $
TYPE=HEADING, STYLE=BOLD, $
TYPE=TITLE, STYLE=BOLD, COLOR=NAVY, $
TYPE=SUBFOOT, HEADALIGN=BODY, JUSTIFY=RIGHT, $
TYPE=SUBFOOT, OBJECT=TEXT, JUSTIFY=LEFT, $
ENDSTYLE
END
-RUN

DEFINE FILE CENTORD CLEAR
END
-RUN

Thank you always, Francis! I actually got this to work using a multi-verb request and placing the subtotals in SUBFOOTs. I am familiar with the DBLOOKUP function but had not thought about it for this report. I will keep it in my toolbox for future needs. Thank you!

The reason I suggested to use the DB_LOOKUP method was that I thought the BY HIGHEST would affect the multi-verb request (like a WHERE would), but it doesn't, so a multi-verb request is the simpler method.

I'm happy you found the solution yourself.

Cheers,