Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Sort field from field of mixed data type

Status
Not open for further replies.

mg02

Technical User
Mar 10, 2008
3
US
I have read almost every forum I could find trying to find an answer to my problem. I am using a group that is determinded by a parameter. However, the field I'm using for the group by has two different data types. About 9 of the options in the "IF" statement use the data type String while the last two are Numbers. It took me ab while, but I finally figured out that everything in the IF, THEN, ELSE statement had to be of the same data types. I have sucessfully figured out how to do the group on the Numbers by converting them to strings and then formating them to sort correctly.

i.e.

else if {?IN_GROUP_BY} = "$/MMB" then ToText ({@net_mmb}, "#########.##")

This works great for most of my issue. However, I do have some data that handles negative numbers. I cannot figure out if there is a way to be able to sort the number as a string and it handle the negatives correctly. Currently my values are coming back as
0
-.21
-.22
-.34
1.2 (ect.)

Does anyone know if there is a way to get this to sort correctly

-.34
-.22
-.21
0
1.2
ect.

I have been working on this for about 3 weeks now and I'm about at my wits end. Any adivce will be greatly appreciated
 
I just set up a table of strings in an Oracle database and inserted your numbers. when I sorted in a CRXI report, they sorted in this order.

-.34
-.22
-.21
0
1.2

So you have a formula that creates a string field that may or may not contain numeric data. Is that correct?
 
Yes that is correct. I have a parameter that allows the user to enter what they want the report to be sorted by. Then I have a group that sorts on a formula field called 'group_2'. This field then does an IF, ELSE, THEN statement to determine which field to do the group on. Here is the code for the field

if {?IN_GROUP_BY} = "PRODUCTION TYPE" then {PROD_TYPE}
else if {?IN_GROUP_BY} = "SYSTEM" then {SYSTEM_CD}
else if {?IN_GROUP_BY} = "CONTRACT" then {CTR_NO}
else if {?IN_GROUP_BY} = "CONTRACT TYPE" then {CTR_TYPE}
else if {?IN_GROUP_BY} = "CONTRACT PARTY" then {CTR_PTY_NM}
else if {?IN_GROUP_BY} = "OPERATOR ID" then {OPERATR_ID}
else if {?IN_GROUP_BY} = "OPERATOR NAME" then {OPERATR_NM}
else if {?IN_GROUP_BY} = "SUB PLANT" then {SUB_PLANT}
else if {?IN_GROUP_BY} = "SUB_CTR_TYPE_CD" then {SUB_CTR_TYPE_CD}
else if {?IN_GROUP_BY} = "$/MMB" then ToText ({@net_mmb}, "#########.##")
else if {?IN_GROUP_BY} = "NET MARGIN" then ToText ({@prodnetval},"#########.##".

Also I should mention I'm working in CR8.5
 
You could handle this by inserting groups on two formulas as in:
//{@grp1}:
if {?IN_GROUP_BY} = "PRODUCTION TYPE" then {PROD_TYPE}
else if {?IN_GROUP_BY} = "SYSTEM" then {SYSTEM_CD}
else if {?IN_GROUP_BY} = "CONTRACT" then {CTR_NO}
else if {?IN_GROUP_BY} = "CONTRACT TYPE" then {CTR_TYPE}
else if {?IN_GROUP_BY} = "CONTRACT PARTY" then {CTR_PTY_NM}
else if {?IN_GROUP_BY} = "OPERATOR ID" then {OPERATR_ID}
else if {?IN_GROUP_BY} = "OPERATOR NAME" then {OPERATR_NM}
else if {?IN_GROUP_BY} = "SUB PLANT" then {SUB_PLANT}
else if {?IN_GROUP_BY} = "SUB_CTR_TYPE_CD" then {SUB_CTR_TYPE_CD}

//{@grp2}:
if {?IN_GROUP_BY} = "$/MMB" then {@net_mmb}
else if {?IN_GROUP_BY} = "NET MARGIN" then {@prodnetval}

If the parameter doesn't match, the group will have no effect. Be sure to format group headers and footers for both groups to "suppess blank section". In the case of {@grp2}, also use a suppression formula on the group header and footer sections of:

{@grp2} = 0

-LB
 
Thank you so much!!! That's perfect. B-)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top