Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to choose one or more parameters from the list of parametes

Status
Not open for further replies.

lana123

Programmer
Aug 20, 2003
79
US
Hi!
I'm working with Crystal which connects to Oracle database.
I have three or more parameters in my report.
I have to 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.
Plus the users must have possibility to send the value to the third parameter.
I'll appreciate any help.
Thanks in advance.
Lana.
 
This is almost verbatim from my response to another thread of yours (thread782-636597):[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.&quot;ID&quot; = 'K, Rhinok' OR
table.&quot;Name&quot; = '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]

[li]If you have any other parameters that you need in addition to the ID/Name selection, then just create the new parameter and account for it in your Record Selection Criteria:

{table.field} = {?some other parameter}

AND

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

OR

{table.name} = Switch({?Parameter Type} = 'Name',{?Parameter Value})[/li][/ol]It is important to note that in order for the ID/Name selection to work, both fields must be of the same datatype (both String in this example).
 
thanks again, but first:I cannot find such function like
&quot;Switch&quot; in Crystal Report And
second:Unfortunatelly,when I choose one parameter(ID for example),I cannot choose Name or the third parameter.
I know,I'm not an expert in Crystal,but I try my best and I cannot find the solution.
Sincerely,
Lana.
 
What version of Crystal Reports are you using? Switch is available under 'Programming Shortcuts' in the Function List in Versions 8 and above.

Here's how I understand your issue:[ol][li]You have a report with multiple parameters[/li][li]Regardless of the other parameters, you need the User to select either 'Name' or 'ID'[/li][/ol]This sample does exactly what you need.[ul][li]It lets you create a 'Parameter Type' Parameter. The user gets to decide whether the records are based on ID or Name[/li][li]It creates an Input Parameter. The user actually keys in the ID or Name, depending on what the user selected as the 'Parameter Type'[/li][li]You can add as many other parameters as you need (Dates, for example)[/li][/ul] You should be able to cut and paste from the example (just substitute your field elements) once you've created the parameters.
 
Rhinok

Could you just explain a bit further what values switch does not work with - you say &quot;low&quot;, what does that actually mean specifically?

many thanks

Icytrue

icytrue@hotmail.com

 
To Rhinok:
Thank you very much for working with me!
Here what I have to say before(sorry):
1.I'm using for now Version 7 of Crystal.
2.Yes,I'm using multiple parameters in report.
3.The user can(not 100% necessary) choose ID or Name.It's like a filter:the user can choose 3 parameters to filter his records or 5 parameters to narrow the search in the report. In the database, ID or Name are the characteristics of the same single record (or records if parameter contains list of IDs or Names. If not parameters provided the report should contain all the records but I receive this in report in where clause:AND ID=''(it's completly wrong-I want to make my selection wider).
I really appreciate your help.
Sincerely,
Lana.
 
Hi,
I'm confused:( not unusual [smile])

You say

'the user can choose 3 parameters to filter his records or 5 parameters to narrow the search in the report.'

Is it 3 or 5 ( I do not know the difference you mean between between filter and narrow - to me they are the same thing: parts of the WHERE clause of the query).

[profile]
 
To Turkbear:
Yes,you write,it's the same thing:I wanted to enforce the sentence but I confused the people.
Sorry for my English language,it's not my native.
Lana.
 
Hi, No problem; your English is probably much better than my knowledge of your language..


[profile]
 
My apologies Lana. I assumed that you were using CR8 or above with Crystal Enterprise.

Unfortunately, I don't have a copy of CR7 anymore so I can't reliably provide a more suitable solution.
 
Icytrue - When I saw low value (I may be using the term incorrectly, I remember it from days gone by), I generally mean a blank, non-null value. Have you ever run a report with criteria that specified Not IsNull values, but got 'nulls' anyway? Typically, you'd change your Record Selection Criteria to be something like {table.field} <> '', instead. This is what I mean by low values. It may be caused by the 'Change NULL values to default' option, but maybe not.

Its not that the Switch statement won't handle them. On the contrary, it does handle them and that may be a problem.

{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.&quot;ID&quot; = '' OR
table.&quot;Name&quot; = 'K, Rhino')

In the above example, what if there were records where the ID was = ''? I don't want these records returned, so this doesn't really help. This is where I would substitute the alternative Record Selection Criteria:

(
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.&quot;ID&quot; = 'K, Rhinok' OR
table.&quot;Name&quot; = 'K, Rhinok')
 
To Rhinok:
Thank you very much for responding to me.
This mail is just to let you know that we installed Crystal 9 Version and I will use your instructions for creating the report which is very important for the users.
I'll inform you how it will work.

Lana
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top