| Author |
Message |
bgovindhan
Newbie
Joined: 14 Dec 2011
Location: United States
Online Status: Offline
Posts: 14
|
  
Topic: parameters
Posted: 14 Dec 2011 at 5:01am |
Hi,
I am new to the crystal reports forum. But, here is my question. I would like to pass parameters to my SQL. I am using oracle as the database. The user is allowed to select multiple values for the given parameter. Now, I need to use parameter values to filter my records in the SQL. For exaample, I would like to give {CONTRACT_ID} IN (the parameter).
How would I do this? Any help is greatly appreciated.
Thanks. Balaji Edited by bgovindhan - 14 Dec 2011 at 5:03am
|
 IP Logged |
|
|
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 14 Dec 2011 at 8:41am |
I don't believe that you can send an array values (that is how CR treats multiselects) to a stored proc. You can filter the result passed back from your stored proc using the parameter array in CR though. You could also allow the user to format the parameter input in such a way that it is a string and then you could have the stored proc parse the parameter string. HTH
|
| IP Logged |
|
bgovindhan
Newbie
Joined: 14 Dec 2011
Location: United States
Online Status: Offline
Posts: 14
|
Posted: 14 Dec 2011 at 9:06am |
Thanks for the reply. I am not using the stored procedure. The parameter ?pCONTRACT_ID returns something like 368|48|28|35 when I select multiple values for the contract ID. So, I want to use this in the record selection as {CONTRACT_MASTER.CONTRACT_ID} in (368,48,28,35). How would I do it.
|
| IP Logged |
|
comatt1
Senior Member
Joined: 19 May 2011
Online Status: Offline
Posts: 337
|
Posted: 14 Dec 2011 at 9:21am |
in the selection expert, if you are using a parameter that allows multiple values, you just set the field is equal to {parm values}
|
| IP Logged |
|
bgovindhan
Newbie
Joined: 14 Dec 2011
Location: United States
Online Status: Offline
Posts: 14
|
Posted: 14 Dec 2011 at 9:56am |
I used it in the selection expert as
{CONTRACT_MASTER.CONTRACT_ID} = {?pCONTRACT_ID}. It's not taking it. It is saying a number range is required and it is highlighting this parameter.
|
| IP Logged |
|
comatt1
Senior Member
Joined: 19 May 2011
Online Status: Offline
Posts: 337
|
Posted: 14 Dec 2011 at 9:59am |
is it set to allow multiple values? TRUE
I would set discrete to False Edited by comatt1 - 14 Dec 2011 at 10:01am
|
| IP Logged |
|
bgovindhan
Newbie
Joined: 14 Dec 2011
Location: United States
Online Status: Offline
Posts: 14
|
Posted: 14 Dec 2011 at 10:04am |
|
The parameter pCONTRACT_ID is set to allow multiple values with discrete values radio buton checked. Also, the user may or may not select any contract ID when they run the report.
|
| IP Logged |
|
bgovindhan
Newbie
Joined: 14 Dec 2011
Location: United States
Online Status: Offline
Posts: 14
|
Posted: 14 Dec 2011 at 10:06am |
So, there are three options.
1. Discrete values
2. Range values
3. Discrete and range values.
Which one do you want me to select? You said to set the discrete to false. So, should I select range values?
|
| IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 14 Dec 2011 at 2:26pm |
if the value of the parameter is 3|4|5|, then the In for an array won't work, as it isnot an array. I would try something like: local stringvar array arr1:= split({?parameter}, "|") local numbervar array arr2; local numbervar ind; redim arr2(ubound(arr1)); for ind = 1 to ubound(arr1) do ( arr2[ind] = val(arr1[ind]); ); now you can use the array arr2 in your record selection formula...so all of this would be in the record selection. At least this is the tack that I would take for what I believe is the data being returned by the parameter. HTH )
|
| IP Logged |
|
bgovindhan
Newbie
Joined: 14 Dec 2011
Location: United States
Online Status: Offline
Posts: 14
|
Posted: 15 Dec 2011 at 8:55am |
Thanks for the reply. When I try this, it is giving an error saying that "The array must be subscripted. For example: Array and it is pointing to the parameter filed. In my case, it is pointing to pCONTRACT_ID parameter.
|
| IP Logged |
|
|
|
