How to choose one of two parameters

[lana123]

Hi!
I'm working with Crystal Report and Oracle database now and I have two parameters on each report. By business rules the users can choose only one of the parameters(another must be disabled).
I'll appreciate if anybody know how to do it.
Thanks in advance.

 

[synapsevampire]

It sounds like you want hierarchial/conditional parameters, which Crystal doesn't support.

Create just 1 parameter and then in a formula resolve their choice into the proper record selection formula.

If this doesn't resolve, please post specifics about the parameters and the choices to be made.

-k

 

[lana123]

To synapsevampire:
Thank you very much for your reply!
Perhaps, I didn't explain well: I need open each record in the report using only ONE of two parameters:ID or NAME (using one exclude using another).
When the users open the report (from Crystal) they would have only one possibility:to send ID and open the report or to send NAME.
Thanks in advance.

 

[Turkbear]

Hi, Make 2 reports, one for each user, that uses one parameter. You cannot restrict users the way you want to, besides, what will the report do for the unspecified parameter?

As suggested,a single parameter can be part of a If..Then..Else structure and,if you can identify the user, you can use that to set either the NAME or the ID

 

[rhinok]

I've done this many times within Crystal, it just takes some creative thinking![ol][li]Create a parameter called {?Parameter Type} that has default values of 'ID' and 'Name'. Only allow a single selection and don't allow modification of the value.[/li][li]Create a parameter called {?Parameter Value}. Leave the list of values blank.[/li][li]Modify your Record Selection Criteria to allow for both parameters:

{table.id} = Switch({?Parameter Type} = 'ID',{?Parameter Value})

Or

{table.name} = Switch({?Parameter Type} = 'Name',{?Parameter Value})

The Record Selection Criteria listed above will return SQL (Database|Show SQL Query) as follows when selecting a Type of 'Name' and a value of 'K, Rhino':

WHERE
(table."ID" = '' OR
table."Name" = 'K, Rhino')

This shouldn't be a problem unless you have low values (not Nulls) stored in the ID or Name fields.

An alternative Record Selection Criteria is:

(
  If {?Parameter Type} = 'ID'
  Then {table.ID} = {?Parameter Value}
  Else If {?Parameter Type} <> 'ID'
  Then False
)

Or

(
  If {?Parameter Type} = 'Name'
  Then {table.Name} = {?Parameter Value}
  Else If {?Parameter Type} <> 'Name'
  Then False
)

The above statement returns SQL (Database|Show SQL Query) as follows when selecting a Type of 'Name' and a value of 'K, Rhino':

WHERE
(table."ID" = 'K, Rhinok' OR
table."Name" = 'K, Rhinok')

Since you shouldn't have IDs and Names with the same value, this shouldn't cause any adverse affects and it accounts for low values.[/li][/ol]

Hope this helps, have fun!

 

[rhinok]

I figured I put enough detail in the last post to make your head swim, but here's another little tidbit:

In order for the above method to work, the datatypes for the two values must be the same. Name is obviously a String datatype, but ID might be numeric. If ID is not a String, then you will need to convert it before you can use this method. You might be able to do it within the formula, or you can create a SQL Expression. Let us know if the situation arises and you need assistance.