Sorting multiple fields using formula

[srpeterson]

I am currently using Crystal Reports version 8.5 with an OLEDB connection to a SQL2000 database. The report I am building has a parameter option that will allow the user to pick which software product they would like to report on. Based on the parameter the corresponding customer information will display on the report of customers who currently have licensed that module of software. The problem that has arisen is that depending on the software chosen I need to have the sort order change.

If {?Product Identifier} = "VA/PO" then sort order needs to be Core Software Name, City, State.

If {?Product Identifier} = "VALMI" then sort order needs to be Product Vendor.

I created a formula named Sort that has the following code. I just wanted to test this out and if it worked I plan to add onto the If-Then statement for all of my other parameter options.


if {PRODUCT.ACTUALID} in ["VA/PO","BVA/PO"] then
({C_ACCOUNT.CORE_SFW_NAME};
{ADDRESS.CITY};
{ADDRESS.STATE}
)
else
{ACCOUNT.ACCOUNT};

When VA/PO is selected as my parameter with the Sort formula field chosen in the Report>Sort Records dialog box I end up getting the following results returned. I have also eliminated a couple of the fields in the formula to see if I cannot just get it to sort on, for instance City,State and I end up getting the same results returned.

CoreSoftwareName City State
Vision Ash Flat AR
Vision Clarendon AR
Vision Parkdale AR
Vision Rogers AR
Vision Jonesboro AR
Vision Paragould AR
BAIS Anamosa IA


It appears as though State is the only field that it is sorting by. Is it possible to accomplish what I am attempting in Crystal? If anyone can give me some insight as to what I need to do in order to sort multiple columns using a formula field that would be great. I hope I have included enough information for you.

 

[lupins46]

If you want to sort on up to 3 fields you must have 3 dummy 'sort' fields into which you feed values.

You specify the sort sequence as ,say:
@Sort1 then @sort2 then @sort3

Now you need 3 formulas to derive these values.
@sort1:-
if {PRODUCT.ACTUALID} in ["VA/PO","BVA/PO"] then
{C_ACCOUNT.CORE_SFW_NAME};
else
{ACCOUNT.ACCOUNT};

@sort2:-
if {PRODUCT.ACTUALID} in ["VA/PO","BVA/PO"] then
{ADDRESS.CITY};
else
"A";

:sort3:-
if {PRODUCT.ACTUALID} in ["VA/PO","BVA/PO"] then
{ADDRESS.STATE}
else
"A"

 

[TheBlondOne]

What I think your going to have to do is to create 3 seperate formula which will form the basis of your sort order, as your limited with the sort order it might be easier to use groups.

The first formula should look something like this

If {?Product Identifier} = "VA/PO" then {Core Software Name} else
if {?Product Identifier} = "VALMI" then {Product Vendor}

The second formula

If {?Product Identifier} = "VA/PO" then {City} else ""

The third formula

If {?Product Identifier} = "VA/PO" then {State} else ""

If you then create a group for each of the formula's this should get round the problem for you.

You may need to suppress the group header/footers for group 2 + 3 if the parameter option selected is VALMI.

HTH.