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,

Have you tried

BY FIELDNAME SUBTOTAL?

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

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

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.

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 

Thanks for the input. Does anyone know how to do this using the Info Assist gui? I do not have access to the code.

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,