We help IT Professionals succeed at work.

Order Alphanumeric grouped records numerically

DataManipulator
on
Medium Priority
287 Views
Last Modified: 2012-08-13
I have a report (CR 8.0.1.0) printing out details of multiple people living at street addresses.  Group 2 is the street.  Group 3 is the address formula consists of four data fields:  number; letter suffix; half; unit.  i.e. 97A Main St or 125 Washington Ave Unit 17.  I need the order of Group 3 to have numeric priority (1, 5, 14-A, 33, 275, 4200) rather than alphanumeric (1, 14-A, 275, 33, 4200, 5).

I have tried ordering by fields using the ORDER clause in Show SQL Query.  This seems to do nothing.  I have tried the various options in Change Group Options.  These mostly deal with the formula as alphanumeric.  Record Sort Order will not allow me to remove or reorder groups.

How can I order these street addresses in numeric (street) order?

Thanks,
DM
Comment
Watch Question

Commented:
What I have done several times for that is to create a separate "numeric" field in the table containing just the numeric portion of the address.  Then use it in the "Order By" clause and in the grouping.  That will do the trick and your report will present the data correctly.

Commented:
What I have done several times for that is to create a separate "numeric" field in the table containing just the numeric portion of the address.  Then use it in the "Order By" clause and in the grouping.  That will do the trick and your report will present the data correctly.

Author

Commented:
EYoung, There already is a seperate numeric field in the raw data.  That is part of the formula used in Group 3 and what seems to be taking precident.  The first thing I tried was redoing the ORDER BY, but nothing changes.
Commented:
Is the numeric field sorted after the three groups are sorted?  Each group automatically causes the report to be sorted based on its value.  Additionally, groups are sorted before any fields you add to the sort order.

All that is to say that maybe you will need to make the numeric field a group level or if it is already a group, then raise it up to Group #2 or #1.

Author

Commented:
EYoung, the numeric field is the first part of the formula used by Group 3.  If there is no better answer, I will have to move Group 3 to 4 and have the new Group 3 be just the numeric value.  That is of course if an integer data field is sorted numerically rather alpha-numerically.  DM

Author

Commented:
EYoung, the numeric field is the first part of the formula used by Group 3.  If there is no better answer, I will have to move Group 3 to 4 and have the new Group 3 be just the numeric value.  That is of course if an integer data field is sorted numerically rather alpha-numerically.  DM

Commented:
Yes, you might try that.  I am leaving for the evening.  Will check back with you tomorrow.

Author

Commented:
EYoung, the group ordering taking precedence was the key.  I did add another group and resorted the group order so that the numeric came alpha.

One note, I had to create a formula containing:
IIF ({person.half}='5',{person.number}+.5 ,{person.number} )

Using this rather than just the number field makes sure a 1/2 address comes after a whole. i.e. 14, 14 1/2

Author

Commented:
Thanks for pointing me in the right direction.

DM

Commented:
You're welcome.  Sorry to be getting back to you so late in the day.  (Gulp - I totally forgot.)

Explore More ContentExplore courses, solutions, and other research materials related to this topic.