How to remove sort order

[TonyScarpelli]

Using CR in VB .NET 2003.

I have a report that pulls data from a table.
The table is sorted correctly, but when I view the report, the rows are not sorted the way I want.

Either I have to get rid of the sort order on the report, or I have to get CR to sort it correctly.

I have a text field in the table that gets either a date ('01/01/2005') or text like 'RUSH' or 'ASAP'. I want normal order to be reversed so that 'RUSH' is first 'ASAP' is next, and the dates next.

Since I can't seem to get rid of the sorts (must have done it when I created the report) what do I need to do?

Thanks.


Tony Scarpelli
Clinical Engineering Dept.
Maine Medical Center
Portland, Maine 04102

 

[TonyScarpelli]

Figured it out.
The group is what controlled the sort.
I removed the group. Added a new group based on the field in descending order and it worked.

Thanks.

CU


Tony Scarpelli
Clinical Engineering Dept.
Maine Medical Center
Portland, Maine 04102

 

[Madawc]

There are also options for sorting groups in a specified order, rather than ascending or descending. I'd do it by right-click and selecting 'Change Group', though how you'd do it from VB may be different.

Another option is to define your group with a prefix: a) 'RUSH', b) 'ASAP', c) '01/01/2005'.

Madawc Williams (East Anglia, UK)

 

[TonyScarpelli]

Madawc,

<<define your group with a prefix: a) 'RUSH', b) 'ASAP', c) '01/01/2005'>>

Good idea, but how is that done?

Thanks.


Tony Scarpelli
Clinical Engineering Dept.
Maine Medical Center
Portland, Maine 04102

 

[Madawc]

You'd do it in a formula field, and than group on that field. Any formula field can be used for grouping, so long as it only involves one 'row', the data for a detail line.

Note that the date would have to be ToText({your.date}, "dd/MM/yyyy"), because Crystal will not mix numbers or dates with 'string'

Madawc Williams (East Anglia, UK)

 

[TonyScarpelli]

I was able to 'Change Group Options' and create 'Named Groups'.

That worked the way I wanted, with 'FEDEX' at the top and 'RUSH' next, etc., however the dates (left over in 'Other', aren't in the correct decending order.

The 'RequestDate' field, which I base the group on, _is_ a text field and the dates are already set as text.

Any idea how I can get the dates in the right order? They are all right in the temp table I put them in and in the Selected output.

Thanks.


Tony Scarpelli
Clinical Engineering Dept.
Maine Medical Center
Portland, Maine 04102

 

[bdreed35]

The dates are not in the right order because of how they are formatted.
If you want them in the correct order, you will need them in yyyymmdd format for the sort.

One way to do this is to create a formula to group on:

select {table.field}
case "FEDEX" : "1" & {table.field}
case "RUSH" : "2" & {table.field}
default : "3" & {table.field}[7 to 10] & {table.field}[1 to 2] & {table.field}[4 to 5]

Group on this formula Ascending.
In the Group Options dialog, under Options, click Customize Group Name, and then in the drop down, use the original field to display in the Group Name field.

If you need any other entries in the formula, add them and change the numbers accordingly.

~Brian

 

[TonyScarpelli]

After looking at my stored procedure and checking out the dates, it looks like the dates (because they are text) are being sorted in ASCII order.

Looks like I might have to separate the query into two different selections, one for the dates, and one for the other text, and then combine them.

Sound reasonable?

Thanks.

Tony Scarpelli
Clinical Engineering Dept.
Maine Medical Center
Portland, Maine 04102

 

[TonyScarpelli]

OK, fixed the sorts.

I did the sort in the query using substring() on the year/month/day.

That got the dates sorted correctly, but the other text stuff was messed up.

However, the Crystal report got the named groups set correctly and it all looks pretty good now.

Thanks.

CU


Tony Scarpelli
Clinical Engineering Dept.
Maine Medical Center
Portland, Maine 04102