Sort by Formula

[dancar]

I have a report which lists customers and displays a value that is the result of a number of formulas within the report.

The boss wants to sort the report by this value descending. I'm not aware of how Crystal can sort by values which are not available in the source data.

Is there a way to do this?

 

[lbass]

You would have to show us the content of the formula (and of any nested formulas within it).

-LB

 

[dancar]

This formula creates the value I want to sort by:

//@Projected
If {@BusDaysThisMonth}>0 Then
({#MTD}/{@BusDaysThisMonth}) * 21.75
Else
({#MTD}/{@BusDaysLastMonth}) * 21.75

Formulas referred to:

//BusDaysThisMonth
// Determine number of business days so far this month (excluding Saturdays, Sunday)
Local numbervar i := 0;
Local numbervar Total := 0;

If Month(CurrentDate) = Month(DateAdd("d",-1,CurrentDate)) Then

For i := 1 to datepart("d",CurrentDate)-1 //to yesterday
Do (if DayOfWeek(date(Year(CurrentDate), Month(CurrentDate),i)) in 2 to 6
Then Total := Total + 1);
Total;

//Running Total #MTD
If {@BusDaysThisMonth} > 0 Then
Month({@Createdate})= Month(CurrentDate)
Else
(If Month(CurrentDate) = 1 Then
Month({@Createdate})= 12
Else
Month({@Createdate})= Month(CurrentDate)-1)

 

[lbass]

I don't see the content of {@Createdate] or {@BusDaysLastMonth}. You reference a running total {#MTD} but did not explain how it was setup. You need to replace the running total in order to do a sort. If you explain the setup, we can see whether that will be possible.

-LB

 

[dancar]

//CreateDate Create_On added to source data only recently,
// so Due_on is used on older records.
If isnull({WORKORDER_CSV.CREATED_ON}) or {WORKORDER_CSV.CREATED_ON} = "" then
{WORKORDER_CSV.DUE_ON}
Else date({WORKORDER_CSV.CREATED_ON})

//BusDaysLastMonth
Local numbervar i := 0;
Local numbervar Total := 0;
//Number of Days in the previous Month:
Local numbervar MonthEnd := datepart("d",DateAdd("d",-1,Date(Year(CurrentDate),Month(CurrentDate),1))-1);

(If Month(CurrentDate) > 1 Then
For i := 1 to MonthEnd //to last day of last month
Do (if DayOfWeek(date(Year(CurrentDate), Month(CurrentDate)-1,i)) in 2 to 6
Then Total := Total + 1)
Else //If previous month is December
For i := 1 to MonthEnd //to last day of last month
Do (if DayOfWeek(date(Year(CurrentDate)-1,12,i)) in 2 to 6
Then Total := Total + 1));
Total;

The #MTD Running total is a count of records coming in, evaluated on the formula at the end of my previous message and resets on a change of the group. So if I replace the running total with a var = var+1 I can sort on a Formula value derived from it?

 

[lbass]

What you would like to do is really a group sort, which requires that you insert a summary on the field you want to sort on (in order to activate the group sort feature). Even if you convert the running total to a summary based on a conditional formula, you still won't be able to insert a summary on it because the formula already contains a summary. The solution is to convert the {#MTD} to a summary using a SQL expression, but this becomes more complex than I can tackle because of your test for # of business days.

-LB

 

[dancar]

Are you saying that essentially the entire report must be written as a SQL query?

 

[lbass]

No. If you were able to recreate the {#MTD} as a SQL expression (in the field explorer), you would then be able to plug the expression into your {@Projected} formula and then insert a summary (maximum) on it, which would activate the group sort.

-LB

 

[dancar]

The SQL Expression option (Crystal 2008) is greyed out. This may be because my source data is a CSV text file and not a formal SQL or Access file. Am I out of luck?

 

[lbass]

I think so, unless you can create the summary as a field in your database.

-LB

 

[dancar]

I found another solution. I did a Save As on the original report making a new version with the Report Header, Page Header and Report Footer suppressed. I'm using a 3rd Party schedule/export tool to export the report to an Excel file. A 3rd Crystal report uses this Excel file as it's data source, sorting by the value that was calculated in the first report.

Maybe not the slickest solution but it produces the desired result.

 

[lbass]

Quite a few steps, but it's a clever workaround. Glad you got it to work and that you explained how.

-LB