Solved

Crystal Reports: Formula for multiples value lookup

Posted on 2013-12-03
14
821 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

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
 

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
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…

803 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