Converting a charvar2 to number, decending order shows 100 after smallest number

[Former Member]

Using Crystal Reports 14.1.2.1121

I have student grades that are stored as varchar2, I convert them to numbers using

If NumericText ({HONORROLL.GPA}) then Val ({HONORROLL.GPA}) else 0  (I have also tried ToNumber)

But.. then Record Sort ascending or descending does not put the students who have 100% in the right spot.

See attached jpg

Thank you in advance for any help you can give me.

[abhilash_kumar · Accepted solution]

Hi Pegeen,

Does the report have a Group on any field?

-Abhilash

[Former Member · Accepted solution]

Yes, there is a grouping on what grade they are in.

[abhilash_kumar · Accepted solution]

Group Sorting takes precedence over Record Sorting.

Here's what you need to do:

1) Go to the Insert Summary option > Choose the formula you created to convert to number as the 'field to summarize' > Choose 'Maximum' as the sum function > Under 'Summary Location', choose Group Footer (Grade group)

2) Go to the Group Sort Expert > Select the Grade Group > Under 'for this group sort', choose 'All'

3) Under 'Based on', select the summary field and set the sort order as 'Descending'.

Hope this helps.

-Abhilash

[Former Member · Accepted solution]

This did not help at all.

It just stuck a 100 at the end of the group.

[abhilash_kumar · Accepted solution]

Could you also attach a screenshot of the report's design page please?

-Abhilash

[Former Member · Accepted solution]

[abhilash_kumar · Accepted solution]

OK. Could you go to the Record Sort Expert and add the @GPA formula to the sort list?

You should already see the 'Grade' group in there however, @GPA formula needs to be the very next field in that list.

If that doesn't work as well, would you be able to send the .rpt file 'with saved data' to my e-mail?

-Abhilash

[Former Member]

That worked. Thank you so much.