Is there a formula that will allow me to sort (in ascending order) a string type field that contains both numeric and text characters?

I am creating a report using Crystal Reports XI that should include a parameter to prompt the user to select a sort order for viewing the data (the data is being retrieved from an Oracle database).  

The first parameter option Case Number, should sort values retrieved from a CASE.DSCR database field in ascending order.  The CASE.DSCR field is a string field type which stores values in the following format:  2006 XYZ 001234.  

The second parameter option Days Pending, should sort records in descending numeric order based on the values returned from the @DaysPending formula which uses the DateDiff function.

These are the steps I have taken so far to accomplish this:
1. I created the {?ReportSort} parameter, string type, no value field entered.
2. I added an @SortGroup formula field with the following code:  If {?ReportSort} = "Case Number" Then ToNumber({CASE.CASE.DSCR})
Else
If {?ReportSort} = "Days Pending" Then {@DaysPending}
3. I inserted a new group using the @SortGroup formula field and I then used    the following formula as the sort order for the group:  
If {?ReportSort} = "Case Number" Then crAscendingOrder
Else
If {?ReportSort} = "Days Pending" Then crDescendingOrder

When I run the report selecting the Days Pending parameter option the report sorts the records correctly.  When I run the report selecting the Case Number parameter, the formula editor window appears on the screen and I get the error message The string is non-numeric, which references the  ToNumber({CASE.CASE.DSCR}) portion of the @SortGroup formula.  

Does anyone know of a way to get around this problem?
 


Janice SmithSystems AnalystAsked:
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:
There are 2 options I can think of.

Modify your sorting formula

If {?ReportSort} = "Case Number" Then
    {CASE.CASE.DSCR}
Else If {?ReportSort} = "Days Pending" Then
    Right('0000000000' & CStr({@DaysPending}),12)

That will convert the numbers to strings but make them all 12 digits long with leading 0s.  If they need to be longer just change the 12

Another idea is to sort on 2 formula fields
Formula 1 -
If {?ReportSort} = "Case Number" Then
    {CASE.CASE.DSCR}
else
    'A"

Formula 2
If {?ReportSort} = "Days Pending" Then
    {@DaysPending}
Else
    0

That way each will sort appropriately when chosen and not change the sort order if not chosen

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
UnifiedISCommented:
CASE.DSCR is not numeric so remove the "ToNumber"
0
mlmccCommented:
That is true, but then you have to change the other one to a string.  Crystal formulas can only return a single data type.

mlmcc
0
Janice SmithSystems AnalystAuthor Commented:
Wow!  Thank you!  The records are sorting correctly when either parameter is selected.
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
DB Reporting Tools

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.