Hi again - hate to be a pest and post twice in one day, but I've been wondering about something for awhile....and now I could really use it, if it's possible to do.

Is there any way to rank Row-Total in dollar descending order? I'm guessing it can only be done in the source code, but I'm not quite sure if it's possible.

Thanks!

Don't know about ROW-TOTAL but there is BY HIGHEST TOTAL. Then there's always the hold and re-sort option.

In it's simplest form, you can use:

TABLE FILE CAR
SUM SALES NOPRINT
BY TOTAL SALES NOPRINT
BY SEATS NOPRINT
SUM SALES
BY TOTAL SALES
BY SEATS
ACROSS COUNTRY ROW-TOTAL
END

or, more complicated:

TABLE FILE CAR
SUM RCOST NOPRINT 
    DCOST NOPRINT
COMPUTE FRED = RCOST+DCOST; NOPRINT
BY TOTAL FRED NOPRINT
BY SEATS 
SUM RCOST DCOST 
BY TOTAL FRED NOPRINT
BY SEATS
ACROSS COUNTRY ROW-TOTAL
END

I tried that code, but it didn't work the way I was wanting, but thanks. Here's my code:

TABLE FILE INVOICE_DATA
SUM
SHIPPED_QUANTITY AS 'Units'
GROSS_DISC_EXT/D12.2CM AS 'Sales'
BY REP1_KEY AS 'Rep Key'
BY REP_NAME AS 'Rep Name'
BY CUSTOMER_CODE AS 'Acct Num'
BY CUSTOMER_DESC AS 'Customer Name'
ACROSS CALENDAR_MONTH AS ''

ON REP1_KEY SUBHEAD
"ON REP1_KEY SUBTOTAL AS 'TOTAL'
HEADING
""
FOOTING
""
WHERE CALENDAR_YEAR EQ 2008;
WHERE REP_TYPE EQ 'TD';
ON TABLE SET PAGE-NUM OFF
ON TABLE ROW-TOTAL AS 'TOTAL'
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='ARIAL',
SIZE=9,
$
TYPE=DATA,
COLUMN=ROWTOTAL(1),
STYLE=BOLD,
$
TYPE=DATA,
COLUMN=ROWTOTAL(2),
STYLE=BOLD,
$
TYPE=TITLE,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=ROWTOTAL(1),
STYLE=BOLD,
$
TYPE=TITLE,
COLUMN=ROWTOTAL(2),
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=SUBTOTAL,
BY=1,
SIZE=10,
STYLE=BOLD,
$
TYPE=ACROSSVALUE,
SIZE=9,
$
TYPE=ACROSSVALUE,
ACROSS=1,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=ACROSSVALUE,
COLUMN=ROWTOTAL(1),
STYLE=BOLD,
$
TYPE=ACROSSTITLE,
STYLE=BOLD,
$
TYPE=ACROSSTITLE,
ACROSS=1,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=GRANDTOTAL,
BACKCOLOR=RGB(210 210 210),
STYLE=BOLD,
$
ENDSTYLE
END

What I was looking for was a way to sort the Row-totals in dollar descending by the subtotaled Rep.

Any ideas?

PROD: WebFocus 7.6.9 on WinXP

quote:

ON REP1_KEY SUBHEAD
"FOOTING
""

what is this doing?

I'm sorry, when I copied the code, I must have done something wrong. Here's what it says:

ON REP1_KEY SUBHEAD
"ON REP1_KEY SUBTOTAL AS 'TOTAL'
HEADING
""
FOOTING
""

PROD: WebFocus 7.6.9 on WinXP

OK, I just sent this response with the corrected code and it's showing incorrect again in the Post.

I'm copying the correct code. I'll try again:

ON REP1_KEY SUBHEAD
"ON REP1_KEY SUBTOTAL AS 'TOTAL'
HEADING
""
FOOTING
""

PROD: WebFocus 7.6.9 on WinXP

ok, I'm typing it out this time instead of copying it:


ON REP1_KEY SUBHEAD
"ON REP1_KEY SUBTOTAL AS 'TOTAL'
HEADING
""
FOOTING
""

PROD: WebFocus 7.6.9 on WinXP

I'm sorry, it looks right when I type it in here, but when I post it, it changes the code.

Is this a common problem in this forum?

I don't know how to get this to you, Praire.

Your code should be posted between
[ code]
codetext
[ /code]

just leave out the spaces

What sosterle was trying to post was:

TABLE FILE INVOICE_DATA
SUM
     SHIPPED_QUANTITY AS 'Units'
     GROSS_DISC_EXT/D12.2CM AS 'Sales'
BY REP1_KEY AS 'Rep Key'
BY REP_NAME AS 'Rep Name'
BY CUSTOMER_CODE AS 'Acct Num'
BY CUSTOMER_DESC AS 'Customer Name'
ACROSS CALENDAR_MONTH AS ''

