Parameters 2

[novone]

Can I cause the parameter to return all values if no entry is palced in the parameter request dialog?

 

[Storyteller]

Hello,<br>
<br>
I have read that you will have to allow for a wildcard at the parameter prompt.<br>
<br>
Be sure to use the &quot;Like&quot; or &quot;LooksLike&quot; operators in the Select Wizard, when placing the parameter.<br>
<br>
To allow for all values, be sure to have the wildcard &quot;*&quot; as the default value.<br>
<br>
Hope this works for you.<br>
<br>
MO

 

[MalcolmW]

Another way of doing this with SQL data sources is as follows.<br>
Assume your parameter is {?EmployeeID}. Put the value of &quot;All&quot; as the default value. In your WHERE clause in the SQL statement, put the line <br>
WHERE ({?EmployeeID} = &quot;All&quot; OR {?EmployeeID} = Table.EmployeeID)<br>
This will return all employee IDs when the default value &quot;All&quot; is used, or if a specific EmployeeID is entered, it will return just that. Of course, this assumes that there does not exist an employee with a valid employeeID of &quot;All&quot;!<br>
<p>Malcolm Wynden<br><a href=mailto:wynden@island.dot.net>wynden@island.dot.net</a><br><a href= > </a><br>

 

[MaintenanceGroup]

If you want to select all values when a parametet is left null, you can build a formula such as:<br><br>(if {?parameter} = &quot; &quot; then 1=1 else (workorder.value) = {?parameter})

 

[HoustonGuy]

MaintenanceGroup has it - here's my two cents...<br><br>if {?Parameter} = &quot; &quot; then true<br>else<br>{MyDatabase.Myfield} = {?Parameter}<br><br>For a more user friendly version I often use the word &quot;All&quot; as the default value and make the user enter something different. The code would then be:<br><br>if {?Parameter} = &quot;All&quot; then true<br>else<br>{MyDatabase.Myfield} = {?Parameter}<br><br> <br><br> <p>Cody ford<br><a href=mailto:codyford@yahoo.com>codyford@yahoo.com</a><br><a href= > </a><br>VB, VBS, Seagate Info/Crystal Reports<br>

 

[Dond12]

I use a very simple-minded technique for this, but it seems to work just fine.

In my parameter pick list, I include as the default value something like &quot;All Cases&quot;

Then, in the Select Expert, I use the following formula:

if({PARAMETER} <> &quot;All Cases&quot then
{field} = {PARAMETER}
else
{field} = {field}

The logic is basically this: if the parameter field contains a discrete value, then USE that value to match against a field in the database; otherwise just ACCEPT whatever value happens to be in that field in the database.

 

[icytrue]

The LIKE &quot;*&quot; version is the only method of those listed above that gets converted into SQL to be passed to the database. However using LIKEis not very efficient when the parameter has an actual value in it (ie not *). Has anybody worked out a solution to this problem that does result in the SQL being written, but does not use LIKE? Icytrue

icytrue@hotmail.com

 

[MalcolmW]

Using the Crystal Query Designer with parameters allows you create more efficient SQL, at the expense of having to use a less than robust product. Passing parameters to stored procedures is a more reliable way of achieving this same goal. Malcolm Wynden
malcolm@wynden.net