Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.



Read-Only Read-Only Topic
Go
Search
Notify
Tools
Ranking Row Total
 Login/Join
 
Gold member
posted
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!


PROD: WebFocus 7.6.9 on WinXP
 
Posts: 59 | Registered: October 31, 2006Report This Post
Virtuoso
posted Hide Post
Don't know about ROW-TOTAL but there is BY HIGHEST TOTAL. Then there's always the hold and re-sort option.


Regards,

Darin



In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Virtuoso
posted Hide Post
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


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Gold member
posted Hide Post
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
 
Posts: 59 | Registered: October 31, 2006Report This Post
Virtuoso
posted Hide Post
quote:
ON REP1_KEY SUBHEAD
"FOOTING
""

what is this doing?


In Focus since 1993. WebFOCUS 7.7.03 Win 2003
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Report This Post
Gold member
posted Hide Post
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
 
Posts: 59 | Registered: October 31, 2006Report This Post
Gold member
posted Hide Post
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
 
Posts: 59 | Registered: October 31, 2006Report This Post
Gold member
posted Hide Post
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
 
Posts: 59 | Registered: October 31, 2006Report This Post
Gold member
posted Hide Post
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.


PROD: WebFocus 7.6.9 on WinXP
 
Posts: 59 | Registered: October 31, 2006Report This Post
Virtuoso
posted Hide Post
Your code should be posted between
[ code]
codetext
[ /code]

just leave out the spaces




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Virtuoso
posted Hide Post
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.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Master
posted Hide Post
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
 
Posts: 755 | Location: TX | Registered: September 25, 2007Report This Post
Gold member
posted Hide Post
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.


PROD: WebFocus 7.6.9 on WinXP
 
Posts: 59 | Registered: October 31, 2006Report This Post
Virtuoso
posted Hide Post
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 *
.
.
.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Gold member
posted Hide Post
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.


PROD: WebFocus 7.6.9 on WinXP
 
Posts: 59 | Registered: October 31, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic


Copyright © 1996-2020 Information Builders