?
Solved

Crystal Reports: Formula for multiples value lookup

Posted on 2013-12-03
14
Medium Priority
?
917 Views
Last Modified: 2013-12-07
Experts,

Thank you in advance, as this issue has been puzzling me for a while.
Here is what I am trying to do.  I have a field name "COMBINED_RESOURCES", which can contain multiples resources that are speperated by a comma and a space so ", ".  Here is an example output:
NOC Operations, Critical Engineering, Video Operations, Maryland Headend
or it can be just a single resource without a comma like:
NOC Operations

More details:
I want to be able add multiple resources to a parameter and return the values if any of those values in the parameter is in the "COMBINED_RESOURCES".

so, lets say I add in the parameter:
NOC Operations
Critical Engineering

It should return the following records:
RECORD#   COMBINED_RESOURCES
1                NOC Operations
2                NOC Operations, Video Operations
3                Critical Engineering, Video Operations


Thank you, so much!
0
Comment
Question by:Maliki Hassani
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 3
  • 3
14 Comments
 
LVL 101

Accepted Solution

by:
mlmcc earned 2000 total points
ID: 39692519
I can't think of a way to make that work.
Have you tried
     InStr({CombinedResources},{?Parameter}) > 0

mlmcc
0
 

Author Comment

by:Maliki Hassani
ID: 39692554
hmm..

Maybe this can help you some how:

Well what I have been doing in the past with my reports for "assigned groups" is to create 2 parameters, one for listing multiple groups I_ASSIGNED_GROUPS, which has a parameter called DELIMITER, that has the selection for |,. etc. I then use these 2 paramaters to retrieve records that match any of the assigned groups I listed.  This works fine but what I am trying to do is actually opposite because the "COMBINED_RESOURCES" has muliple values.

My Parameter looks like this:
National-Analysis & Development|National-Carrier Operations|National-Surveillance

{COMBINED_GROUP} in split(join({?I_ASSIGNED_GROUPS},{?I_DELIMITER}),{?I_DELIMITER})

It would seem like I would need to do something like this:
split(join({?I_Combined Resources},{?I_DELIMITER}),{?I_DELIMITER}) in split(join({COMBINED_RESOURCES},{?I_DELIMITER}),{?I_DELIMITER})
0
 

Author Comment

by:Maliki Hassani
ID: 39692607
I believe this does the trick:
{Combined Resources} in split(join({?I_RESOURCED_GROUPS},{?I_DELIMITER}),{?I_DELIMITER})
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Maliki Hassani
ID: 39692637
No, that isn't working
0
 

Author Comment

by:Maliki Hassani
ID: 39692757
Your InStr({CombinedResources},{?Parameter}) > 0

is making progress..  

 InStr({Combined Resources},"NOC Operations") > 0  works!

If I do:
  InStr({Combined Resources},{?I_RESOURCED_GROUPS}) > 0 I get the following error:

This array must be scripted. For example:Array "i"

If i can figure this out I will have it working.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 39693083
Try it this way
(
Local NumberVar index;
Local BooleanVar Found := False;
For Index := 1 to UBound({?I_RESOURCED_GROUPS}) do
     Found  := Found OR  InStr({Combined Resources},{?I_RESOURCED_GROUPS}[Index]) > 0
)

Open in new window


mlmcc
0
 

Author Comment

by:Maliki Hassani
ID: 39693750
Not making any progress here. I am still searching.

Your formula only returns True to all records.
0
 

Author Comment

by:Maliki Hassani
ID: 39693783
Trying a different route:
If {?I_RESOURCED_GROUPS} = "Headend" then
(
InStr({@F-Combined Resources},"Central Florida-East Headend") > 0
or
InStr({@F-Combined Resources},"Central Florida-West Headend") > 0
  )
else if {?I_RESOURCED_GROUPS} = "BSS" then
(
InStr({@F-Combined Resources},"NSO-BSS") > 0
  )
etc
0
 
LVL 35

Expert Comment

by:James0628
ID: 39697876
You need to add Found (the variable) to the end of mlmcc's formula.  Otherwise, you just get the result of the For loop, which is always True.  Try this:

(
Local NumberVar index;
Local BooleanVar Found := False;
For Index := 1 to UBound({?I_RESOURCED_GROUPS}) do
     Found  := Found OR  InStr({Combined Resources},{?I_RESOURCED_GROUPS}[Index]) > 0;
Found
)

Open in new window

0
 
LVL 101

Expert Comment

by:mlmcc
ID: 39698264
Actually it isn't.  It will be true only if there is a match.

mlmcc
0
 
LVL 35

Expert Comment

by:James0628
ID: 39698356
As far as I can tell, a For loop just produces a True result.  For example, the following gives me a true result:

Local NumberVar i;
Local BooleanVar found;

for i := 1 to 10 do
  found := false

Open in new window

0
 

Author Comment

by:Maliki Hassani
ID: 39701435
I went with the following:
If {?I_RESOURCED_GROUPS} = "Headend" then
(
InStr({@F-Combined Resources},"Central Florida-East Headend") > 0
or
InStr({@F-Combined Resources},"Central Florida-West Headend") > 0
etc

I appreciate the help!
0
 

Author Closing Comment

by:Maliki Hassani
ID: 39701446
Assisted my final solution
0
 
LVL 35

Expert Comment

by:James0628
ID: 39702970
FWIW, if a formula like that works, then it might be a better solution than a loop, like the one that mlmcc posted.  Assuming that you're doing these tests in a record selection formula, a loop won't be passed to the server, but tests like the ones that you're using might.

 James
0

Featured Post

How To Reduce Deployment Times With Pre-Baked AMIs

Even if we can't include all the files in the base image, we can sometimes include some of the larger files that we would otherwise have to download, and we can also sometimes remove the most time-consuming steps. This can help a lot with reducing deployment times.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question