Solved

Modify formula in select statement

Posted on 2012-04-06
22
419 Views
Last Modified: 2012-05-09
Hi, I need some help modifying a formula in the select statement. There are other parameters involved (like CaseSense) but the main one I'm focused on now is "Keyword". I want to add a second parameter (Keyword2 and maybe even Keyword3) to this. I want these to be using the "OR" operator. Can someone provide the change for this?

Just a little background on this report we are using. This basically searches one of the fields for specific keywords. You can use ? or * as wildcards currently but it doesn't support searching multiple keywords throughout the report. It will only search for consecutive keywords (which is good also and we want to keep that). I want to give the end users the option to search for multiple keywords throughout the report also though.

I have one more additional request. How much more difficult is it to put logic in so it will take symbols (like || or &&) instead of using the OR operator? So instead of using multiple "Keyword" parameters, I want to keep it currently at one but add some logic into it so if the user puts in specific characters or symbols, it will pick it up as an AND or OR operator. For example, if they put in "apples||oranges" it will search for apples or oranges...or "apples&&oranges" will search for reports that contain both words.

Attached is a sample text of the formula currently used in the select expert.

Thanks.
Select-Statement.txt
0
Comment
Question by:greyknight17
  • 11
  • 6
  • 5
22 Comments
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 100 total points
ID: 37818037
How difficult this is depends on the full range of allowed values.

For instance can I search for

    Apple* ||  Orange?  && Plums

Can you explain in words the possible inputs?

How many && and ||an there be?

The more complex input you allow, the slower the report will run.  It may be better actually build the SQL in the database to take advantage of its search capabilities.

mlmcc
0
 
LVL 15

Author Comment

by:greyknight17
ID: 37818089
Thanks for the quick reply.

The sample input you mentioned above is perfect for what we need it for. We just need to have one or two more keyword parameters to use (either OR or AND). Definitely want to have the wildcards available as well.

So, either two &&, two || or one of each.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 37818181
The parsing of something like that is probably beyond the capability of a Crystal formula

If you mix && and || you have an issue as to the order of operations.  

In your quesiton you mention consecutive keywords.  Does that mean you want apple && orange to match apple ......orange or do you need it to match orange ...... apple

Is it a full word match or will substring matches work?  For instance does Apple match Apples?

mlmcc
0
 
LVL 15

Author Comment

by:greyknight17
ID: 37818232
Right now, if we input "one apple" for example, it will only return results that have those two words together (which is fine). We just want to give the users more options, so if they want to search for say "one" or "apple" separately, it will return those results.

Currently it should match the entire word only unless a wildcard is used. Unless Apple* or Apple? is used, I don't think Apples will show up.

If you can come up with just using && or ||, that would be great also. I guess I can just create two separate reports if they really need both options.

Thanks.
0
 
LVL 34

Expert Comment

by:James0628
ID: 37818510
If you have a lot of records, it would probably be much better if you could handle this on the server (assuming that your datasource is a db), like in a stored procedure, or in a query entered in a CR Command.  I'm guessing that a record selection formula that complex (and even more complex with the changes that you're trying to make) will not be passed to the server, so the server will send all of the records to the report, and the report will do the filtering.  If you're talking about a lot of records, that's going to take a while and tie up the server and network bandwidth.

 Having said that, one idea for doing this in CR would be two multi-value parameters, one for "and" and one for "or".  For example, ?all_of_these_words and ?any_of_these_words.  The record selection formula could theoretically loop through the values in each parameter and test each value (like the checks that you're doing now), and combine the results based on which parameter each value was in.  That seems easier than trying to parse a string like "apples||oranges&&plums".  You could treat the two parameters as separate (include all records that match _either_ parameter), or combined (include only the records that match _both_ parameters).

 That's off the top of my head.  It seems like it could be done, but it's not the kind of thing I would _want_ to do in a record selection formula.

 Or, for a somewhat simpler approach, you could just have some separate "and" and "or" parameters (instead of multi-value parameters).  Like ?and1, ?and2, ?or1 and ?or2.  Those are terrible names, but hopefully you get the idea.  :-)  That would be simpler than trying to go through the values in some multi-value parameters.

 FWIW, I think it would be possible to parse a string like "apples||oranges&&plums".  It wouldn't be my first choice, but I think it could be done, although, like mlmcc said, there is a question of how to handle conditions that include "and" and "or".  Normally, "and" has precedence, so (apples || oranges && plums) would equate to (apples or (oranges and plums)), meaning records that contain "apples", or contain both "oranges" and "plums".  How would you want something like "apples||oranges&&plums" interpreted?

 FWIW, I think that could be one potential benefit to using separate "and" and "or" parameters.  You set the report up to include the records that match both sets of values, or either set, and let the users know which way it works, as opposed to them entering a string using "||" and "&&" and not getting the records they were looking for, because the report is not interpreting the string the same way they do.

 James
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 37819500
I think that is a good idea.  It would be easier to code.

