Solved

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

Posted on 2008-10-23
4
454 Views
Last Modified: 2013-11-15
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
Comment
Question by:jsmith08
  • 2
4 Comments
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 22789838
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
 
LVL 18

Expert Comment

by:UnifiedIS
ID: 22789892
CASE.DSCR is not numeric so remove the "ToNumber"
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 22790185
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
 

Author Closing Comment

by:jsmith08
ID: 31509372
Wow!  Thank you!  The records are sorting correctly when either parameter is selected.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Crystal Reports with Yardi error 800a004b 2 121
Crystal Report Date Range Parameters 2 60
Mask on Parameter CR2008 28 52
Cognos BI 7.5 calculated date syntax 2 44
1. Set up your parameter at the report level as usual, check the box Multi-value, and set the Data Type to String 2. Set the Stored Procedure Parameter to varchar(max)  --<---- This part here is the key to it's success Example:    @cst_key var…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question