Want to sort by product, but not in Alpha order

[modglin]

I would like to sort by cigarette products, but do not want the groups to come back in Alpha order. Currently I have a formula:
if {PROD_BY_UPC.PRICE_GROUP} in ["000000001" to "000000004"]
or {VICSRECONS_SALES_DETAIL.CODE_OR_UPC} in ["30","31","5711"] then "3-Marl" else
if {PROD_BY_UPC.PRICE_GROUP} in ["000000005" to "000000006"]
or {VICSRECONS_SALES_DETAIL.CODE_OR_UPC} in ["28","29"]then "2-Basic" else
if {PROD_BY_UPC.PRICE_GROUP} in ["000000007" to "000000008"]
or {VICSRECONS_SALES_DETAIL.CODE_OR_UPC} in ["26","27"]then "6-Doral" else
if {PROD_BY_UPC.PRICE_GROUP} in ["000000009" to "000000012"]
or {VICSRECONS_SALES_DETAIL.CODE_OR_UPC} in ["36","37","5714"]then "4-Virg" else
if {PROD_BY_UPC.PRICE_GROUP} in ["000000013" to "000000016"]
or {VICSRECONS_SALES_DETAIL.CODE_OR_UPC} in ["40","41","5717"]then "9-Salem" else
if {PROD_BY_UPC.PRICE_GROUP} in ["000000017" to "000000020"]
or {VICSRECONS_SALES_DETAIL.CODE_OR_UPC} in ["38","39","5715"]then "5-Parl" else
if {PROD_BY_UPC.PRICE_GROUP} in ["000000021" to "000000024"]
or {VICSRECONS_SALES_DETAIL.CODE_OR_UPC} in ["32","33","5712"]then "8-Camel" else
if {PROD_BY_UPC.PRICE_GROUP} in ["000000025" to "000000028"]
or {VICSRECONS_SALES_DETAIL.CODE_OR_UPC} in ["34","35","5713"]then "7-Winst" else
if {PROD_BY_UPC.PRICE_GROUP} in ["000000029" to "000000032"]
or {VICSRECONS_SALES_DETAIL.CODE_OR_UPC} in ["24","25","5710"]then "1-Kool" else
if {PROD_BY_UPC.PRICE_GROUP} in ["000000034" to "000000035"] then "10-Monar" else
if {PROD_BY_UPC.PRICE_GROUP} in ["000000036" to "000000039"]
or {VICSRECONS_SALES_DETAIL.CODE_OR_UPC} in ["22","23"]then "11-Pall" else
if {PROD_BY_UPC.PRICE_GROUP} in ["000000040" to "000000043"] then "12-Newp" else "13-"

This was so it would give Kool, then Basic, then Marlboro ane etc. The end user indicated that she would like the option to choose the order. Sometimes she wants Kool,Basic,Marlboro and etc sometimes she would rather have Winston, Kool, Basic and etc. Is there a way to set up a parameter that she would choose which is 1st, 2nd, 3rd, 4th, 5th and etc?

Thanks!

 

[mbarron]

The following will work in V8 and up:
Create a parameter that allows multiple discrete values. Set the default values to the output you have above (without the numbers)


Create the following formula: -{@prod} is the formula that you create to determine the brands

==================================================
stringvar array order:={?your.sort.parameter};
local numbervar looper;
local stringvar posit;
local stringvar output;


For looper:= 1 to ubound(order) do (
if order[looper]= {@prod} then
posit:=totext(looper,00));

output:=posit &"-"&{@prod}
===============================================

Then sort on the field ( the field doesn't need to be on your report canvas)



Mike

 

[modglin]

Mike - first thanks for such a quick response!
I apparently missed something.
The above formula is named {@Price Grouping}. I did change it so that it does not have the numbers.
I have a parameter named {?Price Groups} that has each of the Cig Brands.
I named the formula you suggested {@Group}.
it currently reads -
===============
stringvar array order:={?Price Groups};
local numbervar looper;
local stringvar posit;
local stringvar output;

for looper:=1 to ubound(order) do (
if order[looper]={@Price Grouping} then
posit:=totext(looper,00));

output:=posit & "-" & {@Price Grouping}
=====================

When I save the formula it indicates that a string array is required on the fist line. The parameter is a String.

The report is grouped by store, price grouping, upc. Price Group is currently used as a parameter so that she can pick one or more of the groups to include in the report.

Sorry, like I said, apparently I missed something.

 

[mbarron]

Make sure the parameter is set to be allow multiple discrete vaules. Range values will crash the formula.

Mike