Example: Passing Parameters from Crystal

Following is an example of how to create a query in OLE DB Object services that passes a Crystal parameter at runtime.

Note: If you have used the ‘?paramName' method of specifying parameters, the actual parameter names, not Param1, Param2 and so on, will appear in Crystal.

In order for this solution to work, you must have the latest weekly hot fixes for Crystal Reports 9, or a higher release of Crystal Reports.

On the OLE DB Object Services side:

  1. Select fields and other options for a database query. (If you need detailed instructions on how to create a Database query, see Creating Database Queries.)

  2. Create a filter or index criteria in the OLE DB Query Builder using the "?" (question mark).

    Note: You cannot use date fields and some specialized conditional fields will not work.

    The following screen shot shows how you would specify an index criteria using an index called EMPSET1 for the COMPANY and EMPLOYEE fields. To get to this dialog, click Indexes on the main Database query form.

    Creating an index criteria for a database query

  3. When you are finished making specifications, you can exit OLE DB Object Services and return to Crystal Reports.

On the Crystal side:

  1. You will be prompted as the Crystal parameter field, Param1, is created. Do not rename this parameter; this parameter will be recreated each time you exit from Query Builder into Crystal Reports.

  2. Uncheck "Set parameter to null."

  3. Refresh the report and choose "Prompt for new parameter values."

  4. The parameter created by OLE DB Object Services will be a string parameter, so a Crystal string function (like ToNumber) might be needed to convert the string to a number if used in a selection criteria or other formula.

    More than one parameter can be entered into an Index criteria or Filter, which will result in Param2, Param3, and so on, in Crystal. This makes a range possible in an index criteria, as in "?->?" creating Param1 and Param2 where Param1 is the starting string (00050) and Param2 is the ending string (1000).