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.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED]Sort on the Basis of Row Totals (Across)

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED]Sort on the Basis of Row Totals (Across)
 Login/Join
 
Gold member
posted
Hi
I have a requirement where in user wants to change the sort order as follows:
sort by row totals, descending, then by module and dtc

Below is the current report output:


This is the TABLE Request for above:
TABLE FILE SQLOUT
SUM
CNT.DTC_KEY AS 'DTC_COUNT'
BY MODULE_ID
BY &DTC_BYTE AS 'DTC'
ACROSS VEHICLE
END

Could you please suggest the possible solution to achieve the sort order desired.

This message has been edited. Last edited by: <Emily McAllister>,


Webfocus 8105,8808,7703,7611, EXL2K,HTML,PDF,COMT,AHTML Info Assist+ , Reportcaster
 
Posts: 64 | Location: Toronto, Ontario | Registered: May 15, 2014Report This Post
Virtuoso
posted Hide Post
May not be the only solution, but this is one.
You need to calculate the "across" total first then add the information to your "detail" data :
TABLE FILE CAR
SUM SEATS AS 'TSEATS'
BY COUNTRY
ON TABLE HOLD AS TOT FORMAT FOCUS INDEX COUNTRY
END
-RUN

JOIN COUNTRY IN CAR 
  TO COUNTRY IN TOT TAG J1 AS J1
END

-*-* Total on left side BEFORE key column
-*TABLE FILE CAR
-*SUM SEATS
-*BY HIGHEST TSEATS AS 'Total Seats'
-*BY COUNTRY
-*ACROSS MODEL
-*END
-*-RUN

-* Total on left side AFTER key column
TABLE FILE CAR
SUM SEATS
BY HIGHEST TSEATS NOPRINT
BY COUNTRY
BY TSEATS AS 'Total Seats'
ACROSS MODEL
END
-RUN


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Gold member
posted Hide Post
Does not work as intended, I need to show the Row Total for the Across as the last column and sorted in Descending Order. Frowner


Webfocus 8105,8808,7703,7611, EXL2K,HTML,PDF,COMT,AHTML Info Assist+ , Reportcaster
 
Posts: 64 | Location: Toronto, Ontario | Registered: May 15, 2014Report This Post
Virtuoso
posted Hide Post
I don't think that there is a straight way to perform an 'order by' on the row total (ACROSS TOTAL).

So you need to fake it by calculating your own ACROSS TOTAL, order it and finally add it to the detailed rows.
-* CREATE TOTAL SEATS PER COUNTRY
TABLE FILE CAR
SUM SEATS
BY COUNTRY
BY TOTAL COMPUTE IDMODEL /I3  = 999;
BY TOTAL COMPUTE   MODEL /A24 = 'Total Seats';
ON TABLE HOLD AS TOT FORMAT FOCUS
END
-RUN

-* CREATE COUNTRY ORDER PER THEIR SEATS TOTAL
TABLE FILE TOT
BY SEATS NOPRINT
BY TOTAL COMPUTE IDCOUNTRY /I3 = IDCOUNTRY + 1;
BY COUNTRY
ON TABLE HOLD AS TOTORD FORMAT FOCUS INDEX COUNTRY
END
-RUN

-* EXTRACT TOTAL SEATS PER COUNTRY & MODEL
TABLE FILE CAR
SUM SEATS
BY COUNTRY
BY TOTAL COMPUTE IDMODEL /I3 = IDMODEL + 1;
BY MODEL
ON TABLE HOLD AS DET FORMAT FOCUS
END
-RUN

-* MERGE DETAIL AND TOTAL
TABLE FILE DET
SUM SEATS
BY COUNTRY
BY IDMODEL
BY MODEL
ON TABLE HOLD AS MRG FORMAT FOCUS
MORE
FILE TOT
END
-RUN

-* ADD COUNTRY ORDER PER TOTAL SEATS
JOIN COUNTRY IN MRG
  TO COUNTRY IN TOTORD TAG J1 AS J1
END

TABLE FILE MRG
SUM SEATS
BY IDCOUNTRY   NOPRINT
BY COUNTRY
ACROSS IDMODEL NOPRINT
ACROSS MODEL
END
-RUN


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Virtuoso
posted Hide Post
An example using the CAR file:

TABLE FILE CAR
 SUM SALES
 BY COUNTRY
 BY CAR
 BY MODEL
 ON TABLE HOLD AS TOTSALES
END
-*
TABLE FILE TOTSALES
 SUM SALES
 BY SALES NOPRINT
 BY CAR
 BY MODEL
 ACROSS COUNTRY
 ON TABLE ROW-TOTAL
END


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Virtuoso
posted Hide Post
I thought about it Dan, but this way you have multiple rows where BMW, JAGUAR and ALPHA ROMEO are displayed at several places between other car's row. The same cars are not grouped together.


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Virtuoso
posted Hide Post
See if this does the trick:
TABLE FILE SQLOUT

SUM
  CNT.DTC_KEY NOPRINT
COMPUTE MODTOT/I9C=CNT.DTC_KEY; NOPRINT
  BY TOTAL MODTOT NOPRINT
  BY MODULE_ID
  BY &DTC_BYTE AS 'DTC'

SUM
  CNT.DTC_KEY AS 'DTC_COUNT'
  BY TOTAL MODTOT NOPRINT
  BY MODULE_ID
  BY &DTC_BYTE AS 'DTC'
    ACROSS VEHICLE ROW-TOTAL AS 'MODULE TOTAL'
END


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Virtuoso
posted Hide Post
Much more easier than my solution Jack Good One
TABLE FILE CAR
SUM SEATS AS 'TOT SEATS' NOPRINT
    COMPUTE TSEATS / I4 = SEATS; NOPRINT
BY TOTAL TSEATS
BY COUNTRY

SUM SEATS
BY TOTAL TSEATS NOPRINT
BY COUNTRY
ACROSS MODEL ROW-TOTAL
END
-RUN

That's what I was trying to perform without success : combining MULTI-VERB with ROW-TOTAL


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Gold member
posted Hide Post
[SOLVED]: The Multiverb Request by Jack Worked like a Charm, Thanks a lot for the help, Seems like there is a lot to learn after 4 years of the Development experience in my Kitty.
Thanks a lot Gurus.


Webfocus 8105,8808,7703,7611, EXL2K,HTML,PDF,COMT,AHTML Info Assist+ , Reportcaster
 
Posts: 64 | Location: Toronto, Ontario | Registered: May 15, 2014Report This Post
Virtuoso
posted Hide Post
iBeny, edit the FIRST post of this discussion and update the subject and add [SOLVED} at the beginning of the subject.


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Silver Member
posted Hide Post
How about this?

 TABLE FILE CAR
SUM 
     CAR.BODY.DEALER_COST
BY TOTAL HIGHEST CAR.BODY.DEALER_COST NOPRINT 
BY  CAR.ORIGIN.COUNTRY
BY  CAR.COMP.CAR
ACROSS CAR.BODY.SEATS
ON TABLE ROW-TOTAL AS 'TOTAL'
ON TABLE PCHOLD FORMAT EXL2K
END 


8009
Windows, HTML, AHTML, Excel
In FOCUS since 1983
 
Posts: 41 | Location: Charlotte, NC | Registered: January 06, 2012Report This Post
Virtuoso
posted Hide Post
Try it without the NOPRINT and see what you get. I think you'll find the two-verb approach, with aggregation at nested levels (whether in one multi-verb request, or joining to an initial HOLD file) is essential.
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED]Sort on the Basis of Row Totals (Across)

Copyright © 1996-2020 Information Builders