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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

707 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

18 Experts available now in Live!

Get 1:1 Help Now