How can I use a Multivalue or Date Range Parameter with a Command

This FAQ describes a technique in Crystal that gets around a major limitation with Commands.

The limitation is that CR Commands do not have a mechanism to allow for the use of multivalue or date range parameters. You cannot simply put in the command sql "table.fieldname IN ({?Parameter})" because the parameter is technically an array in crystal that is not comma delimited in a parsable string by a database server.

One solution is to present the user with say 10 parameters (one for each item) that have the same 10 items that can be selected. Then you insert each parameter into the SQL of the command with 10 OR commands for each parameters and perhaps an "ALL" or "None" option as well. This can get messy though and will not support dynamic multi-value prompts. There is also a method by presenting the user with a single value parameter and them manually input a comma delimited string û but then you lose CRÆs ability to present a List of Values for the user to select and you have to trust the user to enter the string properly or risk a scary error.

This technique will "inject" a multi-value or date range parameter directly into the SQL for a true IN statement which opened up a several interesting possibilities. I would call the technique "SQL Injection" because that is in effect what it is doing (it sort of mimicks crystals sql generation ability).

Here is the technique as I was able to get to work.

1. Create a master report that has no connections.
2. Create a multi-value parameter in the master report as normal (ie: db_Products)
3. Create a formula in the master report (ie: par_Products)

This "Formula Parameter" will be responsible to parse the "db_Products" parameter into a sql string that your server can understand. The bottom of this FAQ has some examples of the formulas.

4. Create your report in a separate .rpt file with your SQL Command.
5. Insert Command parameters with the same name as your formula in the master report (ie: par_Products) for ease of understanding only.
6. Insert the report in the master report as a sub report.
7. Create a subreport link on the "Formula Parameter" in the master report to the Command Parameter (ie: par_Products -> par_Products)

8. Refresh the report from the master.

The master report will prompt the user using the multi-value or date-range parameter giving the user access to Crystal full capabilities with presentable parameters.

The formula will "massage" the answer into a format suitable for your server

And pass it to the subreport parameter to be inserted into the Command sql.

For the date-range formula example, you can literally place the "BETWEEN" statement in the formula. So in the command SQL you would just put the databasefield and the parameter side by side.
AND PROPOSAL.RATE_DATE {?par_RateDateRange}
The date-range formula could therefore be cleaned up a little to use a ">=" or "<=" operators to dynamically handle the "No Upper Bound" selection instead of a very wide between statement as I implemented.

That's the whole thing!. The major disadvantage is that you lose the ability to put subreports in your report (since subs cannot have subs). But if subreports are not a factor, this produces clean sql to drive your reports. With peer review I am sure the technique could be improved and probably have other uses.


Formula Examples:

If the parameter is a string, the formula would look like this:
If the parameter is a number, the formula would look like this:
If the parameter is a date range, the formula would look like this: