?
Solved

Crystal Report using a parameter for partial text matching not working

Posted on 2015-02-11
10
Medium Priority
?
396 Views
Last Modified: 2015-02-16
I built a parameter for Medication, it is a string field.  I included in the select statement the following:
{MEDICATE.DESCRIPTION} like {?Medication}.  

The report prompts me for a medication and I put in ?spirin*   I get no results.  There are hundreds of patients with aspirin prescriptions.  

If I take out the parameter and run the report, all the medications/patients show up.

What am I doing wrong?
0
Comment
Question by:Becky Edwards
[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
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 18

Expert Comment

by:vasto
ID: 40604794
I don't think Crystal supports ?.
Try  %spirin%  and  *spirin*
0
 
LVL 35

Expert Comment

by:James0628
ID: 40605326
"?" should match any single character.  I'm guessing that you're trying to match "A" or "a"?  Otherwise, you'd presumably just include the letter.  FWIW, if your db is not case sensitive, you may be able to include an "a", and have it match "a" or "A".

 ?spirin* would match any string that _starts_ with "aspirin".  If that may not be the first thing in the field, use *spirin* instead.  I'm assuming that the field won't contain any other strings that include "spirin".

 Of course if your db is case sensitive, then "spirin" will only match "spirin", not "SPIRIN", "Spirin", etc.  So, if "aspirin" is not always in lowercase, you may need to adjust for that.

 James
0
 
LVL 101

Accepted Solution

by:
mlmcc earned 2000 total points
ID: 40605608
Agree with the above.  

Crystal uses ? for a single character and * for multiple.  
Assuming this is in the SELECT EXPERT Crystal will translate the ? and * to the appropriate wildcard characters for your database.

you are doing this in a COMMAND then you have to use the wildcards for the database.

Unless you need to match the case you can use

UCase({MEDICATE.DESCRIPTION}) like UCase({?Medication})

Rather than having the user add the wildcards you could use
UCase({MEDICATE.DESCRIPTION}) like "*" & UCase({?Medication}) * "*"

mlmcc
0
On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

 

Author Closing Comment

by:Becky Edwards
ID: 40607956
James your information was good, but I have to give this one to mlmcc because I was actually able to copy and paste the last bit into crystal to get my solution.  I had to change it a small bit (typo)  to get it to work.  That last part from * to &.  Otherwise perfect!

UCase({USRINFO_Provider.SEARCHNAME}) like "*" & UCase({?Choose Provider}) & "*"
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 40608120
You have the option to split points between comments if you so wish.

I can reopen the question if you want to allow you to do that.

mlmcc
0
 
LVL 35

Expert Comment

by:James0628
ID: 40608629
FWIW, you only need the UCase functions if the case can vary (eg. sometimes you have "spirin" and sometimes you have "SPIRIN"), and your db is case sensitive.  You didn't mention different cases, so you may not need those functions.

 James
0
 

Author Comment

by:Becky Edwards
ID: 40608723
Well, if James would like to give me the exact sequel to the option he has, then I can split the points.

Becky
0
 
LVL 35

Expert Comment

by:James0628
ID: 40608829
<shrug>  It depends.

 First of all, is the case an issue or not?  If you're not sure, you can always play it safe and use UCase.  But that's going to add some overhead (how much will depend on how long the DESCRIPTION field is, and how many records you have), so it'd be best to only use it if you really need it.

 And do you want to enter the wildcards (as in your first post), or have CR add them (as in mlmcc's code) ?

 Ignoring those questions for the moment, the main problem in your first post seems to have been that you used "?".  If you use {MEDICATE.DESCRIPTION} like {?Medication} in the record selection formula and enter ?spirin in the parameter, that will only match records where DESCRIPTION _begins_ with a single character, followed by "spirin".  IOW, it would match "aspirin" followed by other characters, but not something like "xyz aspirin".  But if you enter *spirin*, that should match any DESCRIPTION that contains "spirin" anywhere in the field.

 James
0
 

Author Comment

by:Becky Edwards
ID: 40612726
Speed is definitely an issue because we are currently accessing a live production environment. So I would prefer the method with the least draw on the server.

?SPIRIN* is the only one that worked, after I changed my record selection to ({MEDICATE.DESCRIPTION}) like ({?Medication}).  So should I create a parameter that says *MEDICATION*
And if I do, will the person using this have to put any special characters in?  I do NOT want them to have to figure that out if at all possible.  I just want them to be able to put any part of the medication name in the empty box and have the machine look for anything with those characters together.  

I have determined everything is in all caps.  So I will specify to everyone their word or partial word must be in all caps.

Thanks,
Becky
0
 
LVL 35

Expert Comment

by:James0628
ID: 40613000
You can add the wildcards to the parameter value, as in the formulas that were posted earlier.  Then the user won't have to enter them.

 If the values in the DESCRIPTION field are _always_ in uppercase, you can leave off the UCase function for that field, but use it for the parameter.  That way, the user can enter the letters in any case, and CR will convert them to uppercase.  This should have no effect on the performance, because there is only one parameter value.  CR will just convert that to uppercase, and should then pass that value to the server.  Performance becomes a concern if you're converting the _field_ to uppercase, because that has to be done for every character in the field, in every record that's read.  But converting the parameter to uppercase is a one-shot thing.

 So, you could use the following, to convert the parameter to uppercase, and add the "*" wildcards.

{MEDICATE.DESCRIPTION} like "*" & UCase ({?Medication}) & "*"

 James
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

752 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