Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Parameter with Like, Specific and ALL Clause Issue Post Reply Post New Topic
Page  of 2 Next >>
Author Message
jszawlosky
Newbie
Newbie
Avatar

Joined: 08 Jan 2014
Online Status: Offline
Posts: 16
Quote jszawlosky Replybullet Topic: Parameter with Like, Specific and ALL Clause Issue
    Posted: 12 Feb 2014 at 7:17am
My dataset has a field that shows City, State ZIP and sometimes country code as well. I need my parameter to gather sales info from the dataset, currently my parameter has the specific dataset listed as a drop down, but I also made it editable. I want the end user to be able to pick a specific City, State ZIP from the drop down or manually type in part of whats in the field, such as for Fresno, CA which may have multiple entries with different ZIP's if the user manually types in Fres I want the result to be every record that has Fres in it.

So the formula I am trying to make thus far is as follows...
if {?CityStateZipParameter} = "ALL" then {CityStateZipDataset} = "*" else
{CityStateZipDataset} like {?CityStateZipParameter}

Not sure what I'm missing here.

Any help would be greatly appreciated.

IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 12 Feb 2014 at 7:27am
what version are you using?
Params were not optional until (i think) versions after XI so that will play a role in this and may change your approach if each param is mandatory.
IP IP Logged
jszawlosky
Newbie
Newbie
Avatar

Joined: 08 Jan 2014
Online Status: Offline
Posts: 16
Quote jszawlosky Replybullet Posted: 12 Feb 2014 at 7:30am
The version is CR 8.5, sorry I didn't list that...
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 12 Feb 2014 at 8:13am
not being able to see your dta exactly this is a guess but your select statment should end up looking something like
 
({?CityStateZipParameter} = "ALL"
or
{CityStateZipDataset} like "*"+{?CityStateZipParameter}+"*")
 
IP IP Logged
jszawlosky
Newbie
Newbie
Avatar

Joined: 08 Jan 2014
Online Status: Offline
Posts: 16
Quote jszawlosky Replybullet Posted: 12 Feb 2014 at 8:31am
Thanks DBlank! That worked, when I type in Fres it brings up the correct data, when I select ALL I get all data for the date range. The only issue I have is when I select a specific City, State ZIP from the drop down it still shows nothing...

Dataset Sample:
Fredericksburg, TX 78624
Fresno, CA 93706
FRESNO, CA 937116096
Fresno, CA 93722-3451
Fresno, CA 93726
Friendswood, TX 77546
Frisco, TX 75034

So with that parameter I have it set to allow editing but I also selected the field and added all values so they show up like the above sample in a drop down list, so if someone knew the specific City, State ZIP they could select it.

I tried to edit the formula you provided a little bit...
({?CityStateZipParameter} = "ALL"
or
{CityStateZipDataset} like "*"+{?CityStateZipParameter}+"*"
or
{CityStateZipDataset} = {?CityStateZipParameter})

When someone selects from the list the report should pull that data for that specific City, State ZIP, not sure if this is possible or not.


Edited by jszawlosky - 12 Feb 2014 at 8:35am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 12 Feb 2014 at 9:45am
what does some raw data in this field {CityStateZipDataset} look like?
IP IP Logged
jszawlosky
Newbie
Newbie
Avatar

Joined: 08 Jan 2014
Online Status: Offline
Posts: 16
Quote jszawlosky Replybullet Posted: 12 Feb 2014 at 9:54am
Rancho Cordova, CA 95742                    
Carrollton, TX 75006                        
Carrollton, TX 75006                        
Houston, TX 77036                           
Rohnert Park, CA 94928                      
Rohnert Park, CA 94928                      
Rohnert Park, CA 94928                      
Duluth, MN 55812                            
Grand Terrace, CA 92313                     
Elm Grove, WI 53122-0825                    
Dallas, TX 75229                            
Ontario, CA 91761                           
Burnsville, MN 55337                        
Rancho Cordova, CA 95745                    
West Chicago, IL 60185                      
Sarasota, FL 342382773                      
Prescott Valley, AZ 86314                   
Bakersfield, CA 93312                       
Garden Grove, CA                            
San Antonio, TX 78232                       
Sacramento, CA 95824                        
Sacramento, CA 95824                        
Sacramento, CA 95824                        
Rancho Cordova, CA 95742                    
Rancho Cordova, CA 95742                    
Rancho Cordova, CA 95742                    
Rancho Cordova, CA 95742                    
Montclair, CA 91763                         
Montclair, CA 91763                         
Las Vegas, NV 89102                         
Fallon, NV 89406                            
Riverside, CA 92507                         
Escondido, CA 92025                         
San Luis Obispo, CA 93401                   
Duarte, CA 91010                            
Montclair, CA 91763                         
Rocklin, CA 95765                           
Murrieta, CA 92562                          
Murrieta, CA 92562                          
Seattle, WA 98108                           
Santa Clarita, CA 91355                     
Van Nuys, CA 91406                          
Van Nuys, CA 91406                          
Palm Springs, CA 92264                      
El Cajon, CA 92020                          
Murrieta, CA 92562                          
Riverside, CA 92507                         
Houston, TX 77036                           
Redding, CA 96001                           
El Cajon, CA 92020                          
Santa Clarita, CA 91355                     
Santa Clarita, CA 91355                     
Escondido, CA 92025                         
Sacramento, CA 95815                        
Laguna Hills, CA 92653                      
Cerritos, CA 90703                          
Cerritos, CA 90703                          
Grand Terrace, CA 92313                     
Worland, WY 82401                           
Orange, CA 92868                            
Palm Desert, CA 92211                       
Palm Desert, CA 92211                       
Scottsdale, AZ 85257                        
Riverside, CA 92507                         
Riverside, CA 92507                         
Riverside, CA 92507                         
Fresno, CA 93727                            
Fresno, CA 93727                            
Fresno, CA 93727                            
IP IP Logged
jszawlosky
Newbie
Newbie
Avatar

Joined: 08 Jan 2014
Online Status: Offline
Posts: 16
Quote jszawlosky Replybullet Posted: 12 Feb 2014 at 9:56am
Each record represents the Ship-To location for a specific order...


Edited by jszawlosky - 12 Feb 2014 at 10:03am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 12 Feb 2014 at 10:25am
do these have carriage returns in them?
IP IP Logged
jszawlosky
Newbie
Newbie
Avatar

Joined: 08 Jan 2014
Online Status: Offline
Posts: 16
Quote jszawlosky Replybullet Posted: 12 Feb 2014 at 11:24am
I don't believe they do, how could I find that out?
IP IP Logged
Page  of 2 Next >>
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.