Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 483
  • Last Modified:

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?
 


0
jsmith08
Asked:
jsmith08
  • 2
1 Solution
 
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
 
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
 
jsmith08Author Commented:
Wow!  Thank you!  The records are sorting correctly when either parameter is selected.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now