Crystal
Reports Training by Ken Hamady, MS, Reporting and Training Nationwide
Free
Crystal Reports formula examples from
KenHamady.com
To
learn
the techniques used in these formulas get:
Prompt for a sort
field from fields of mixed data types:
It is fairly
easy to use a prompt to select a sort or group field, if all
of the fields you are choosing from have the same data
type.
You simply create a parameter prompt called {?Sort Choice} with three
default
choices (Cust, State, City) and set the parameter so that they must
pick
one of these three. Then write a formula field called
{@Sort
Field } that selects the appropriate field based on the user's
choice.
You use {@Sort Field } as your sort or group field.
if {?Sort
Choice} = 'Cust' then {Customer.Customer Name} else
if {?Sort Choice} = 'State' then {Customer.Region} else
{Customer.City}
If, however, the
three fields don't all have the same data type you
will need to convert numeric and date values into strings using the
ToText()
function. This is because the Then and Else values in a formula
must
have the same data type. Then, to assure that the values sort
correctly
once converted, you have to use format strings within the ToText()
function.
A numeric needs to have spaces or zeros on the left to keep numbers of
varying lengths sorted in the proper order. A date will need to
start
with a 4 digit year to sort correctly. The following formula
shows
both format strings. It chooses between a character, a date or a
numeric as the sort, based on {?Sort Choice}. In the third
line, the numeric format string should have as many pound signs (#) as
there are digits in the highest value for that field.
if {?Sort Choice}
= 'Cust' then {Customer.Customer Name} else
if {?Sort Choice} = 'Date' then ToText({Orders.Order
Date},"yyyy-MM-dd")
else
if {?Sort Choice} = 'ID' then ToText({Customer.Customer
ID},"#######")
else
{Customer.Customer Name}