mlmcc
0
 
LVL 15

Author Comment

by:greyknight17
ID: 37820001
Regarding the "apples||oranges&&plums" interpretation, it would be in order or precedence so it will return only those records that have "apples" or "oranges...plums" (orange and plums).

Yes, I just brought up the more complicated && and || to see if it was possible. The additional parameters were definitely the first choice I wanted but I'm not sure how to do that either. Can someone post the modifications to include two extra OR parameters? If possible, add 3 AND parameters to it also.

Thanks.
0
 
LVL 34

Accepted Solution

by:
James0628 earned 400 total points
ID: 37820991
Do you want all of the parameters to work like the ?Keyword parameter in the formula that you posted?

 I think the simplest thing might be to just create the new parameters (for OR and AND) and then duplicate the formula that you posted for each parameter.  By that I mean create a separate formula to check each parameter, as opposed trying to do everything in the record selection formula.  Each formula would do your tests on one parameter and produce True/False, and then you'd combine the results from those formulas in the record selection formula.

 For example:

 Rename your ?Keyword parameter to ?OrWord1 (again, not the greatest name :-).

 Create a new formula named @check_OrWord1, copy your record selection formula and paste it into that formula, and replace the references to ?Keyword with ?OrWord1 (assuming that CR didn't already do that for you).

 Then in the record selection formula you would just have {@check_OrWord1}, so the record selection formula would just get the result from that formula.

 Then create an ?OrWord2 parameter, and a @check_OrWord2 formula, copy the formula from @check_OrWord1 into @check_OrWord2 and replace the references to ?OrWord1 with ?OrWord2.  The variables are all local, so you don't have to worry about those.  Then the record selection formula would be:

{@check_OrWord1} or {@check_OrWord2}

 You'd do the same thing for the third "or" parameter, and the 3 "and" parameters.  Eventually, your record selection formula would look like this:

({@check_OrWord1} or {@check_OrWord2} or {@check_OrWord3}) and
({@check_AndWord1} and {@check_AndWord2} and {@check_AndWord3})


 By breaking down the "or" and "and" words into separate parameters, and using a separate formula to check each parameter, it becomes simple to combine the results however you like in the record selection formula.  This is all untested, of course, but it seems straightforward enough.

 You would also need to include something in each formula (@check_OrWord1, @check_OrWord2, etc.) so that they default to True if the parameter in question is empty.  It could be as simple as:

{?parameter} = "" or
(
<rest of your tests>
)


 Or you could check for empty parameters in the record selection formula:

(
({?OrWord1} = "" or {@check_OrWord1}) or ({?OrWord2} = "" or {@check_OrWord2}) or
({?OrWord3} = "" or {@check_OrWord3})
)
 and
(
({?AndWord1} = "" or {@check_AndWord1}) and ({?AndWord2} = "" or {@check_AndWord2}) and
({?AndWord3} = "" or {@check_AndWord3})
)


 James
0
 
LVL 15

Author Comment

by:greyknight17
ID: 37821203
Thanks for the details James. It seems pretty straight forward. I'll give it a try.
0
 
LVL 15

Author Comment

by:greyknight17
ID: 37823538
I made all the changes for the OR keywords only to see how it would work out. It looks like the report will pull everything if I leave any of the keyword parameters blank. I think I followed everything you mentioned including adding the check for blanks in the select expert. How do I make it so if one of the parameters is empty or blank to ignore it?

Thanks.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 37824036
What version of Crystal?
Versions before CR2008 require a value for each password.  CR2008 and CR2011 allow optional parameters

mlmcc
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 15

Author Comment

by:greyknight17
ID: 37824167
Crystal Reports XI
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 37824179
All parameters will require a value.

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
ID: 37825172
The checks for "" were supposed to "ignore" the "empty" parameters, by having the tests for a parameter return True if the parameter was blank, but you're right - If you don't enter anything for any parameters, you get everything.

 Maybe you could just add a separate check for the case when all of the parameters are blank?

({?OrWord1} <> "" or {?OrWord2} <> "" or {?OrWord3} <> "" or
{?AndWord1} <> "" or {?AndWord2} <> "" or {?AndWord3} <> "") and
(
({?OrWord1} = "" or {@check_OrWord1}) or ({?OrWord2} = "" or {@check_OrWord2}) or
({?OrWord3} = "" or {@check_OrWord3})
)
 and
(
({?AndWord1} = "" or {@check_AndWord1}) and ({?AndWord2} = "" or {@check_AndWord2}) and
({?AndWord3} = "" or {@check_AndWord3})
)


 If all of the parameters are blank, the first part will be False, so the record selection formula will be False.

 For the record, I'm talking about the parameters getting a blank/empty value (""), as opposed to no value at all.  mlmcc is talking about not passing any value at all to a parameter.  I'm assuming that you're just talking about a parameter that has been given a default value of an empty string and the user hasn't entered a different value for it, so it's just "".

 James
0
 
LVL 15

Author Comment

by:greyknight17
ID: 37845057
Thanks. I'll give that a try also.
0
 
LVL 15

Author Closing Comment

by:greyknight17
ID: 37906253
Thanks for all the help. Still seem to be running into some problems with it. It seems to be accepting "blank" as a criteria also and returning everything basically. I'll try to figure this out unless anyone else has other ideas that I can try. If I only use Keyword1 for example and leave Keyword2 and Keyword3 blank, it returns everything.

Accepting solution now as it has dragged on longer than necessary.
0
 
LVL 34

Expert Comment

by:James0628
ID: 37908242
I don't know what your formula looks like now, but I see now that the section for the "or words" (if you're using that) would do that.  I swear, it seemed to make sense when I wrote it.  :-)

 No guarantees (I've thought I had it figured out before), but if you don't want an empty "or word" parameter to affect the results, try changing the "or" section to something like this:

(
({?OrWord1} <> "" and {@check_OrWord1}) or ({?OrWord2} <> "" and {@check_OrWord2}) or
({?OrWord3} <> "" and {@check_OrWord3})
)


 James
0
 
LVL 15

Author Comment

by:greyknight17
ID: 37908531
Thanks for all your help on this James. I'll give this another try.
0
 
LVL 34

Expert Comment

by:James0628
ID: 37913411
<sigh>  Just noticed another potential issue.

 I had a set of checks for the "or words" and a set of checks for the "and words", joined by AND.  That made sense at the time, because I set the formula up so that an empty parameter meant "true".  So, for example, if you didn't enter any "or words", that whole section would evaluate to True, so you'd end up with

 True and ("and word" tests)

 But with the new version of the "or word" tests, empty parameters mean "false", so if you don't enter any "or words", that section comes out False, so you'd end up with

 False and ("and word" tests)

 so, you wouldn't get any records.

 I think you need to change the AND in between the "or word" and "and word" tests to OR.

 And if you're using a separate set of tests before those to see if all of the parameters are empty (as shown in one of my previous posts), then I think you should put all of the "or word" and "and word" tests, combined, in () too.  Without those, in pseudo-code, you would have:

(<empty param tests>)
 and
(<"or word" tests>)
 or
(<"and word" tests>)

 CR would do the AND first, which is not what you want.  What you want is:

(<empty param tests>)
 and
(
(<"or word" tests>)
 or
(<"and word" tests>)
)


 If you still have any problems or questions, can you post your formula?  I'm just making guesses about what it looks like now.


 Also, FWIW, I think the empty parameter tests could be simplified a bit to

({?OrWord1} + {?OrWord2} + {?OrWord3} +
 {?AndWord1} + {?AndWord2} + {?AndWord3} <> "")


 Just concatenate all of the parameters together and if you don't get an empty string, they're not all empty.  Or you could test them separately, as in my previous post.  This is just a bit shorter.

 James
0
 
LVL 15

Author Comment

by:greyknight17
ID: 37915120
I didn't get much time to work on this lately. The last thing I had was the select statement you had previously on 4/9:

({?OrWord1} <> "" or {?OrWord2} <> "" or {?OrWord3} <> "" or
{?AndWord1} <> "" or {?AndWord2} <> "" or {?AndWord3} <> "") and
(
({?OrWord1} = "" or {@check_OrWord1}) or ({?OrWord2} = "" or {@check_OrWord2}) or
({?OrWord3} = "" or {@check_OrWord3})
)

I'm only using OR for now to see how it works.

Unfortunately, the timing is not right. My contract just ended and my access is removed. I will have to work on this on the next contract (which should be using a similar application) and connect to that database to continue working on this.

If I still have problems, I will post back or create a new post and link to this one when the time comes. Thanks for your continued help on this problem.
0
 
LVL 34

Expert Comment

by:James0628
ID: 37917957
OK, this is really getting annoying now (and kind of embarrassing).  :-)  I've spotted another issue.

 If you change the operator between the "or word" tests and "and word" tests to OR, as I suggested in my last post, you'd need to change the "and word" section too.  As it is, if you use OR and the user doesn't enter any "and words", you would get every record, regardless of the "or words".

 Here we go again.  :-)

