I am using Crystal Reports XI.  I have an existing sales report that my Comptroller wants to have sorted by largest amount to smallest amount.

The issue I am having is that the field by which I need to sort it is a SUM formula and is not shown in the list of Available Fields in the Record Sort Expert.

The field is Adjusted_YTD_Invoices.  It looks like this:
sum({@CalcYTDInvoices})-sum({@CalcYTD_Credits})

It takes the results of two other formulas and subtracts one from the other to get the final result.  I need to sort, in descending order, by this final result.

How can I get my report to sort this way?



*Edited for redundancy


Edited by GrisCorp - 11 Apr 2013 at 5:42am

@ DBlank,
Thanks for your response.  I spent the better part of yesterday trying different things with groups and group sorts.  I'm having trouble getting this to work.  I'm new to CR and have no training in it, but I do want to learn.  Let me explain how this report is setup.

I open the report in CR.  It looks like this:
Report Header

(no information in this section and it is suppressed)

Page Header

Print Date field and Text Objects for Report Title and Column Titles

Group Header #1

Sub-report titled "Sales History by Customer_Sub.rpt"

Details

(no information in this section and it is suppressed)

Group Footer #1

(no information in this section and it is suppressed)

Report Footer

Sub-report titled "MTD_YTD_Sales".  (This is just a Grand Total at the end of the report for all sales for all customers for both the current and the prior year.  This one appears to be working properly.)

Page Footer

Page N of M field and Text Object with file name.

The data by which I want to sort is the data returned by the Sales History by Customer sub-report.  I open that up and it looks like this:

Report Header

(no information in this section and it is suppressed)

Group Header #1

(no information in this section and it is suppressed)

Details

(no information in this section and it is suppressed)

Group Footer #1

Field: CustID_23
Field: CustName_23
Formula: Adj_YTD_Invoices_LastYr
Formula: Adj_YTD_Invoices

Report Footer

(no information in this section and it is suppressed)

The Sub-report Links CUSTID_23 to parameter ?Pm-Cust_Master.CUSTID_23 and 'Select data in subreport based on field:' is checked and Cust_Master.CUSTID_23 is selected.

The formulas in the sub-report are as follows:
Adj_YTD_Invoices:

sum({@CalcYTDInvoices})-sum({@CalcYTD_Credits})

Adj_YTD_Invoices_LastYr:

Sum({@CalcYTDInvoices_LastYr})-Sum({@CalcYTD_Credits_LastYr})

CalcYTDInvoices:

if({Inv_Master.INVDTE_31} in YearToDate)
and {Inv_Master.STYPE_31}='CU'
then
{Inv_Master.LNETOT_31}-({Inv_Master.ORDDSC_31}

CalcYTD_Credits:

if({Inv_Master.INVDTE_31} in YearToDate)
and {Inv_Master.STYPE_31}='CR'
then
{Inv_Master.LNETOT_31}

CalcYTDInvoices_LastYr:

if({Inv_Master.INVDTE_31} in LastYearYTD)
and {Inv_Master.STYPE_31}='CU'
then
{Inv_Master.LNETOT_31}-({Inv_Master.ORDDSC_31}

CalcYTD_Credits_LastYr:

if({Inv_Master.INVDTE_31} in LastYearYTD)
and {Inv_Master.STYPE_31}='CR'
then
{Inv_Master.LNETOT_31}

The main report has a Group Sort that is sorted by @CalcYTDSales which does get the information in almost descending order.  What I mean is that the results are largely in descending value by the Adj_YTD_Invoices amounts.  It starts off with the three largest amounts in the correct order then the 10th largest, then 5th, 4th, 6th, 7th, 13th, 14th, 11th, 8th, 12th, 9th. You get the idea. It does mostly straighten out a few pages into the report.

There are no Record Selection Formulas in the Main Report.

The @CalcYTDSales formula in the Main report is:
if({Inv_Detail.INVDTE_32} in YearToDate)
and {Inv_Master.STYPE_31}='CU'
then
{Inv_Detail.PRICE_32}*{Inv_Detail.INVQTY_32}

OK.  I've created the formula in the Main report. 

I clicked on the Group Sort Expert and there is still only the one choice, Sum of @CalcYTDSales.  I tried putting the new formula in the body of the report, but it still does not show up as a choice in the Group Sort Expert.  Am I doing something wrong?

Got that done, but now the sort is even more jumbled than before.  This is making my brain hurt, so I'm going to put it aside for a few days.  Perhaps I can convince my Comptroller that either it's fine the way it is or to get some professional assistance from our contact at Exact Software as the report is pulling information from the Max database.

Also going to try to convince him I need training in CR.  THAT shouldn't be too hard! :-P

I truly appreciate your help thus far, DBlank.  I'm very good at sort of "reverse-engineering" things like this, these reports (taking them apart, figuring out how they work and improving upon them), but CR is proving to be my Achilles heel of late.  Maybe I'll have better luck with it next week after getting away from it for a bit.  I've been looking at this for three days straight, now, and there are just too many formulas and fields and databases all swirling around in my head at the same time.