Solved

How to enter a Like parameter

Posted on 2015-01-20
15
129 Views
Last Modified: 2015-03-05
I have a report whereby I have a parameter to select ALL or a task
I have created the following forula
if {?Task Number} ='ALL' then 1 else
if {?Task Number} = {TASK.TASKNUM} then 0 else 1
How can i modify this to be able to select some tasks by inputtin example RS%

Gordon
0
Comment
Question by:GiaHughes
  • 5
  • 3
  • 2
  • +2
15 Comments
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 40559482
Maybe

if {?Task Number} like 'RS*'

Regards
0
 

Author Comment

by:GiaHughes
ID: 40559504
Hi
Tried
if {?Task Number} ='ALL' then 1 else
if {?Task Number} like 'RS%' then 0 else 1

But does not work

Gordon
0
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 40559517
and with *
0
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:GiaHughes
ID: 40559520
Hi

Does not work with the * either
I think it needs to know what field it is looking at so I think the formula should include a variant
of
if {?Task Number} = {TASK.TASKNUM} then 0 else 1

Gordon
0
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 40559528
Sorry can't help further
0
 

Author Comment

by:GiaHughes
ID: 40559556
Someone please help

Gordon
0
 
LVL 18

Expert Comment

by:vasto
ID: 40559705
I believe it should be the opposite

if {?Task Number} ='ALL' then 1 else
 if {?Task Number} like 'RS*' then 1 else 0

if you are trying to find partial matches in  {TASK.TASKNUM} try

 if {TASK.TASKNUM} like {?Task Number} +'*' then 1 else 0


You may need to replace * with %
0
 

Author Comment

by:GiaHughes
ID: 40559781
Hi

What I am trying to do is give an option to see ALL the tasks or select part of the task number
The current formula works ok if you select ALL or a complete task number
Does this make sense

Gordon
0
 
LVL 18

Expert Comment

by:vasto
ID: 40559872
then try

if {?Task Number} ='ALL' then 1 else
if {TASK.TASKNUM} like {?Task Number} +'*'  then 0 else 1

but this will return 1 if the parameter value is ALL or NOT like {?Task Number}

If you want just to filter the data add this to the Record Selection Formula

{?Task Number} ='ALL' OR
{TASK.TASKNUM} like {?Task Number} +'*'
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 40561183
Assuming you want it to match the first characters then it is

{Product.Product Name} LIKE {?ProductName} & "*"

Where are you using the formula?

If it is in the Select Expert then the formula has to return TRUE or FALSE

mlmcc
0
 

Author Comment

by:GiaHughes
ID: 40562031
Hi mimcc

I have a parameter field {Task Number} that says ALL  and allows Custom Values, Multiple values and Discrete values

To make this work I have a formla {Task Number} :-
if {?Task Number} ='ALL' then 1 else
if {?Task Number} = {TASK.TASKNUM} then 0 else 1

Then in the Select Expert @Task Number say is equal to 1.00

This all works fine if I want to see all the tasks or I can add the complete task numbers as a selection

What I would like to be able to do is to either select ALl or Enter the complete task number or select some of the task number i.e. RS% or %RS% etc

Hope this makes sense, so what do I need to modify?
Gordon
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 250 total points
ID: 40562181
I don't see the need for the TaskNumber formula but that may make it easier for you to understand.  One issue with it is the selection criteria won't be passed to the database for filtering so the report could run slower.

To me I think you have the formula reversed onthe last test
I think it should be
{Task Number} :-
if {?Task Number} ='ALL' then 
     1 
else  if {?Task Number} = {TASK.TASKNUM} then 
     1
else 
     0

Open in new window


Do you expect the user to enter the wildcard?  
If so this should work.  User should enter * for the wildcard.  Crystal will convert it to the appropriate value for the database.

{Task Number} :-
if {?Task Number} ='ALL' then 
     1 
else  if  {TASK.TASKNUM} LIKE {?Task Number} then 
     1
else 
     0

Open in new window


If you want to handle the wildcard rather than require the user to enter  *RS*  or *RS  or RS*
you will need to decide if to always add the wildcard to what the user enters or to try to determine if it should be added.

mlmcc
0
 
LVL 35

Assisted Solution

by:James0628
James0628 earned 250 total points
ID: 40562905
Some of this has already been covered, but if the parameter allows multiple values, but _not_ ranges, then a test like ( {field} like {?parameter} ) should work, but the user will have to enter any wildcards (*).  You can't use something like {?parameter} + "*" to add the wildcard, because CR treats a multi-value parameter like an array, so {?parameter} + "*" just adds the "*" as another element in the array, and you end up with the last test being {field} like "*", which matches everything.

 James
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone 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

Suggested Solutions

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…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

679 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