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
rporter45Asked:
Who is Participating?
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.

mlmccCommented:
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
0
Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
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.
0
rporter45Author Commented:
Hi there,

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

Have a great day,
CS
0
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

rporter45Author Commented:
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
0
mlmccCommented:
What currency field?

mlmcc
0
rporter45Author Commented:
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
0
mlmccCommented:
Try this

Use this for the field to sort on

Right("0000000000" & CStr({YourCurrencyField},2,""),10)

Another way is to have 2 sort formulas

Sort1 - Your current sort formula
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}  
Else
    "A"

Open in new window


Sort2
if {?Sort Parameter} = "Amount Asked" then
    {Amount Asked Field}
Else
    0

Open in new window


Use both formulas in the sort expert

mlmcc
0

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
rporter45Author Commented:
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
0
rporter45Author Commented:
Option 2 worked perfectly!

Thank you very much and have a good night,
CS
0
mlmccCommented:
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
0
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.