ON REP1_KEY SUBHEAD
"<REP1_KEY <REP_NAME "
ON REP1_KEY SUBTOTAL AS 'TOTAL'
HEADING
""
FOOTING
""
WHERE CALENDAR_YEAR EQ 2008;
WHERE REP_TYPE EQ 'TD';
ON TABLE SET PAGE-NUM OFF
ON TABLE ROW-TOTAL AS 'TOTAL'
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
     GRID=OFF,
     FONT='ARIAL',
     SIZE=9,
$
TYPE=DATA,
     COLUMN=ROWTOTAL(1),
     STYLE=BOLD,
$
TYPE=DATA,
     COLUMN=ROWTOTAL(2),
     STYLE=BOLD,
$
TYPE=TITLE,
     STYLE=BOLD,
     JUSTIFY=CENTER,
$
TYPE=TITLE,
     COLUMN=ROWTOTAL(1),
     STYLE=BOLD,
$
TYPE=TITLE,
     COLUMN=ROWTOTAL(2),
     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=SUBTOTAL,
     BY=1,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=ACROSSVALUE,
     SIZE=9,
$
TYPE=ACROSSVALUE,
     ACROSS=1,
     STYLE=BOLD,
     JUSTIFY=CENTER,
$
TYPE=ACROSSVALUE,
     COLUMN=ROWTOTAL(1),
     STYLE=BOLD,
$
TYPE=ACROSSTITLE,
     STYLE=BOLD,
$
TYPE=ACROSSTITLE,
     ACROSS=1,
     STYLE=BOLD,
     JUSTIFY=CENTER,
$
TYPE=GRANDTOTAL,
     BACKCOLOR=RGB(210 210 210),
     STYLE=BOLD,
$
ENDSTYLE
END

sosterle, can you explain in more detail what you mean by:

quote:

What I was looking for was a way to sort the Row-totals in dollar descending by the subtotaled Rep.

I am sure that what you want is easily doable, but could you post your expected output in simple terms.

I think what you are looking for is:
TABLE FILE INVOICE_DATA
SUM
SHIPPED_QUANTITY AS 'TOT_UNITS' NOPRINT
BY REP1_KEY
SUM
SHIPPED_QUANTITY AS 'Units'
GROSS_DISC_EXT/D12.2CM AS 'Sales'
BY HIGHEST TOT_UNITS NOPRINT
BY REP1_KEY AS 'Rep Key'
BY REP_NAME AS 'Rep Name'
BY CUSTOMER_CODE AS 'Acct Num'
BY CUSTOMER_DESC AS 'Customer Name' ROW-TOTAL
ACROSS CALENDAR_MONTH AS ''

ON REP1_KEY SUBHEAD
"ON REP1_KEY SUBTOTAL AS 'TOTAL'

Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes

Thanks for responding. I'm responding to each of you.

Pat - I got an No HTML output error saying that the fieldname TOT_UNITS wasn't recognized. I copied your code from your post. What am I doing wrong?

Alan - I was wanting to do the following:

Rep A Jan Feb Mar Total
1234 Company ABC 1000 1000 1000 3000
5678 Company DEF 500 500 500 1500
9000 Company GHI 1500 1500 1500 4500

And rank them by dollar descending, with the Total column being a Row-Total:

Rep A Jan Feb Mar Total
9000 Company GHI 1500 1500 1500 4500
1234 Company ABC 1000 1000 1000 3000
5678 Company DEF 500 500 500 1500

I hope that makes more sense.

Sosterle, try:

TABLE FILE INVOICE_DATA
SUM
COMPUTE R_TOTAL/D12.2=GROSS_DISC_EXT; NOPRINT
BY HIGHEST TOTAL R_TOTAL NOPRINT     
BY REP1_KEY NOPRINT
BY REP_NAME NOPRINT
BY CUSTOMER_CODE NOPRINT
BY CUSTOMER_DESC NOPRINT
SUM
     SHIPPED_QUANTITY AS 'Units'
     GROSS_DISC_EXT/D12.2CM AS 'Sales'
BY HIGHEST TOTAL R_TOTAL NOPRINT     
BY REP1_KEY AS 'Rep Key'
BY REP_NAME AS 'Rep Name'
BY CUSTOMER_CODE AS 'Acct Num'
BY CUSTOMER_DESC AS 'Customer Name'
ACROSS CALENDAR_MONTH AS ''
COMPUTE ROW_TOTAL/D12.2CM = R_TOTAL; AS TOTAL

ON REP1_KEY SUBHEAD
"<REP1_KEY <REP_NAME "
ON REP1_KEY SUBTOTAL AS 'TOTAL'
HEADING
""
FOOTING
""
WHERE CALENDAR_YEAR EQ 2008;
WHERE REP_TYPE EQ 'TD';
ON TABLE SET PAGE-NUM OFF
ON TABLE ROW-TOTAL AS 'TOTAL'
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET STYLE *
.
.
.

Hi Alan -

I copied your code and it brought back some interesting results. Is there anywhere I can send three spreadsheets so that you can see what results I'm needing? I ran one that is what happens with the current code, then I altered the results in Excel to show what I need it to look like, and then I ran one with the results from your code. I edited them all down to one page each.

Thanks.