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>,

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

Does not work as intended, I need to show the Row Total for the Across as the last column and sorted in Descending Order.

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

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

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.

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

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

[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.

iBeny, edit the FIRST post of this discussion and update the subject and add [SOLVED} at the beginning of the subject.

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 

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.