Crystal Reports Conditional Sorting Question

I have a need to change the
Tim RaganAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

WHat is your question?

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

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
Else if {?SortField} = "FirstName" then

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

Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

Tim RaganAuthor 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.
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
Change  tonumber({Traveller.MATERIALS.M.REFERENCE})
This would yield the reference as a string with 0 decimals and no thousand separator.
Tim RaganAuthor 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.
Ido MilletProfessor 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".
Tim RaganAuthor 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.
Ido MilletProfessor 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.
Tim RaganAuthor 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.
Ido MilletProfessor 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.
Does the formula get accepted?

You say the field is a text field.
If add a formula  like this

DO you get an error?

If not then try this for the sort formula

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

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Tim RaganAuthor Commented:
This last formula worked.  Problem solved.  Thanks to both of you for the help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.