# Crystal Reports Conditional Sorting Question

I have a need to change the
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:

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

mlmcc
Commented:
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

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

mlmcc
Professor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
Author Commented:
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.
Professor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
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.
Author Commented:
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.
Professor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
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".
Author Commented:
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.
Professor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
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.
Author Commented:
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.
Professor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
Place the formula on the report layout to see the values.  This would provide clear clues as to what is causing the wrong sort.
Commented:
Does the formula get accepted?

You say the field is a text field.
If add a formula  like this
tonumber({Traveller.MATERIALS.M.REFERENCE})

DO you get an error?

If not then try this for the sort formula

`````` if{Traveller.OPERATIONS.O.RESOURCE_ID}="09" then
{Traveller.MATERIALS.M.DESCRIPTION}
else
Right ("00000000" + {Traveller.MATERIALS.M.REFERENCE}, 8)
``````

mlmcc

Experts Exchange Solution brought to you by