Parameter sets order of a string value

[SUSANNE]

Hi to all,

I`ve already searched the whole forum, but did not find an answer to my problem.

I want to give the user of my report the possibility to set the sorting criteria of the displayed data himself. Therefor I created one Parameter {?ORDER}. This has already defined default values (for each criteria asc and desc). The user can choose multiple of this criteria.

Here a simple example for only one criteria: The user wants to sort by "business unit desc" (=string!!!).

To find out, how the user wants the data to be sorted, I created the following formula {@ORDER1}:

--------------------------------------------------------
...
if ({?ORDER})[1] = "Business units asc" then
({BusinessUnit.Name}) //string!!!
else
-({BusinessUnit.Name}) //string!!!
...
--------------------------------------------------------

To this formula {@ORDER1} I want to refer in the SortingManager (don't know how it is called in the English version, sorry).

My problem know is:
The "-" operator in the above formula does not work. CR tells me that a number or a currency value is expected after "-".
Is there another possibility to set the sort order of a string descending?

I use CR 8.5...
Thanks for any help!

 

[Ngolem]

if ({?ORDER})[1] = "Business units asc" then
({BusinessUnit.Name}) //string!!!
else
-({BusinessUnit.Name}) //string!!!
***********

I see your problem You are applying a math operator to a string and that does not apply to a string

even doing this won't work ("-" + {BusinessUnit.Name}} since it will still look at the second char since the first always the same...ie. "-"

It may be possible to make a formula that can convert the opposite order

It would be messy perhaps but I am just brainstorming here.

You would have to have all possible Business Units and then add a first character in the opposite end of the alphabet.

Maybe not....let us look at the first 3 characters of a name and give them opposite values

Eg. if the first 3 chars were "abc" then we would convert that to "zyx"....

Yeah....that might work! Probably we would need at least 10 chars in case there were some Business units with similar names

NOTE: you don't use "whileprintingrecords" here.

@ChangeBUOrder

StringVar Result := "";
numberVar pointer;

for pointer := 1 to 10 do
(
If uppercase({BusinessUnit.Name}[pointer]) = "A" then
Result := Result + "Z"
else If uppercase({BusinessUnit.Name}[pointer]) = "B" then
Result := Result + "Y"
else If uppercase({BusinessUnit.Name}[pointer]) = "C" then
Result := Result + "X"
else If uppercase({BusinessUnit.Name}[pointer]) = "D" then
Result := Result + "W"
.
.
.
else If uppercase({BusinessUnit.Name}[pointer]) = "Z" then
Result := Result + "A";
);

Result;

ONE CAUTION Make sure that you don't specify more letters than are in the Business Name itself...ALSO I have not accounted for non-letters in the Business Name...ie numbers or things like dashes or brackets...if those exist then you will have to decide on an opposite character...or just leave then as they are perhaps...though if they are the first char that might be a problem for you.

Anyway, this approach might work...it doesn't matter if the "Result" is not the Business unit name since that is only for grouping urposes

this would now be your Grouping formula

@Group1

if ({?ORDER})[1] = "Business units asc" then
({BusinessUnit.Name})
else
@ChangeBUOrder;



Jim Broadbent

 

[SUSANNE]

Hi Jim,


thank you very much for your response. Thought there could be a function doing that stuff for me... I`ll give it a try and will inform you on how it works...


In thanks,
Susanne

 

[kenhamady]

A simpler alternative is to have 2 sort fields assigned back to back. Each is a formula field, one is set to Ascending and the other to Descending. You then replace one of the 2 sort formulas with a constant and the other with the field (or vice versa).

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[SUSANNE]

Hi Jim, hi Ken,

thank you very much for your proposals. I finally solved my problem using Kens suggestion. But I was facing another problem, which I want to share with you, because I think it could be interesting for somebody else, too....

Let's start the story:
My first post was only half the truth... I wanted to give the user the opportunity to select the order of sorting the data himself. For example first to sort on business units (asc/desc) and afterwards on User-IDs (asc/desc) or vice versa... (using C++)

Using Ken`s solution brought the problem, that I wanted to make the user able to sort numeric AND/OR string values, depending on what the user decided. (but Crystal only allows that one formula gives back a numeric OR a string value... uaaahhh!) So I decided to give back a string value everytime.

But:
ToText(1000) was shown in the report before ToText(2) sorting ascending. Due to this reason I used the following in my formulas:

--------------------------------------------------------
// If numeric value:
Space(254 - Length(ToText({RMX12.ENT1208}))) + ToText({RMX12.ENT1208})
--------------------------------------------------------

Little bit weird, but it works...

Greetings,
Sue

 

[kenhamady]

You could use:

Totext ({Field}, "########&quot

(the number of pound signs is based on the number of digits in the largest possible number in this field). When you cross data-types, you have to convert non-strings into a text values that will sort correctly.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com