asked on

Multiple Sort Options

Hi there,

Our team would like to be able to sort on one of the following possible fields;

Last Name, Strategic Plan Ranking, Strategic Plan Rating, Cultivation Window and Solicitation Target Date. All are string fields.

I have created a static parameter that includes each of these and then the formula below;
if {?Sort Parameter} = 'Sort Name' then {CnBio.CnBio_Sort_name}
else if {?Sort Parameter} = "Strategic Plan Ranking" then {CnPrProp_1AttrCat_2.CnPrProp_1AttrCat_2_Description}
else if {?Sort Parameter} = "Strategic Plan Rating" then {CnPrProp_1AttrCat_3.CnPrProp_1AttrCat_3_Description}
else if {?Sort Parameter} = "Cultivation Window" then {CnPrProp_1AttrCat_4.CnPrProp_1AttrCat_4_Description}
else if {?Sort Parameter} = "Solicitation Target Date" then {CnPrProp_1AttrCat_5.CnPrProp_1AttrCat_5_Description}

When I run the report, I can select a value for the parameter but the report doesn't seem to sort. I'm not sure where this went wrong - any help would be appreciated,

Thank you - CS

Mike McCracken

Did you add the formula as the sort in REPORT --> RECORD SORT EXPERT

DO you have groups?
Groups are the first level of sorting

mlmcc

Kurt Reinhardt

FYI, If you're using Crystal Reports 2008 or above, you can just add sort controls to the column headers. This allows the end user to simply click an up or down button to sort the report within a viewer. Of course, this depends on using a viewer, as opposed to scheduling a report for printing.

rporter45

ASKER

Hi there,

Thanks very much for the responses, I will get back soon!

Have a great day,
CS

rporter45

ASKER

Hi there,

That was the missing piece - I removed the group, it was unnecessary and then added the formula to the sort. I do notice that the currency field is of course not sorting correctly based on the field type but rather on the parameter type. Can this be changed as well?

Thanks - CS

Mike McCracken

What currency field?

mlmcc

rporter45

ASKER

One of the sort options that I need to have in the report is called Amount Asked and was just added as part of the request. The parameter however is a string because all of the other options are strings. Of course, the sort is now off for Amount Asked.

Is there a way to fix this?

Thanks
CS

ASKER CERTIFIED SOLUTION

Mike McCracken

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rporter45

ASKER

Hi there,

I just tried the first option but it didn't work - I used the following;

if {?Sort Parameter} = "Sort Name" then {CnBio.CnBio_Sort_name}
else if {?Sort Parameter} = "Rating" then {CnPrProp_1AttrCat_3.CnPrProp_1AttrCat_3_Description}
else if {?Sort Parameter} = "Cultivation Window" then {CnPrProp_1AttrCat_4.CnPrProp_1AttrCat_4_Description}
else if {?Sort Parameter} = "Solicitation Target Date" then {CnPrProp_1AttrCat_4.CnPrProp_1AttrCat_4_Description}
else if {?Sort Parameter} = "Amount Asked" then Right("0000000000" & CStr({CnPrProp_1.CnPrProp_1_Amount_Asked},2,""),10)

There are values from $0.00 to $10,000,000.

I will try the other to see if that will help

thank you -
CS

rporter45

ASKER

Option 2 worked perfectly!

Thank you very much and have a good night,
CS

Mike McCracken

I find it useful when there are different data types to create one sort formula for wach type otherwise the order gets messed up by the alphabetizing or the conversion to string. I find it is always better to sort based on the native datatype.

If one was a date field then you can use Today as the default value or any date like Date(1900,1,1)

mlmcc