Sorting by Formula Field Within a Group

[heatherk]

I have created a report with 5 groups: Region, Salesperson, Category, Corporate Customer, and Customer. After sending out the reports for the first time last month the salespeople decided that they would like the Corporate Customer and Customer Groups to be sorted by the YTD Quantity Column. This column as well as others in the report are all done using running totals. The formulas do not appear in the Record Sort Order Dialogue Box. Is there some way that I can sort the way they want it? Is there a formula that can be inserted some where in the Section Expert Dialogue Box? Any help is much appreciated!
Thanks in Advance,
Heather

 

[MalcolmW]

What is your database and CR version number?

 

[heatherk]

I am using CR version 6 and we run off an Oracle Database using PeopleSoft, so I get my CR tables from queries written using the PS QueryTool.

 

[MalcolmW]

If you do your running totals in the query, and return the value as part of the data set, then SCR will be able to sort by them.
Not a trivial solution, but CR can only sort records based on a field or formula that can be evaluated using one row at a time. A running total calculated in CR is by definition something that uses more than one record to get a result, so it cannot be used for sorting purposes.

 

[heatherk]

I have tried to create a running total column in my query but I keep bombing out on the sql. Could you possibly give me an example sql expression. I have tried it many ways. For example :
select region_cd, sales_person, category, corporate_cust, cust, sum(qty), sum(amount), sum(select d.qty from ps_l_sales_history d where year = 2000 and month <= 7) from...
Any ideas??

 

[MalcolmW]

See my recent FAQ re running totals.
If that doesn't do it, post your SQL statement in SQL forum - either myself or someone else will help you.