Solved

Crystal Report using a parameter for partial text matching not working

Posted on 2015-02-11
10
276 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
  • 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 34

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 100

Accepted Solution

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

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 100

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 34

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 34

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 34

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

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…
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…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

760 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

20 Experts available now in Live!

Get 1:1 Help Now