[resolved]how do you add additional fields to a report to do the following ?

:check:I have aliased filelds coming in from a stored procedure in my crystal report.
The procedures themselves in addition to my adding a sort fields ascending in report returns the information in this format in regards to this field column of data

A
A+
B
C
D
E
NONE

My user informs me they want the data aligned in the rowset that has A+ to come before A. And this means using additonal fields to add to the report possibly numeric so that A+ = 1
A= 2 and b =3 and so on. How do I go about this exactly ?

Create a formula that adds a character to the single letter marks. Any character will do as long as it comes after the + sign in an alphabetic sort sequence. Something like

If Len({TableName.FieldName}) = 1 Then
{TableName.FieldName} + "A"
Else
{TableName.FieldName}

Then Sort the report by this formula. The formula doesn't need to appear anywhere on the report.

[Edit] Ooops. The + "A" portion won't work if you have Marks like A-, B- etc.

Is there a character between the + and - in an alphabetic sort? Try the comma whose ascii code is between + and -.

If Len({TableName.FieldName}) = 1 Then
{TableName.FieldName} + ","
Else
{TableName.FieldName}

Quote:

---

Originally Posted by brucevde

Create a formula that adds a character to the single letter marks. Any character will do as long as it comes after the + sign in an alphabetic sort sequence. Something like

If Len({TableName.FieldName}) = 1 Then
{TableName.FieldName} + "A"
Else
{TableName.FieldName}

Then Sort the report by this formula. The formula doesn't need to appear anywhere on the report.

[Edit] Ooops. The + "A" portion won't work if you have Marks like A-, B- etc.

Is there a character between the + and - in an alphabetic sort? Try the comma whose ascii code is between + and -.

If Len({TableName.FieldName}) = 1 Then
{TableName.FieldName} + ","
Else
{TableName.FieldName}

---

This didn't work. I inserted it into the report by placing it in a formula and nothing happened I received the same results. When SQL grabs data by way of stored procedure or query the nature of SQL dealing with strings will be to place the the rowset with the column sorted by A first before the the rowset with column A +. In my case the column reads
A - Tier 2
A+ - Tier 1
B -Tier 3
C - Tier 4
D - Tier 5
E - Tier 6
No Tier - No score

the dashes are just hyphens btw.

Are you saying the actual strings to sort are "A - Tier 2", "A+ - Tier 1", etc?

If no then

The theory works, you just need to figure out Crystal's sorting rules for punctuation, non-alphabetic characters etc. This works in SQL Server for the strings "A", "A-", "A+", eg.

SELECT *
FROM (Select 'D-' As Mark Union Select 'B' Union Select 'B+' Union Select 'A-' As Mark Union Select 'A' Union Select 'A+') As Marks
ORDER BY CASE Len(Mark) WHEN 1 THEN (Mark + ',') ELSE Mark END

But in Crystal - comes before + and , is last. Try this Crystal formula instead

Code:

---

stringVar curMark := Trim({Marks.Mark});

If Len(curMark) = 1 Then
  curMark := curMark + "1"
Else
(
curMark := Replace (curMark,"+" ,"0");
curMark := Replace (curMark,"-" ,"2");
);

curMark;

---

Quote:

---

Originally Posted by brucevde

Are you saying the actual strings to sort are "A - Tier 2", "A+ - Tier 1", etc?

If no then

The theory works, you just need to figure out Crystal's sorting rules for punctuation, non-alphabetic characters etc. This works in SQL Server for the strings "A", "A-", "A+", eg.

SELECT *
FROM (Select 'D-' As Mark Union Select 'B' Union Select 'B+' Union Select 'A-' As Mark Union Select 'A' Union Select 'A+') As Marks
ORDER BY CASE Len(Mark) WHEN 1 THEN (Mark + ',') ELSE Mark END

But in Crystal - comes before + and , is last. Try this Crystal formula instead

Code:

---

stringVar curMark := Trim({Marks.Mark});

If Len(curMark) = 1 Then
  curMark := curMark + "1"
Else
(
curMark := Replace (curMark,"+" ,"0");
curMark := Replace (curMark,"-" ,"2");
);

curMark;

---

---

This worked however I altered it so that
it did this

stringVar curMark := Trim({sp_v_velocity_Unsecured_Tier_Auto_reports;1.Tier});

If Len(curMark) = 1 Then
curMark := curMark + "1"
Else
(
curMark := Replace (curMark,"+" ,"");
curMark := Replace (curMark,"-" ,"1");
);

curMark;

so the the plus sign in the field goes away in the formula field and it marks the others with one. The way the formula above had it

it generated in this order A2
A02 for A +
B2
C2 etc which is still although however sequentially correct not the outcome I needed. Give me the point. :D