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: " +
ToText
( Minimum ( {?Date Range} ) ) +
" To: "
+
ToText ( Maximum ( {?Date Range}
) )
"From: " + ToText (
Minimum
( {?Number Range} ) , 0 , "" ) +
" To: "
+
ToText ( Maximum ( {?Number Range} ) , 0 , "" )
"From: " + Minimum
(
{?String Range} ) +
" To: "
+
Maximum ( {?String Range} )
For a more sophisticated example that takes into account the options
for
"no lower/upper bound" you can use:
if Minimum (
{?Date
Range} ) = Date (0,0,0)
then "All Dates through " + ToText ( Maximum ( {?Date Range} ) )
else
if Maximum ( {?Date Range} )= Date (0,0,0)
then "From " + ToText ( Minimum ( {?Date Range} ) ) + " Forward"
else
"From: " + ToText ( Minimum ( {?Date Range} ) ) +
" To: " + ToText ( Maximum ( {?Date Range} ) )
Unfortunately, there is no
technique
that will take into account a user who 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;