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
November 16, 2015, 08:28 AM
MartinY
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
November 16, 2015, 10:24 AM
iBeny
Does not work as intended, I need to show the Row Total for the Across as the last column and sorted in Descending Order.
Webfocus 8105,8808,7703,7611, EXL2K,HTML,PDF,COMT,AHTML Info Assist+ , Reportcaster
November 16, 2015, 11:57 AM
MartinY
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
November 16, 2015, 01:38 PM
Dan Satchell
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
November 16, 2015, 02:16 PM
MartinY
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
November 16, 2015, 02:20 PM
j.gross
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
November 16, 2015, 02:40 PM
MartinY
Much more easier than my solution Jack
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
November 17, 2015, 03:18 AM
iBeny
[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
November 17, 2015, 08:54 AM
MartinY
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
November 17, 2015, 09:36 AM
Joni Campbell - Europa Sports
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
November 17, 2015, 09:49 AM
j.gross
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.