Formula to convert string to number that will sort alphabetically 3

[cheyney]

Hey,

I'm using Crystal Reports 8.5.

I've been on this problem a while. I need the ability to dynamically change the sort order of a group based on a input parameter. The group has to sort either by sum(price) or alphabetically by its title.

Its automatic that it sorts by its title, no problem.
I can make it sort by the sum of the price by adding a sub total to the group and using the Top N/Sort Group Expert.

Now I need a way to change this based on a parameter.
If I could turn off the sorting defined in the Top N/Sort Group Expert at runtime based on the boolean parameter, I would be done. I'm assuming that isn't possible, but I'd certainly like to hear that it is

Assuming its not, what I've determined I need is this: A way to convert a string to a number so that sorting that number results in the same ordering as sorting the original string alphabetically, case INsensitive.

I'm working on an algorithm right now, using a sum of the weighted ascii values of the characters in the string, based on position. If anyone has done this before, or knows a standard way to do this, or can think of an easier solutions to my problem, i'm all ears

Thanks in advance,
Cheyney

 

[Madawc]

You can sort using a formula field that picks up its data from two or more possible sources, depending on a parameter. But I think you have to have all numeric or all alpha.

Another option is to have a group within group, the numeric within the alpha, say. For numeric usage there is only one alpha value, and for alpha usage there is only one numeric value, meaning that each alpha group has just one of them.



Madawc Williams (East Anglia, UK)

 

[synapsevampire]

You have several ways to accomplish this.

As Madawc pointed out, you can have an optional sortation of an outer group which is a Formula rather than the text field which has something like:

If {?sortParm} = "Text" then
{table.field}
Else
"All"

Now you can conditionally suppress this group as well.

Or you can create a valid 0000 padded number for sortation using something like:

ReplicateString("0",20-len(totext(sum({table.field}),0,""))+totext(sum({table.field}),0,"")

This latter answers your question of sorting a numeric as a string filed, assuming that it's less than 20 characters as a numeric.

-k

 

[cheyney]

Madawc,

Thanks for your response. Your first paragraph was what I assumed, and its what I was going for with the algorithm to convert a string to a sortable number.

I didn't quite understand what you meant by your second paragraph, but it game me an idea. First I tried creating a group for the sum(Price), but you can't group on a sum. Then I tried creating a second group on Title (had to do this by creating a formula field that = title, since groups can't be created on the same field) and sorting that by the sum(price). I then had 2 groups on Title, one sorted by title, one sorted by sum(price), and I suppressed either one or the other based on my parameter.

This didn't solve my problem because the sorting of the outside group always took precidence, even when it was suppressed. Is there any way to dynamically remove a grouping altogether?

Cheyney

 

[cheyney]

synapsevampire,

Thanks for your response as well.

What I think was misunderstood is that I don't want to sort the records within a group by the title or price, I want to sort the group entries by either the title (the grouped field) or the SUM(price).

So assume the following data:

A 1.00
A.1 20.00
A.2 -19.00
B 17.00
B.1 3.00
B.2 12.00
B.3 2.00
C -5.00
C.1 3.00
C.2 -8.00

(A, B, C) are Titles, (1.00, 17.00, -5.00) are sum(prices)

I want to present these groups in either this order:
A,B,C (order by title)
or this order
C, A, B (order by sum(price))

The only way I know to sort a group by a sum field is by using the Top N/Sort Group Expert, but this won't let you choose a formula field that contains a sum, only a sum of a formula field.

I tried something similar earlier with converting numerals to padded strings (handling negative numbers involves subtracting their absolute values from a large number, so you sort the ABS descending), but it didn't work because I couldn't sort a group by a formula field that contained a sum.

How large a number can Crystal's NumberVar hold? How precise is it?

Thanks,
Cheyney

 

[lbass]

Create a parameter {?groupsort} with two defaults: "Sum Price" and "Title". Then create a formula {@grpsort}:

if {?groupsort} = "Sum Price" then {table.price} else 0

Place this in the details section, insert a summary (sum) on it, and suppress the formula and its summary. Also insert a summary (sum) on {table.price}.

Next go to report->topN/group sort and choose "Sum of {@grpsort}" ascending or descending (depending on your needs).

Since you have a group on {table.title}, this will be your default order and will appear if "Title" is selected as the default option. If "Sum Price" is selected, the topN sort will do the group sort by price.

-LB

 

[cheyney]

ooh la la, sounds promising. I'll try it as soon as I get a chance.

Another slight twist on this, the "Sum Price" sort has to be DESC and the "Title sort has to be ASC, but I should just be able to just change the then expression in the if statement to -1 * {table.price} and emulate a DESC sort.

great, I'll let you know how it goes.

Cheyney

 

[lbass]

No, all you have to do is set the topN to descending. Don't change the formula. Since the topN is only kicking in when you choose "sum price", you can have topN descending, while Title will sort ascending, if chosen.

-LB

 

[cheyney]

Bang on with both counts, lbass. Thanks so much,

Cheyney