Link to home
Start Free TrialLog in
Avatar of Tim Ragan
Tim RaganFlag for United States of America

asked on

Crystal Reports Conditional Sorting Question

I have a need to change the
Avatar of Mike McCracken
Mike McCracken

WHat is your question?

BY the title I suspect you want to enter a parameter to change the field you sort on.

mlmcc
There are 2 basic methods for achieving your sorting.

First method works if all the sort fields you want to use are the same type like strings and numbers

Create a parameter called SortField

Add a formula
Name - SortField
If {?SortField} = "LastName" then
    {LastNameField}
Else if {?SortField} = "FirstName" then
    {FirstNameField}
Else
    "A"

Second Method works when all the fields are not the same type
Add sort field formula as above for each type
Add a sort to the report for each sort formula
Add a parameter as noted

mlmcc
Avatar of Tim Ragan

ASKER

Thanks for the answers folks.   My apologies for the lack of an actual question on this post.  I joined up yesterday and started the post on my phone and did not realize I submitted it.

I have a report done in Crystal 8.5.  I did not write the report as it bundled in an application and, sadly, I cannot move it to a current version.

The report produces a material list by department.  When the report prints materials for each department on separate pages.  The departments are in a table by themselves.  The materials are another table and are grouped by field #1 (string), field #2 (numeric), and field #3 (numeric).  I need to change the sort based on the department.

Group Sort formula is:  if{Traveller.OPERATIONS.O.RESOURCE_ID}="09" then {Traveller.MATERIALS.M.DESCRIPTION} else tonumber({Traveller.MATERIALS.M.REFERENCE})

The tonumber conversion is where I fail.  I get a message indicating the value must be a string.  I know I'm missing something basic.
Change  tonumber({Traveller.MATERIALS.M.REFERENCE})
into
ToText({Traveller.MATERIALS.M.REFERENCE},0,"")
This would yield the reference as a string with 0 decimals and no thousand separator.
I get Too Many Arguements when I use this ToText setup.   I need to to sort the values numerically.  If this ToText({Traveller.MATERIALS.M.REFERENCE},0,"") did not produce errors would it sort properly?   I can get the conditional sort to work but not if I convert the data to numeric.
What is the data type of {Traveller.MATERIALS.M.REFERENCE} ?

The 2 IF THEN branches must return the same data type, so you just need to take care of that aspect.
Description sounds like a text field, so it's not clear what you mean by "I need to to sort the values numerically".
The field type I am trying use for the alternate sort is String.  The only values being entered into this field are numbers.  My intention in this effort is to have one departments list to sort numerically ascending on the numbers entered into the {Traveller.MATERIALS.M.REFERENCE} field.  The datatype is string.

Based on your comment that the If Then branch must return the same data type I won't be able to accomplish this sort sort change.  All departments but one need an alphanumeric sort on a different String field.  The one department needs their BOM list to sort ascending numerically.   The only field I have available to enter these values is the reference field.

Thanks for you help.
You should be able to convert the numbers in the REFERENCE field to a string that sorts according to the numeric values.

For example, ToText(val({Traveller.MATERIALS.M.REFERENCE}), '000000000')
would ensure the string is padded with up to 9 leading zeros, to ensure proper numeric sort on the resulting string.
Thank you.  I did get a sort on this field to work in another report I wrote just for testing.  I still don't get the result I'm looking for in the original document.  I suspect there is something in the grouping getting in my way.

Thanks again.
Place the formula on the report layout to see the values.  This would provide clear clues as to what is causing the wrong sort.
ASKER CERTIFIED SOLUTION
Avatar of Mike McCracken
Mike McCracken

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
This last formula worked.  Problem solved.  Thanks to both of you for the help.