Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Sorting on an across total....
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] Sorting on an across total....
 Login/Join
 
Member
posted
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,


WF Release: WebFOCUS 8.0.09
Windows 7, HTML
 
Posts: 9 | Location: Peterborough, UK | Registered: July 09, 2012Reply With QuoteReport This Post
Gold member
posted Hide Post
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
 
Posts: 75 | Location: Dallas, TX | Registered: February 12, 2004Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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
 
Posts: 226 | Registered: June 08, 2003Reply With QuoteReport This Post
Member
posted Hide Post
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!


WF Release: WebFOCUS 8.0.09
Windows 7, HTML
 
Posts: 9 | Location: Peterborough, UK | Registered: July 09, 2012Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Sorting on an across total....

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.