Tim Ragan
asked on
Crystal Reports Conditional Sorting Question
I have a need to change the
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
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
You can also use sort controls: http://www.sdn.sap.com/irj/scn/index?rid=/library/uuid/60f4cde6-622e-2e10-559e-e09a04be13ff&overridelayout=true
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_I D}="09" then {Traveller.MATERIALS.M.DES CRIPTION} else tonumber({Traveller.MATERI ALS.M.REFE RENCE})
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.
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.
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.MATERI ALS.M.REFE RENCE})
into
ToText({Traveller.MATERIAL S.M.REFERE NCE},0,"")
This would yield the reference as a string with 0 decimals and no thousand separator.
into
ToText({Traveller.MATERIAL
This would yield the reference as a string with 0 decimals and no thousand separator.
ASKER
I get Too Many Arguements when I use this ToText setup. I need to to sort the values numerically. If this ToText({Traveller.MATERIAL S.M.REFERE NCE},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.REF ERENCE} ?
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 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".
ASKER
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.REF ERENCE} 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.
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.MATE RIALS.M.RE FERENCE}), '000000000')
would ensure the string is padded with up to 9 leading zeros, to ensure proper numeric sort on the resulting string.
For example, ToText(val({Traveller.MATE
would ensure the string is padded with up to 9 leading zeros, to ensure proper numeric sort on the resulting string.
ASKER
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This last formula worked. Problem solved. Thanks to both of you for the help.
BY the title I suspect you want to enter a parameter to change the field you sort on.
mlmcc