SQL Commands and Parameters

[alc25]

I am using Crystal Reports XI. I have written an SQL Command and I am trying to create a multi value parameter in the command and a date range parameter. It is not allowing me to do it. Is there any way I can do this?

 

[MisterMo]

With CRX

Add query, create Param, position cursor where you want the param to be in your query, double click on param and hey presto.

you can create as many as you want but only he one that are actually added to the command will be added to the Report

the others will simply disappear

Hope it helps



Mo

 

[chelseatech]

The problem is that the SQL command is pure SQL so that doesn't support multivalue parameters.

But you can still use a parameter (ncluding multivalue and range parameters) in your record selection outside the SQL command.

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[davidwendelken]

In SQL, the IN operator supports multiple values.

Example:

SELECT city_name FROM city_list
WHERE state_cd IN ('GA','NC','SC')

I did a test with the report wizard in v10, and chose the "is one of" option, which seemed like it might be a logical equivalent. However, it built a (state_cd = 'GA' or state_cd = 'NC' or state_cd = 'SC') where clause phrase instead of using an IN operator. (Not an exact equivalent, as the IN operator doesn't handle NULL well in my experience.)

I tried creating a sql statement directly using the Add Command feature of the data expert.

SELECT city_name FROM city_list
WHERE state_cd IN ({?PI_STATE_LIST})

The idea was to see if a multi-valued list showed up as a comma-delimited list that could replace the parameter placeholder. If so, it might get the results I wanted. Single quote marks surrounding the items in the list might prove problematic, but the technique might work for numbers.

No such luck, the multi-value option wasn't available for a parameter created via the add command.

Looks like you will need to filter the records out in the report instead of the original query, unless there is an ability to dynamically create a SQL statement at runtime.
Just learning CR, I haven't got that far in the manuals yet.



David Wendelken

http://www.casetech.net