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
452 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hi, In my previous Article  (http://www.experts-exchange.com/Database/Reporting/A_15199-Introduction-to-Microstrategy.html)I discussed some basic understanding of Microstrategy that how we can get in Intro of Microstrategy (MSTR). Now it's tim…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

919 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now