Sorting problem with multiple datatype when i use a formula to sort.

[umeshs79]

Hi,
I am doing sorting depend upon report parameter.
Example given below. For that i have written a formula @SortBy. But the problem is that a formula can return only one type value. But my report column are multiple types e.g. string , date, Number etc.
So if i convert date or number into string the sorting is not proper for date or number columns.

@SortBy Formula

if {?orderByPrm} = "DT" then
//Problem not sorted properly.
cstr({OppReport.OppDate})
else if {?orderByPrm} = "CP" then
{OppReport.CustomerProspectName}
else if {?orderByPrm} = "OP" then
{OppReport.OpportunityName}
else if {?orderByPrm} = "SR" then
cstr({OppReport.SelectedRevenue})
else if {?orderByPrm} = "VO" then
cstr({OppReport.TotalValueOfRelatedProjectsAndSalesOrders})
//Problem not sorted properly.
else if {?orderByPrm} = "FB" then
cstr({@bestOFR}) //Problem not sorted properly.
else if {?orderByPrm} = "FW" then
cstr({@worstOFR}) //Problem not sorted properly.
else if {?orderByPrm} = "FM" then
cstr({@likelyOFR}) //Problem not sorted properly.
else if {?orderByPrm} = "NM" then
{OppReport.ResultOrNextMilestone}
else
""

So please tell me how i do the sorting no particular column
depnend upon report parameter.

Thanks Umesh Sharma,MCSD
India

 

[synapsevampire]

Try:

cstr({OppReport.OppDate},"yyyyMMdddd&quot

This will produce:

20020101 from 1/1/2002, which will sort.

As for converting a number to a string for sorting purposes, you'll might pad the string with the appropriate amount of zeros, just do a length check and concat &quot;000...&quot; + totext(<yourfield> accordingly (you might also consider cheating this by multiplying by an appropriate decimal precision).

You can always use the proper datatypes when displaying, these are just for sorting.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Ngolem]

for sorting numbers you have to pad the LHS with zero's so the easiest way to do that is using

//make &quot;0000000&quot; at least 2 digits longer than you expect
//to allow for unexpected large numbers

Totext({table.numberfield},&quot;0000000&quot;

SV - I find this confusing

&quot;You can always use the proper datatypes when displaying, these are just for sorting&quot;

Within a nested-If all datatypes should be the same or Crystal doesn't like it.

*******************************

else if {?orderByPrm} = &quot;FB&quot; then
cstr({@bestOFR}) //Problem not sorted properly.
else if {?orderByPrm} = &quot;FW&quot; then
cstr({@worstOFR}) //Problem not sorted properly.
else if {?orderByPrm} = &quot;FM&quot; then
cstr({@likelyOFR}) //Problem not sorted properly.
******************************

As far as this section goes you are sorting on a formula

Show us the formula(s) in question. One thing that cannot be present in these formulas is any evaluation function such as

&quot;WhilePrintingRecords&quot; or &quot;EvaluateAfter()&quot;

Are these formulas used elsewhere as well??? If not could they be incorporated into this formula...probably....show us the formulas in question. Jim Broadbent

 

[synapsevampire]

Ngolem:

&quot;SV - I find this confusing&quot;

&quot;You can always use the proper datatypes when displaying, these are just for sorting&quot;

Since we're creating formulas to allow for sorting, the original field can still be used when displaying so that you don't have padded 0's on the numbers and odd date formats, etc.

I think that Umesh understood the process of converting to strings just fine (he had already done as you suggested converting everything to a string), he just needed a means to allow for proper sorting, hence my date formatting and padding with zero suggestion, which you so kindly echoed.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Ngolem]

SV- Ahhh....now I understand what you are getting at....using the original field on the report itself....I was thinking of the tree when I made the comment.

Well, yes, I echoed your thoughts on the string/date but there are still the issues with the formulas that are used in the latter half of his formula which I don't believe you addressed Jim Broadbent