Crystal Reports:
Loading a parameter from Visual Basic (using the OCX)

Here is how to load a parameter from Visual Basic into your Crystal Report. Do the clever stuff in Visual Basic to create a form, load a pick list, and collect the user replies. Then before you invoke the report via the OCX, you can load the parameter values across to the report.

For each parameter you need:

crObject.ParameterFields(0) = "Region;CA;true"

crObject.ParameterFields(1) = "StartDate;Date(2001,5,18);true"

Each value in the string is separated by a semi colon. The first value is the parameter name. Use a separate index (0,1,2…) for each parameter in the report. The ‘true’ value at the end of each line tells Crystal not to ask the user for that parameter. A ‘false’ value will ask the user the question, but with the value as a default.

Also note that dates must be entered as a string in the form Date(yyyy,m,d).

This works only for a single value parameter. For ranges and multiple value parameters, you will have to upgrade to the Report Designer Component (RDC).

There's also a method that might work with the OCX if you are using the parameter in record selection.

The OCX control will not pass a multi value or a range parameter into a report. You need to use the RDC for this. But if all you are doing is using the multi value parameter in record selection, you don’t need a parameter at all; you can load the record selection formula directly from Visual Basic.

Do the clever stuff in Visual Basic to create a form, load a pick list, and build the list of values. Then before you invoke the report via the OCX, you can replace the selection formula with what selection you really need:

crObject.ReplaceSelectionFormula "{Customer.Region in [‘CA’, ‘OR’, ‘PA’, ‘TX’]"

If you need to pass a date range. You can also do that with:

crObject.ReplaceSelectionFormula "{Orders.Order Date} in [Date(2001,6,21) to Date(2001,6,25)]"

Using the OCX. This requires one line of code. To load the Region parameter with a value of CA, use this in your program:

CrObject.ParameterFields(0) = "Region;CA;True"

The "True" indicates that the user is NOT to be asked to enter a parameter value. "False" will ask the user, with "CA" being the default response.

If your report also had a date parameter you would like to load with 12 October, you would use:

CrObject.ParameterFields(1) = "ReportDate;Date(2001,10,12);True"

Using the RDC. The OCX is fine, if you have a single value parameter. However it does not support multi value or range parameters.

If you wish to control multi value or range parameters via your code, then you will have to use the RDC instead of the OCX. The first thing you need to know is how many parameters are in your report, and what they are named.

CrReportObject.ParamaterFields.Count is the number of parameters in your report

CrReportObejct.ParameterFields (1).ParameterFieldName is the name of the first parameter. (2) is the second, etc.

The method to load a value into the parameter will operate with multiple values, so you can have several lines in your program to load multiple values.

"AddCurrentValue" loads a single value, but you can use it more than once.

CrReportObject.ParameterFields(1).AddCurrentValue "CA"

will load the string "CA" into the first parameter.

CrReportObject.ParameterFields(1).AddCurrentValue "TX"

will load a second value of "TX" into the same parameter.

We discussed using the Report Designer Component (RDC) ParameterFields collection to load a simple string parameter into a report. The AddCurrentValue and AddCurrentRange methods can also load numeric, date, date-time and Boolean parameters.

So, how do you know what data type the parameter is expecting?

The ValueType property will tell you what you need to know. Values for ValueType include crStingField (12), crDateField (10), crDateTimeField(16), and the others are listed in the help file.

When you refresh a Crystal report in the report designer, there are several options with range and multi value range parameters. You can say whether to include or ignore the lower or upper bound of the range. You can also control this in your program with the RangeInfo option in the AddCurrentRange method.

On AddCurrentRange, the final number (RangeInfo) specifies how the limits of the range are to be processed

CrRangeNotIncludeUpperLowerbound 0

CrRangeIncludeUpperBound 1

CrRangeIncldueLowerBound 2

CrRangeNoUpperBound 4

crRangeNoLowerBound 8

As these are all binary values they can be combined. For example, the command

CrReportObject.ParameterFields(1).AddCurrentRange "D", "",6

will select the range from D upwards, including D but with no upper bound. And finally, for something to make it all a lot friendlier:

CrReportObject.ParameterFields(1).Prompt displays the prompt for that parameter, so you can see what the report designer suggested to tell the user about the parameter.

As you can see, this is a lot of work. Be sure you document this when you're done. To save time and money in the documentation process, use Report Analyzer.

This is a tool you should have, anyhow, as it will show you all kinds of things about your reports. Report Analyzer automatically detects potential performance issues in your Crystal Report design settings--including record selection formulas, grouping options, and database options. Using Report Analyzer, you can end the frustration, the "surprise" overtime, and the fruitless troubleshooting. In addition to saving time in your work, you can improve infrastructure utilization by ferreting out waste and optimizing resources.

This article is copyrighted by Crystalkeen, Mindconnection, and Chelsea Technologies Ltd. It may be freely copied and distributed as long as the original copyright is displayed and no modifications are made to this material. Extracts are permitted. The names Crystal Reports and Seagate Info are trademarks owned by Business Objects.