bruno71
asked on
Crystal Reports 2008 - Finding an array value in a string
I've got a report with an optional parameter for Lot # (ex. n498p). The field in the database is a string field that could contain multiple lot numbers (ex. "n498p, n502p"). For the report parameter, I want to use multiple values so they can search for 1 or more lot numbers. In the record selection formula, how do I loop through that array to check each value and see if it show up "in" the string field?
~bruno71
~bruno71
Never mind, I read it too quickly and missed the part about the actual database field. The rest still stands, but I'll need to work something else out.
~Kurt
~Kurt
The following code will work IF you input wildcards into your parameter entry...
If
HasValue({?multiplevaluepa rameter})
Then
{table.field} Like {?multiplevalueparameter}
Else
True
Please note, that while this will work, it won't be particularly efficient, since using LIKE will force a full table scan, but at least it will be processed on the database as opposed to the client.
~Kurt
If
HasValue({?multiplevaluepa
Then
{table.field} Like {?multiplevalueparameter}
Else
True
Please note, that while this will work, it won't be particularly efficient, since using LIKE will force a full table scan, but at least it will be processed on the database as opposed to the client.
~Kurt
create a stored procedure like this from your table
declare @str as varchar(1000)
//set @str = '#E00001#, #E00002#, #E00003#'
select * from ppemp where
@str Like '%#' + empno_k + '#%'
and pass the parameter as
'#E00001#, #E00002#, #E00003#'
you will get the result
declare @str as varchar(1000)
//set @str = '#E00001#, #E00002#, #E00003#'
select * from ppemp where
@str Like '%#' + empno_k + '#%'
and pass the parameter as
'#E00001#, #E00002#, #E00003#'
you will get the result
crystal report has to made with the stored procedure
It doesn't have to be made with a stored procedure.
~Kurt
~Kurt
ASKER
Kurt,
It's kind of working. I'm using LIKE with the wildcards...
(not HasValue({?Block Lot #}) OR {GW_SCRAP.BlockLotNum} like "%" + {?Block Lot #} + "%")
...but it's not finding any fields that contain the value, only fields that are equal to it. For example, it will find a record with "n498p", but not "n498p, n500p"
Mike
It's kind of working. I'm using LIKE with the wildcards...
(not HasValue({?Block Lot #}) OR {GW_SCRAP.BlockLotNum} like "%" + {?Block Lot #} + "%")
...but it's not finding any fields that contain the value, only fields that are equal to it. For example, it will find a record with "n498p", but not "n498p, n500p"
Mike
Do you still need help with this?
mlmcc
mlmcc
ASKER
Yes...I never quite got it to work just right. I have a report parameter that let's them input multiple values. However, the report is only finding records EQUAL to the value, not CONTAINING the value.
~bruno71
~bruno71
So you have a field that may contain say
"n498p, n502p"
and a parameter that may contain
"n498p" or "" or "n498p, n503p"
Is that correct?
mlmcc
"n498p, n502p"
and a parameter that may contain
"n498p" or "" or "n498p, n503p"
Is that correct?
mlmcc
ASKER
I may have the following fields...
1. "n498p"
2. "n498p, n502p"
3. "n502p"
For the parameter (which allows multiple values), I enter "n498p" and "n502p" (enter one...click the arrow to add it to the list...enter the second...click the arrow...etc).
The report will find records #1 & 3, but not #2.
Also, if I would enter "n498" for the parameter, it wouldn't find any of them.
I hope this is making sense...
~bruno71
1. "n498p"
2. "n498p, n502p"
3. "n502p"
For the parameter (which allows multiple values), I enter "n498p" and "n502p" (enter one...click the arrow to add it to the list...enter the second...click the arrow...etc).
The report will find records #1 & 3, but not #2.
Also, if I would enter "n498" for the parameter, it wouldn't find any of them.
I hope this is making sense...
~bruno71
I'll see what I can do.
I assume there are other values that can occur.
mlmcc
I assume there are other values that can occur.
mlmcc
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks! I think that worked. I'll continue to test it.
~bruno71
~bruno71
You can simply use {table.field} = {?multiplevalueparameter}
Since you're using an optional parameter, however, you do need to account for the possibility that the parameter might be empty. As such, use the following:
If
HasValue({?multiplevaluepa
Then
{table.field} = {?multiplevalueparameter}
Else
True
~Kurt