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 fileThis message has been edited. Last edited by: Kerry,
WF Release: WebFOCUS 8.0.09 Windows 7, HTML
August 15, 2012, 10:04 AM
Mary Watermann
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
WF 7.6.10, Windows, PDF, Excel
August 15, 2012, 10:59 AM
GCohen
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 ...
Release 7.6.9 Windows HTML
September 20, 2012, 06:25 AM
OliHewitt
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!