Group Sorting by Formulas

[crystaluser10]

I do a group sort by a formula. The formula is not available to sort by.

The report has 2 groups. I am summarizing the data in group 1 then calculating a percentage.

Formula 1 DELQ (supressed in detail)
if {@groupDelqCode}="30 Days" or
{@groupDelqCode}="60 Days" or
{@groupDelqCode}="90 Days" or
{@groupDelqCode}="120+ Days"
then 1 else 0
Formula 2 percdelq (in group footer 1)
Sum ({@DELQ}, {@NOT Specified})/Count ({ReportsStatic.loannumber}, {@NOT Specified}) * 100

I am using formula 2 because I am able to create a sum showing as a percentage of the grand total but not the group total. I need to sort by formula 2.

Any suggestions?

 

[synapsevampire]

You should post what's in {@groupDelqCode} and {@NOT Specified}) since you reference them in your formula.

If you want to use an aggregate function against a formula, the formula must evaluate for each row, and not be an aggregate itself.

Posting that you're using some unknown entities and asking why it doesn't work will only net more questions, please try to be thorough in your posts.

You should also post basic background information, such as:

Crystal version
Database/connectivity used
Example data
Expected output

Showing a broken formula is less meaningful than what you have and what you need.

-k

 

[crystaluser10]

synapsevampire - Sorry, new at this.

{@NOT Specified} is just a group name formula I'm using:
if isnull({ReportsStatic.brokername}) or
{ReportsStatic.brokername}="" then " *Not Specified" else
{ReportsStatic.brokername} + " " +"-" + " " + {ReportsStatic.correspondentbrokercode}

and same with {@groupDelqCode}:
if{ReportsStatic.WarningCode}=0 then totext({@days})
else
if{ReportsStatic.WarningCode}=4 then totext("Foreclosure")
else
if{ReportsStatic.WarningCode}=5 then totext("BK CH 7 & 13")
else
totext({@days})


The formulas are working fine. I just need a to be able to sort by the results of these formulas. Wich is a percentage of a sum of a group.

Crystal version - 9
Database/connectivity used = OLE DB (ADO)
Example data - Broker X has totals 100 loans, 10 of the 100 meet the delquent criteria. 10% of this brokers loans are delq.
Expected output - the sort order needs to be decending by the delq % of the broker (Formula 2 @{percdelq} in group footer 1).

 

[lbass]

What is the content of {@days}? And is the {ReportsStatic.correspondentbrokercode} ever null, or is it only the broker name that can be null?

-LB

 

[crystaluser10]

lbass -
{@days}:
if {@datediff}<30 then totext("Current") else
if {@datediff}>=30 and {@datediff}<60 then totext("30 Days") else if {@datediff}>=60 and {@datediff}<90 then totext("60 Days") else if {@datediff}>=90 and {@datediff}<120 then totext("90 Days") else if {@datediff}>=120 then totext("120+ Days")

and
{@datediff}:
datediff ("d", {ReportsStatic.NextDueDate}, date ({ReportsStatic.AsOfDate}))

Yes the broker code can be null, I just use this to name the null brokers

 

[lbass]

You have to be able to return your formula 2 (your percent formula) as a hard number that you can place in the details section and insert a maximum on. This will allow you to use topN/group sort to order your groups. I was going to suggest using a SQL expression and it's almost doable in your case, but I'm not sure about handling the nulls. You could try the following:

First, set your report options to "convert nulls to default". Then create a SQL expression {%delq}:

(select count(AKA.`AsOfDate`) from ReportsStatic AKA where
AKA.`correspondentbrokercode` = ReportsStatic.`correspondentbrokercode` and
AKA.`WarningCode` not in (4,5) and
AKA.`NextDueDate` - AKA.`AsOfDate` >= 30)/
(select count(AKA.`AsOfDate`) from ReportsStatic AKA where
AKA.`correspondentbrokercode` = ReportsStatic.`correspondentbrokercode`) * 100

I used the AsOfDate field to represent a field that is not null. If it can be null, this wouldn't work correctly. After getting the SQL expression to work, you would place it in your detail section (even though it is a group level summary, it will have the same value wherever you place it). Then right click and choose insert summary->maximum. Then go to topN/group sort and select "maximum of {%delq}" as your topN field, descending.

This is a bit of a long shot because of the complexity and the nulls.

-LB