Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


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?

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] How do I sort my report based on subtotal amounts?
 Login/Join
 
Audrey Aulenti-Gray
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, 2016Report This Post
Msondra
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, 2013Report This Post
Tamra
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, 2009Report This Post
Audrey Aulenti-Gray
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, 2016Report This Post
George Patton
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, 2010Report This Post
Joni Campbell - Europa Sports
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, 2012Report This Post
Audrey Aulenti-Gray
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, 2016Report This Post
Cimmerian
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: 56 | Location: UT | Registered: December 01, 2015Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

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-2020 Information Builders