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     [SOLVED] How do I sort my report based on subtotal amounts?
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] How do I sort my report based on subtotal amounts?
 Login/Join
 
Member
posted
In our company, we have a standard of sorting reports in descending order based on groupings. For Example we have an exterior grouping of Product Line, then a sub group of Style.

I want to sort by the sales total of the Product Line, then within the Product Line by sales total of the Style. I can sort the subgroup of Style using the Rank function, but I cannot sort the exterior group. Any ideas?

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


WebFOCUS 8
Windows, All Outputs
 
Posts: 4 | Registered: May 09, 2016Reply With QuoteReport This Post
Silver Member
posted Hide Post
Have you tried

BY FIELDNAME SUBTOTAL?


WebFOCUS 8.1.05
Windows, All Outputs
 
Posts: 35 | Location: Minnesota | Registered: May 17, 2013Reply With QuoteReport This Post
Guru
posted Hide Post
Audrey Aulenti-Gray,

Welcome to Focal Point!

In order to be able to help you better please update your profile with the version of WebFOCUS you are using ie. WebFOCUS 8x or WebFOCUS 81x.

Here is a link to WebFOCUS 8105 InfoAssist documentation

You may also consider a subtotal for the Prodline - see page 176 , 179 has an example
Thank your for participating in the Focal Point Forum,
Tamra Colangelo
Focal Point Moderator
Information Builders


WebFOCUS 8x - BI Portal, Developer Studio, App Studio, Excel, PDF, Active Formats and HTML5
 
Posts: 487 | Location: Toronto | Registered: June 23, 2009Reply With QuoteReport This Post
Member
posted Hide Post
I know how to apply a subtotal. What I need to do is sort by said subtotals in descending order. I can't find any information on how to do this in IA


WebFOCUS 8
Windows, All Outputs
 
Posts: 4 | Registered: May 09, 2016Reply With QuoteReport This Post
Master
posted Hide Post
Presumably you want to sort on the subtotal of a single field.

You could do this by DEFINEing a new field equivalent to the data you want to subtotal on.

Then create a multi-verb report where the first part SUMs the new field thus creating a subtotal without using the subtotal command.

You can then use this fake subtotal as your sort field. You might have to take the output of this first report and HOLD it and then report off the hold file.


WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
 
Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010Reply With QuoteReport This Post
Silver Member
posted Hide Post
How about this for an example:

 TABLE FILE CAR
SUM 
     CAR.BODY.DEALER_COST
BY TOTAL HIGHEST CAR.BODY.DEALER_COST NOPRINT 
BY  CAR.ORIGIN.COUNTRY
SUM 
     CAR.BODY.DEALER_COST
BY TOTAL HIGHEST CAR.BODY.DEALER_COST NOPRINT 
BY  CAR.ORIGIN.COUNTRY
BY  CAR.COMP.CAR
BY  CAR.CARREC.MODEL
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K
END 


8009
Windows, HTML, AHTML, Excel
In FOCUS since 1983
 
Posts: 41 | Location: Charlotte, NC | Registered: January 06, 2012Reply With QuoteReport This Post
Member
posted Hide Post
Thanks for the input. Does anyone know how to do this using the Info Assist gui? I do not have access to the code.


WebFOCUS 8
Windows, All Outputs
 
Posts: 4 | Registered: May 09, 2016Reply With QuoteReport This Post
Gold member
posted Hide Post
You could use the within function and a hold to do so.


For example: Open up the "Car" sample table, Sort by CAR and Model and drop sales twice in to the sum. Rename the second Sales in the Sum to something else, I chose "WithinSales.



Now click on that field and the field tab will open, all the way over to the right there is and option called within. Click it and do it by CAR.



If you run that you can see that you now have sales Within each car brand (basically a subtotal).



Now Hold the table by clicking the file button on the home tab, do a temporary hold and name it as you please and hit save.



Hit create report at the bottom.



Now sort by WithinSales, car, and model and drag sales into the sum, change sort of withinsales to descending.



change the visibility of withinsales to hidden.



Enable Subtotals by car and you should be good.

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


WebFocus 8.2, IA+, Windows 10, HTML
 
Posts: 54 | Location: UT | Registered: December 01, 2015Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] How do I sort my report based on subtotal amounts?

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