Hi Everyone,

I am trying to sort (Descending) the output of a table based upon the row total..... This is my code:

What's the easist way to do this?

TABLE FILE VOLUMES_CUBE
SUM
VOLUMES_CUBE.VOLUMES_CUBE.Net_MTCs
BY VOLUMES_CUBE.VOLUMES_CUBE.Cancellation_Reason_Level_111
ACROSS VOLUMES_CUBE.VOLUMES_CUBE.Month_Name
WHERE VOLUMES_CUBE.VOLUMES_CUBE.Product_Type1 EQ 'Main';
WHERE VOLUMES_CUBE.VOLUMES_CUBE.Year EQ '&thisyear';
WHERE VOLUMES_CUBE.VOLUMES_CUBE.Brand_Name EQ '&Brand_Name.(FIND VOLUMES_CUBE.VOLUMES_CUBE.Brand_Name,VOLUMES_CUBE.VOLUMES_CUBE.Brand_Name IN volumes_cube).Brand_Name.';
WHERE VOLUMES_CUBE.VOLUMES_CUBE.Product_Class1 EQ '&Product_Class1.(FIND VOLUMES_CUBE.VOLUMES_CUBE.Product_Class1,VOLUMES_CUBE.VOLUMES_CUBE.Product_Class1 IN volumes_cube).Product Class.';
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE ROW-TOTAL AS 'TOTAL1'
ON TABLE COLUMN-TOTAL AS 'TOTAL2'
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
TYPE=REPORT,
GRAPHCOLOR='GREEN',
GRAPHCOLORNEG='RED',
$
TYPE=REPORT,
LINES-PER-PAGE=20,
ARGRAPHENGINE=FUSION,
$
TYPE=REPORT,
OBJECT=MENU,
COLOR='WHITE',
HOVER-COLOR=RGB(66 70 73),
BACKCOLOR=RGB(102 102 102),
HOVER-BACKCOLOR=RGB(218 225 232),
BORDER-COLOR='WHITE',
$
TYPE=REPORT,
OBJECT=STATUS-AREA,
COLOR='WHITE',
BACKCOLOR=RGB(102 102 102),
$
TYPE=REPORT,
OBJECT=CURRENT-ROW,
HOVER-BACKCOLOR=RGB(218 225 232),
BACKCOLOR=RGB(200 200 200),
$
TYPE=REPORT,
OBJECT=CALC-AREA,
COLOR='WHITE',
BACKCOLOR=RGB(102 102 102),
$
ENDSTYLE
END

Once this has been sorted then I'd like the top 10 highest values based upon the row totals in order to feed into a graph file

This message has been edited. Last edited by: Kerry,

Here's a thought ... try the multiple verb technique:

  
TABLE FILE CAR
SUM 
     DEALER_COST NOPRINT
     COMPUTE ROWTTL/D13 = DEALER_COST; NOPRINT
BY TOTAL HIGHEST ROWTTL NOPRINT
BY  COUNTRY    
SUM 
     DEALER_COST
BY TOTAL HIGHEST ROWTTL NOPRINT
BY  COUNTRY    
ACROSS LOWEST CAR AS ''
COMPUTE TOTAL/D13 = C1;
ON TABLE SET PAGE-NUM NOLEAD 
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
TYPE=REPORT,
     GRAPHCOLOR='GREEN',
$
TYPE=ACROSSVALUE,
     ACROSS=1,
     BACKCOLOR='WHITE',
$
TYPE=REPORT,
     OBJECT=MENU,
     COLOR='WHITE',
     HOVER-COLOR=RGB(66 70 73),
     BACKCOLOR=RGB(102 102 102),
     HOVER-BACKCOLOR=RGB(218 225 232),
     BORDER-COLOR='WHITE',
$
TYPE=REPORT,
     OBJECT=STATUS-AREA,
     COLOR='WHITE',
     BACKCOLOR=RGB(102 102 102),
$
TYPE=REPORT,
     OBJECT=CURRENT-ROW,
     HOVER-BACKCOLOR=RGB(218 225 232),
     BACKCOLOR=RGB(200 200 200),
$
TYPE=REPORT,
     OBJECT=CALC-AREA,
     COLOR='WHITE',
     BACKCOLOR=RGB(102 102 102),
$
ENDSTYLE
END

Cleaver solution. If you know the number of ACROSS columns here is another.

SUM Net_MTCs AND COMPUTE COLSUM=C1+c2+c3+C4; NOPRINT
ACROSS Month_Name AND ROW-TOTAL
BY HIGHEST 10 TOTAL COLSUM NOPRINT
BY ...

Thankyou both Mary and Gerry for your responses. I intially managed to get round this by hardcoding the hightest (most common) 5 reasons into the .fex. After this Alan Bell (our IB consultant) showed me how to achieve this top 5 view on an aggregated total using TABLE HOLD method. Albeit the solution was fairly complex due to the raw data being sourced directly from an SSAS cube!