Solved

Crystal Reports: Formula for multiples value lookup

Posted on 2013-12-03
14
808 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
  • 8
  • 3
  • 3
14 Comments
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 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
 

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 100

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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 34

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 100

Expert Comment

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

mlmcc
0
 
LVL 34

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 34

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

939 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now