phone: (540)338-0194

﻿

## Crystal Reports formula examples

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, most versions (7 and later):

This simple technique works in all versions starting with version 7.   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, most versions (8 and later):

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, most versions (8 and later):

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 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 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:

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:
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;