how to dynamically sort records

[Former Member]

Hi Experts,

I have a requirement to sort the records based on the input parameter. If I have 4 fields like Acc No, Acc Nam, Date and Acc Type

I want to sort the records dynamically. Also, we need to sort either in Ascending order or Descending order based on another input parameter.

I can sort the records based on the first parameter, but couldn't order the records in Ascending or Descending order dynamically based on the second parameter.

Please suggest as how this can be achieved.

Thanks

Naresh

[Former Member]

If you are using CR2008 then you can insert sort controls on each column. If not then try the following steps

Symptom

In Crystal Reports, how do you sort on two or more data types that are based on values entered in a parameter field?

For example:

If you choose either "Name" or "City", which are string fields and "Customer ID" or "Last Year's Sales", which are numeric fields. How do you sort on two different data types using parameter fields?

Resolution

To create a sort using a parameter with two or more different data types, complete the following steps:

1. Create a formula for each different data type that you want to sort by.

For example:

If you wanted to sort by either Name, City, Sales, or ID. you must create two formulas:

@SortByNumberField

if {?SortBy} = "ID" then {Customer.Customer ID} else

if {?SortBy} = "Sales" then {Customer.Last Year's Sales} else 0

//sort by Customer ID if user chooses "ID" in parameter ?SortBy

//sort by Last Year's Sales if user chooses "Sales" in parameter, otherwise

//it's 0 that will not affect the sort

@SortByStringField

if {?SortBy} = "Name" then {Customer.Customer Name} else

if {?SortBy} = "City" then {Customer.City} else ""

//sort by Customer Name if user chooses "Name" in parameter

//sort by City if user chooses "City" in parameter, otherwise

//it's empty "" which will not affect the sort

2. From 'Report' select 'Sort Records'. Place both formula fields in the 'Sort Fields' window.

3. Refresh the report.

Now, Crystal Reports sorts according to what you chose in the parameter.

regards,

Raghavendra.G

[Former Member]

Thanks Raghavendra. Also, I need to have the sort order dynamically along with dynamic sorting of the fields. Is there anyway to dynamically have the sort order of the records.

If the user enters Sort By parameter as Acc Num and Sort Order parameter as Descending, then the records have to be sorted by Acc Num in Descending order. Please suggest.

Thanks

Naresh

[Former Member]

Add logic to the formulas shown above to use the "opposite" value as the formula value returned when the user selects descending sort. For numbers, this is easy: Just return the negative field value. One way to handle strings would be something like this (basic syntax):

dim sortval as number
dim temp as string
dim maxlen as number

maxlen = 20
sortval = 0
temp = mid({textfield} + replicate(" ", maxlen), 1, maxlen)

for i = 1 to maxlen
  sortval = (10 * sortval) - AscW(mid({textfield},i,1))
next i

formula = sortval

Then always sort on the formula field ASCENDING. (You could make this a function if needed in more than one place...)

HTH,

Carl