Parameter like and not like formula

[arodri]

Hello,

I'm not all that experienced with Crystal formulas, and have a parameter issue I can't figure out. I have the report working perfectly, and this parameter is the last step. I have a field in the report called "Terminal" that has almost a hundred different values, but here are examples of some values: [expr, wcash, offline, hqoff, lbay, lback, loff] etc.

I need to add a parameter in the report that has a drop down list for users with the following values: ALL, Wash, and Lube.

ALL - If a user selects this from the drop down list, it will pull up ALL values in the report

Wash - This will pull up the values in the report where TERMINAL not like "l*" - I basically need the report to pull all values that do not start with the letter "L"

Lube - This will pull up all of the values in the report where TERMINAL like "L*" - so just the opposite of wash. I need this to pull up only the records where terminal begins with an "L"

I have no idea where to start when coming up with a formula to make this happen. Any help will be appreciated.
Thank you in advance!

 

[AndyMc]

The way I have done it - in record selection formula:

(
If {?TerminalParameter} = "All" then
true
else if {?TerminalParameter} = "Wash" then
not({TerminalField} startswith "L")
else if {?TerminalParameter} = "Lube" then
{TerminalField} startswith "L"
)

 

[pmax9999]

I recommend avoiding If-Then-Else statements in the Record Selection formula and would do it this way:

[Code Record_Selection_Formula]
{?TerminalParameter} = "ALL"
or
(
{?TerminalParameter} = "Wash" and
not({TerminalField} startswith "l")
)
or
(
{?TerminalParameter} = "Lube" and
{TerminalField} startswith "l"
)
[/Code]
This will guarantee it gets passed to the Where clause of the SQL Query.

Hope this helps.

Cheers
Pete

 

[arodri]

Thanks AndyMc and pmaxx9999!

pmaxx9999 - I used your solution and it seems to be working as far as selecting the appropriate terminals, but for some reason when I select "ALL" or "Wash" from the drop down list in the parameter, it displays ALL of the sites (this report is being group by sites) even though I have another parameter that lets me put in a site number. When I put in a site number, it seems that it totally ignores that part of the logic and displays all of the sites. The only time the site selection works is when I select "Lube" for the terminal drop down.

I also had only one site (while testing) in the record selection, and again when I select "All" or "Wash" from the terminal drop down it totally ignores that and just displays all sites.

Any idea why this might be happening?

Thanks!!

 

[pmax9999]

Please post the entire Record Selection formula.

My guess is that you have added my code at the end of what you had, without enclosing it in parenthesis to force the correct order of operation. It should look like this:

[COLOR=#5C3566][... your existing code here ...][/color] 
and
(
	{?TerminalParameter} = "ALL"
	or
	(
		{?TerminalParameter} = "Wash"  and
		not({TerminalField} startswith "l")
	)
	or
	(
		{?TerminalParameter} = "Lube" and
		{TerminalField} startswith "l"
	)
)

Also, check the capitalisation to ensure it matches the data exactly (that the data is as you posted, ie all lowercase). Please confirm that the {?TerminalParameter} options are ALL, Wash and Lube (with correct capitalisation).

Pete

 

[Charliy]

Any time you do a report like this it's a good idea to add a formula that shows what the selection was, and place it in the page header.

Something like:
If {?Terminal Parameter} = 'ALL'
THEN "For All Types"
else
If {?Terminal Parameter} = 'Lube'
THEN "For Lube Only"
else
If {?Terminal Parameter} = 'Wash
THEN "For Wash Only"
else
'X'

 

[arodri]

pmax9999, thank you again. That did the trick. I didn't enclose the formula you gave me in parenthesis so the order of operations must have been off. As soon as I added the parenthesis everything started working as I expected.

Thanks so much! I really appreciate it.