Wildcard group sort 1

[elsenorjose]

Hello all,

I have to group records based on a specific string in a name field and then sort within the grouping based on the records. The string is 'TBD' so in my select formula, I have the following:

{SO_Master.NAME_27} startswith 'TBD'

Now, within the group, I need to sort in the following order:

TBD - Ship To (some other text)
TBD - Credit Hold
TBD - Prepay
TBD - Flex Commit

I'm stuck on the first sort, TBD - Ship To (some other text). The value can be anything like TBD - Ship To Waiting Address, or TBD - Ship To Waiting Buyer Name, etc. Is there a way for me to use the specified order option in the 'Change Group Options' box to accommodate the various possible values? The problem is that as I am writing the report, the data retrieved may not capture all possible permutations of the TBD - Ship To value since there may be more records added in the future that don't appear now. Or is there a formula I could write to capture this sorting logic?

Using CR XI R2 on a SQL Server 2005 backend.

Thank you.

 

[lbass]

You could create a formula like this:

if {SO_Master.NAME_27} startswith 'TBD - Ship To' then
1 else
if {SO_Master.NAME_27} startswith 'TBD - Credit Hold' then
2 else
if {SO_Master.NAME_27} startswith 'TBD - Prepay' then
3 else
if {SO_Master.NAME_27} startswith 'TBD - Flex Commit' then
4 else
5

Add this as your first sort field.

-LB

 

[elsenorjose]

Thank You.