Can I Sort on a Summary field using a Parameter?? 1

[hawg]

Using SQL Server 7.0 and CRv 8.5

My report is grouped by a product Number (i.e. the left 4 characters of [SalesUSA].item). I want the sort order as a parameter field. My problem, as far as I can tell, is the two fields I want to sort on are summary fields.


Creating the formula field below I get: “A number is required here” and the cursor points to my summary field Sum( {... } )

If {?SortField} = "UnitsSold" Then
formula = Sum ({SalesUSA.OrderQuantity}, {@Left4OfItem})
Else
If {?SortField} = "GrossRevenue" Then
formula = Sum ({@SalesUSA.pric*SalesUSA. OrderQuantity}, {@Left4OfItem})

I wanted to place this formula field in the Top N/ Sort Group Expert.

Can I sort on a summary field?
Is there a work around?

Thanks in advance for any suggestions,
Mike

 

[kenhamady]

Your TopN has to use a sumaary field, not a formula. So do it this way:


Create a new formula called Choice:

If {?SortField} = "UnitsSold"
Then {SalesUSA.OrderQuantity}
Else {@SalesUSA.pric*SalesUSA.OrderQuantity}

Then do a summary of this field for each group, and use that summary as your TopN's summary field. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[hawg]

Ken,
Your method works great! Thanks for your response, it is very helpful.

One note: The order the fields appear in the IF ...Else must be considered, or a conversion of datatype needs to be used.

If {?SortField} = "UnitsSold"
Then {SalesUSA.OrderQuantity}
Else {@SalesUSA.pric*SalesUSA.OrderQuantity}

Will not work: Error = "A number is required here"
But switching the field order:

If {?SortField} = "Gross Revenue" Then
formula = ({SalesUSA.pric}*{SalesUSA.OrderQuantity})
Else
formula = {SalesUSA.OrderQuantity}
End If

Does work.
As does leaving the fields in the original order and using a type conversion:
CDbl{@SalesUSA.pric*SalesUSA.OrderQuantity}


Again thank you for your quick response.
Mike