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:
Printing
Parameter
Selections
for Multiple or Range Values:
Starting with V7
of Crystal
Reports,
you are allowed to have a parameter that prompts you for a list of
values
(multiple) or for the low and high points of a range of values
(range).
However, one of the downsides of this feature, is that you can no
longer
simply place the parameter on the report to print the user's
selections.
With 'multiple' you will only see the first selected
value.
With
'range' you won't see either value. Below are
formulas that
allow
you to capture these values and print them on the report.
Range
Values, v7 - v10:
This simple
technique works
in
either version. The three examples show how to deal
with
ranges
using date, numeric and character data types,
respectively.
Look
in the Crystal help for help using ToText() to control numeric or date
formats
.
"From:
" & Minimum ( {?Date Range} )
&
" To:
" & Maximum
( {?Date Range}
)
"From:
" & ToText (
Minimum
( {?Number Range} ) , 0 , "" ) &
" To:
" &
ToText ( Maximum ( {?Number Range} ) , 0 , "" )
"From:
" & Minimum
(
{?String Range} ) &
" To:
" &
Maximum ( {?String Range} )
A more sophisticated example for DATE parameters takes into
account the options
for
"no lower/upper bound":
if
Minimum (
{?Date
Range} ) = Date (0,0,0)
then "All Dates through " & Maximum ( {?Date Range}
)
else
if Maximum ( {?Date Range} )= Date (0,0,0)
then "From " & Minimum ( {?Date Range} )
& " Forward"
else
"From: " & Minimum ( {?Date Range} )
&
" To: "
& Maximum ( {?Date Range} )
Unfortunately,
there is no
technique
that will take into account when the user takes off
the 'include value'
checkmark.
Multiple
String
Parameter,
v7:
In version 7 you
don't have
the
"Join" function, which we can use in V8. So we have
to see
how
long the list is and concatenate each member of the list as shown
below.
This formula assumes that the field is a character field. You
can
add
as many iterations as you feel you will need.
WhilePrintingRecords;
StringVar
Array Input :=
{?AnyStringPrompt};
// replace this with your string prompt
NumberVar
howmany:= count
(Input);
Input [1] +
if
howmany > 1 then ',
'
+ Input [2] +
if
howmany > 2 then ',
'
+ Input [3] +
if
howmany > 3 then ',
'
+ Input [4] +
if
howmany > 4 then ',
'
+ Input [5] +
if
howmany > 5 then ',
'
+ Input [6] +
if
howmany > 6 then ',
'
+ Input [7] +
if
howmany > 7 then ',
'
+ Input [8] +
if
howmany > 8 then ',
'
+ Input [9]
Multiple
Number
Parameter,
v7:
This
formula assumes
that
the field is a numeric field. You can add as many
iterations
as you feel you will need.
WhilePrintingRecords;
NumberVar
Array Input :=
{?Customer
List};
NumberVar
howmany:= count
(Input);
ToText(Input
[1],0,'')
+
if howmany > 1 then ', ' + ToText (Input [2],0,'') +
if howmany > 2 then ', ' + ToText (Input [3],0,'') +
if howmany > 3 then ', ' + ToText (Input [4],0,'') +
if howmany > 4 then ', ' + ToText (Input [5],0,'')
Multiple
String
Parameter, v8 - v10:
Two new
functions in Version
8
allow you to create these same capabilities more
simply.
For
character prompts that have a "multiple" setting you can use the "Join"
function,
which automatically counts and concatenates, and even allows you to
specify
the delimiter.
Join (
{?YourCharPrompt}
, ', ' )
Multiple
Numeric
Parameter,
v8 - v10:
Join only works
with
character
fields. While you could use the V7 technique described
above,
you have to put in a fixed number of 'if-then'
loops. To
account
for long lists, you would have to write a long
formula. In
V8
we have the ability to do 'looping' which makes this formula shorter
and
(to some people) simpler. Of course if you aren't
used to
the
looping syntax in Crystal, it may be more of a
bother.
Anyway,
here is an example of looping.
- WhilePrintingRecords;
- Local
NumberVar Array
InputNum
:= {?AnyNumericPrompt};
- //The
last field above
is
the one you replace with your prompt field
- Local
StringVar
str := "";
- Local
NumberVar strLen
:=
Count (InputNum);
- Local
NumberVar i;
-
For i := 1 to strLen
-
Step + 1
-
Do (str := str +', '+ ToText(InputNum [i],0,''));
- str [3 to
Length(str)]
Multiple
Range Parameters:
The following
two formulas
were
contributed by users. They both allow you to display the
values
selected
in a "multiple range" parameter, even when the parameter allows a mix
of
ranges and discrete valeus. The first can be used with
parameters
of
any data type. It displys discrete values as single values.
The
second example only works with string parametrs. It displays
any
discrete
values as a
range of
one
value, like "Apple
to
Apple".
In both examples, you need to replace the {?test} parameter with
your
parameter
name.
Contributed by Steve G:
- BeforeReadingRecords;
stringvar PrintYourPrompt;
numbervar i;
PrintYourPrompt := "";
for i := 1 to count( {?test} )
step 1
do
(PrintYourPrompt := PrintYourPrompt +
iif(i = 1,'',', ') +
iif(minimum( {?test} [i] ) = maximum( {?test} [i]
) ,
totext( minimum( {?test}
[i])),
totext ( minimum ( {?test} [i]
) ) +
' - ' + totext ( maximum( {?test} [i] ))));
if Length ( PrintYourPrompt ) > 0
then
PrintYourPrompt [ 1 to Length ( PrintYourPrompt )
]
Contributed
by Robert
Taylor:
- WhileReadingRecords;
NumberVar NumRanges := ubound( {?test} );
NumberVar i := 0;
StringVar outStr := "";
for i := 1 to NumRanges
step 1
do
(
StringVar maxCN :=
Maximum( {?test} [i]
);
StringVar minCN :=
Minimum( {?test} [i]
);
outStr := outStr
& iif ( outStr
<>
"" , "; " , "" ) &
iif( len (minCN) > 0, minCN, 'The
Beginning ')
&
' to ' &
iif( len (maxCN) > 0, maxCN, '
The End' );
);
outStr;