Sort records based on formula with nested formulas

[ciscowiz]

I want to sort the records in my report by a formula which is comprised of other formulas. I want to sort on @Balance but it does not show up in the Record Sort Expert. @Balance = (@FinalCurrent + @Over30 + @Over60 + @Over90)

@FinalCurrent =
currencyvar xCur;
xCur := (Sum ({@Past90}, {v_CollectionsReport.Name}) + Sum ({@Past60}, {v_CollectionsReport.Name}) + Sum ({@Past30}, {v_CollectionsReport.Name}) + Sum ({@Current}, {v_CollectionsReport.Name}) - Sum ({@Credits}, {v_CollectionsReport.Name}) );

if xCur < 0
then
0
else
if xCur < Sum ({@Current}, {v_CollectionsReport.Name})
then
xCur
else
Sum ({@Current}, {v_CollectionsReport.Name})

@Over30 =

currencyvar x30;

x30 := (Sum ({@Past90}, {v_CollectionsReport.Name}) + Sum ({@Past60}, {v_CollectionsReport.Name}) + Sum ({@Past30}, {v_CollectionsReport.Name}) - Sum ({@Credits}, {v_CollectionsReport.Name}) );

if x30 < 0
then
0
else
if x30 < Sum ({@Past30}, {v_CollectionsReport.Name})
then
x30
else
Sum ({@Past30}, {v_CollectionsReport.Name})


@Over60 =
currencyvar x60;

x60 := (Sum ({@Past90}, {v_CollectionsReport.Name}) + Sum ({@Past60}, {v_CollectionsReport.Name}) - Sum ({@Credits}, {v_CollectionsReport.Name}) );

if x60 < 0
then
0
else
if x60 < Sum ({@Past60}, {v_CollectionsReport.Name})
then
x60
else
Sum ({@Past60}, {v_CollectionsReport.Name})


@Over90 =
currencyvar x90;

x90 := (Sum ({@Past90}, {v_CollectionsReport.Name}) - Sum ({@Credits}, {v_CollectionsReport.Name}) );

if (Sum ({@Past90}, {v_CollectionsReport.Name}) <> 0) and (Sum ({@Credits}, {v_CollectionsReport.Name})<>0)
then
if x90 < 0
then
0
else
x90
else
Sum ({@Past90}, {v_CollectionsReport.Name})

@Credits =
if {v_CollectionsReport.Type} = 'Payment' or {v_CollectionsReport.Type} = 'CreditMemo'
then
{v_CollectionsReport.OpenAmount}
else
0

The @FinalCurrent does not show up in the Record Sort Expert but the rest do. Any help would be appreciated.

Thanks,
Bryan

 

[Madawc]

You can sort using a formula field that derives several different values. But only if they are all from a single 'row', a single detail line. Not with 'sum' values.

You can sort groups using the Top N function.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10