View Full Version : How can I force the sorting of a report?


Rich Hartnett
03-30-2008, 05:30 AM
I have a sub-report within my main report that has a field called "Person_Code." The sub-report is filtered to only show "Person_Codes" that begin with V, R, T, or W. (Ex. V-1, V-2, R-1, T-1, etc.)

This particular report requires that Person Codes follow the "hierarchy rule" which requires that "V" must always be the first person type listed on the report, then "R," then "T," and finally "W." (If those other person codes exist in a particular report. Not all reports will contain all of those codes, except for the "V" code.)

Ascending or descending order won't work, because neither direction will keep them the correct order. (V,R,T,W)

How can I force the order sort order to require the "V-" codes to come first, the "R-" codes, etc, etc?

Alan Lee
03-30-2008, 08:12 AM
Could you add a field to the record called sort_ord and then assign it a value that would sort logically?


Tim Kiebert
03-30-2008, 08:58 AM

Instead of simply ordering on 'Person_Code' use an expression that results in a value that will sort as you want.



03-30-2008, 12:57 PM
Rich (and Tim)

Or generically, and I believe simpler, particularly when requirements change;

IF(left(Person_Code,1) $ "VRTW",chrtran(left(Person_Code,1),"VRTW","ABCD"),"~")+Person_Code

The tilde "~" places all person_codes not handled at the end of the list

Rich Hartnett
03-30-2008, 06:07 PM
Ira that seemed to work just fine.

I didn't a chance to try the other two solutions yet, but thanks everyone for the contributions.

Tim Kiebert
03-30-2008, 06:23 PM
Thanks Ira.


If Ira's worked then mine probably does as well. Ira's is more compact. Just stay with his. I will probably change my occurrences of that scenario as well as I come across them.

Rich Hartnett
03-30-2008, 06:40 PM
Thanks Tim. I don't know how I'd survive without this message board. I learn something new everyday!