Using a Wildcard in a parameter
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=552
Printed Date: 04 May 2024 at 6:50pm
Topic: Using a Wildcard in a parameter
Posted By: Graham
Subject: Using a Wildcard in a parameter
Date Posted: 19 Apr 2007 at 7:53am
|
I have a report with parameters where I want to select specific customers. I want to use a wildcard entry '*' in the parameter field, when I want to return all customers within that field.
Can anyone please tell me how to do this?
 Graham
|
Replies:
Posted By: EscApe
Date Posted: 07 May 2007 at 1:57am
|
I suggest the following solution.
- set your default value 'ALL' for parameter - add if statement to Select Expert: If {?Parameter}='ALL' Then {Database.Field} like '*' Else {Database.Field} = {?Parameter} Then when asked for parameter, just write (or select from drop down list) ALL to get all records. |
Posted By: omega_red
Date Posted: 10 May 2007 at 11:19am
Helpfull. worked very well 
|
Posted By: Graham
Date Posted: 11 May 2007 at 4:48am
| Works perfectly, Thanks very much for your help |
Posted By: aekelund
Date Posted: 11 May 2007 at 7:23am
|
This may be a dumb question but how do you set the defualt value to "all"? |
Posted By: omega_red
Date Posted: 11 May 2007 at 7:33am
|
What I did...
When creating a parameter field [?] I used the value type String. Click the button that says "Set default values" There is a text box that says "Select or enter value to add" Type in ALL and click the > arrow. Add any other values. you ar edone. |
Posted By: aekelund
Date Posted: 11 May 2007 at 7:41am
| Unfortunately I don' t have a Set Defualt values button! I am running Crystal version 11. |
Posted By: omega_red
Date Posted: 11 May 2007 at 8:35am
| I am several version behind you. I am running V9. I am assume that if you look somewhere in the parameter field setup ox for some value related thing you might find what your looking for. I am no longer of use. |
Posted By: vijayk
Date Posted: 11 May 2007 at 12:22pm
|
I suggest the following solution. - set your default value 'ALL' for parameter - add if statement to Select Expert: If {?Parameter}='ALL' Then {Database.Field} like '*' Else {Database.Field} = {?Parameter} Then when asked for parameter, just write (or select from drop down list) ALL to get all records. ************ If you use the above method, doesn't it affect the performance as it is using LIKE operator..? I am assuming in your SQL, it will append the like statement. is there anyway that it eliminates the LIKE statement from the where clause when you enter ALL or Select ALL in the parameter window.
Thanks
Vijay
|
Posted By: bradschi
Date Posted: 02 Apr 2009 at 12:15pm
|
This really helped me but i do have another question. I am trying to do this with two variables. This is what i have in the formula editor in the select expert: If {?Tech} = 'ALL' Then {Command.TECH} like '*' Else {Command.TECH} = {?Tech} AND If {?Agent}='ALL' Then {Command.AGENT} like '*' Else {Command.AGENT} = {?Agent} If I select ALL for one and then specify the other it works fine but if i do it the other way around it shows all of both. Any help would be greatly appreciated. |
Posted By: DBlank
Date Posted: 02 Apr 2009 at 2:08pm
|
Try and put a () around each part of the statement to make sure they both execute:
(If {?Tech} = 'ALL' Then {Command.TECH} like '*' Else {Command.TECH} = {?Tech})
AND (If {?Agent}='ALL' Then {Command.AGENT} like '*' Else {Command.AGENT} = {?Agent}) |
Posted By: bradschi
Date Posted: 03 Apr 2009 at 6:32am
| Thank you for your fast response. I Tried to enclose them and no not data is pulled. |
Posted By: DBlank
Date Posted: 03 Apr 2009 at 6:47am
| Sorry, what do you mean by "not data"? |
Posted By: kak37
Date Posted: 26 Jul 2009 at 3:16pm
worked great, thank u!
 |
Posted By: DBlank
Date Posted: 26 Jul 2009 at 7:05pm
|
For future views/reference the below should be a more efficient approach:
({?Tech} = 'ALL' or {Command.TECH} = {?Tech}) AND ({?Agent}='ALL' or {Command.AGENT} = {?Agent}) |
Posted By: zach18
Date Posted: 09 Jun 2011 at 5:25am
|
{fpartno} = IIF({?PartNbr} = 'All', '*' , {?PartNbr})
this is what i would hope happened, i tried to use the above solution given on the forum but it didnt work. It was ignoring my time constraints. this is my complete selection formula: fpartno} = IIF({?PartNbr} = 'All', '*' , {?PartNbr}) and {fctime_ts} >= {@StartDate} and {fctime_ts} < {@EndDate} this isnt like the solution in the fact that it doesnt use like. anyone know how to either make the mentioned answer on this forum work with dates or how ot make this way work? ------------- Using Crystal Report 2008 www.zachtech.us |
Posted By: DBlank
Date Posted: 09 Jun 2011 at 5:38am
|
({?PartNbr} = 'All'
or
{?PartNbr}= {fpartno}) and
{fctime_ts} >= {@StartDate} and {fctime_ts} < {@EndDate} |
Posted By: zach18
Date Posted: 09 Jun 2011 at 5:46am
|
the problem with what you have is that i need to get all the part numbers. that would have to be a like '*' or something to that effect ------------- Using Crystal Report 2008 www.zachtech.us |
Posted By: DBlank
Date Posted: 09 Jun 2011 at 5:58am
|
{?PartNbr} = 'All' evalautes to true so has no filtering effect which is equivalent to your desired effect
|
Posted By: zach18
Date Posted: 09 Jun 2011 at 6:12am
|
i guess im missing somthing, could you maybe explain how it being true will help me select every part number in the date range? ------------- Using Crystal Report 2008 www.zachtech.us |
Posted By: DBlank
Date Posted: 09 Jun 2011 at 6:21am
|
User has a run time param (?partnmb)
the select statement starts with a paranthed OR statement. In that OR statement as soon as a condition is met it evaluates to TRUE and moves to the next part of the select statement (the AND startdate part).
so when a user selects "ALL" in your param
{?PartNbr} = 'All' is a true statement which ends the OR. but it does not actually filter your data which is the same thing as selecting all records (but more efficient because it does not have to evaluate each row).
Since the OR statement ended it will now apply your date range criteria.
Does that help?
|
Posted By: zach18
Date Posted: 09 Jun 2011 at 10:40am
|
Perfect explanation, that did EXACTLY what i had hoped it would. ------------- Using Crystal Report 2008 www.zachtech.us |
Posted By: sharona
Date Posted: 10 Jun 2011 at 7:57am
|
If {?Tech} = 'ALL' Then true Else {Command.TECH} = {?Tech} OR If {?Agent}='ALL' Then true Else {Command.AGENT} = {?Agent}
true works as well. use it all the time. ------------- sharona |