// Make sure at least one parameter is not empty
(
 {?OrWord1} <> "" or {?OrWord2} <> "" or {?OrWord3} <>  "" or
 {?AndWord1} <> "" or {?AndWord2} <> "" or {?AndWord3} <> ""
)
and
(
// Check the "or words"
 (
 // One or more "or words" is not empty and matches
 ({?OrWord1} <> "" and {@check_OrWord1}) or
 ({?OrWord2} <> "" and {@check_OrWord2}) or
 ({?OrWord3} <> "" and {@check_OrWord3})
 )
 or
// Check the "and words"
 (
  // Make sure at least one "and word" is not empty
  ({?AndWord1} <> "" or {?AndWord2} <> "" or {?AndWord3} <> "") and
  (
   // All of the non-empty "and words" match
   ({?AndWord1} = "" or {@check_AndWord1}) and
   ({?AndWord2} = "" or {@check_AndWord2}) and
   ({?AndWord3} = "" or {@check_AndWord3})
  )
 )
)


 I realize that you may not be able to use this right now, but I wanted to get this latest version worked out while it was still fresh on my mind.

 James
0
 
LVL 15

Author Comment

by:greyknight17
ID: 37948059
Thanks James. I do appreciate all the follow-ups on this when you notice other problems. Will definitely give this a try when I'm able to.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
I recently went through setting up a JasperReports Server using the AWS EC2 instance, and this article will cover some basic administration tasks I had to perform.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

746 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

13 Experts available now in Live!

Get 1:1 Help Now