IF Then Else with a Parameter 2

[rwn]

I have a Parameter that will be used as a selection for Customers. My objective for the Parameter ("S") that if a Customer ID is enter only that customer is shown; for the Parameter ("E") that Customer ID is excluded and for the Parameter ("All") all customers IDs are shown.


if {?Customer Selection} = "S" then {Customer.Customer} = {?Customer Selection}
else if {?Customer Selection} = "E" then {Customer.Customer} <> {?Customer Selection}
else if {?Customer Selection} = "ALL" then {Customer.Customer} = {Customer.Customer}

 

[briangriffin]

What version are you using? This would be much easier in CR2008.

 

[rwn]

I have both versions, and was/is designing in V11.

 

[rwn]

ps - how would one do this cr2008?

 

[briangriffin]

I think you'll need two parameters - one to collect customer ID's, and another to tell the report what to do with them. The first one would be Customer ID and the second would be Include/Exclude.

The basic logic would be:

if {?Include/Exclude} = "Include" then db.custID = {?CustomerID}
else
if {?Include/Exclude} = "Exclude" then db.custID <> {?CustomerID}



The problem is when you want all customers. In CR2008 we have optional parameters - I don't have a copy in front of me, so I don't recall the exact syntax, but it would be something like:


if (syntax for when {?CustomerID} is left blank) then true
else
if (?Include/Exclude} = "Include" then db.custID = {?CustomerID?
else
if (?Include/Exclude} = "Exclude" then db.custID <> {?CustomerID?


If you have to use R2, then I would make the Customer ID parameter a string and set "All Customers" as the default value. The selection criteria would be:


if {?CustomerID} = "All Customers" then true
else
if (?Include/Exclude} = "Include" then totext(db.custID,"#",0) = {?CustomerID}
else
if (?Include/Exclude} = "Exclude" then totext(db.custID,"#",0) <> {?CustomerID?}


It's not quite as good, since your users would have to know to delete the "All Customers" default value before entering the customer ID's that they want to include or exclude. You could also add a third value "All Customers" to the Include/Exclude parameter to make it more clear to the user, even though it wouldn't actually do anything.

 

[lbass]

(
(
{?Customer Selection} = "S" and
{Customer.Customer} = {?Customer Selection}
) or
(
{?Customer Selection} = "E" and
{Customer.Customer} <> {?Customer Selection}
) or
{?Customer Selection} = "ALL"
)

-LB

 

[rwn]

I have it in my Select Expert, but do not follow the logic of this for example.....({?CustomerSelection}= "Specific" and {Customer.Customer} = {?CustomerSelection}) ... How does it know that "Specific" means only to show what Customer.Customer id was enter to be shown?...

{Job.Status} in ["Active", "Pending"] and
{Delivery.Remaining_Quantity} > 0 and
({?CustomerSelection}= "Specific" and {Customer.Customer} = {?CustomerSelection}) or
({?CustomerSelection} = "Exclude" and {Customer.Customer} <> {?CustomerSelection}) or
{?CustomerSelection} = "ALL

 

[lbass]

You didn't implement this correctly. The parens are not optional. Also, I didn't realize you actually were using the same parameter throughout. You need two parms, one for Customer ID or Name. It should look like this:

{Job.Status} in ["Active", "Pending"] and
{Delivery.Remaining_Quantity} > 0 and
[red]([/red]
(
{?CustomerSelection}= "Specific" and
{Customer.Customer} = {?CustomerID}
) or
(
{?CustomerSelection} = "Exclude" and
{Customer.Customer} <> {?CustomerID}
) or
{?CustomerSelection} = "ALL"
[red])[/red]

By using the "and" in each clause, you are requiring both clauses to be true, e.g., if you have chosen "Exclude", only then will the second clause "{Customer.Customer} <> {?CustomerID}" be applied. If you choose "ALL" the report won't select based on {?CustomerID}. You would probably want to instruct the user to select "0000" or some fake ID if they want to show all records, since a parameter must have some value, but you don't have to work that into the formula--it just would be less confusing to the user.

-LB

 

[rwn]

Thanks, that is much clearer and better. The strange thing is, if I select and run either Specific are Exclude the {Job.Status} in ["Active", "Pending"] is ignored.

 

[rwn]

I just moved these two items to Select Expert ({Job.Status} in ["Active", "Pending"] and
{Delivery.Remaining_Quantity} > 0 and ) and it works fine. I have noticed before, that with to many Parameters Crystal reports is not that realiable.

 

[lbass]

I don't know what you mean. The formula was designed for record selection. You can get there two ways--through the select expert which I never use, and by going to report->selection formula->record. If you mean you used the Section Expert and suppressed unwanted records, you should not be doing that. Instead it sounds like you still don't have the parentheses correct. Please post your current record selection formula so we can troubleshoot it.

-LB

 

[Turkbear]

Hi,
Follow LB's advice..That selection formula would not ignore any criteria that uses an AND opersator.

CR can handle ( and for me has handled many times) multiple parameters - I have used over 20 in some complex reports with no issues once I got the ANDs and ORs sorted out.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[rwn]

My exact formula in Select Expert is:
{Job.Status} in ["Active", "Pending"] and
{Delivery.Remaining_Quantity} > 0 and
(
(
{?CustomerSelection}= "Specific" and
{Customer.Customer} = {?CustomerID}
) or
(
{?CustomerSelection} = "Exclude" and
{Customer.Customer} <> {?CustomerID}
) or
{?CustomerSelection} = "ALL"


If I take these two items and use them in Section Expert to suppress if the condition exists all is fine.
not {Job.Status} in ["Active", "Pending"] and
{Delivery.Remaining_Quantity} = 0.

 

[lbass]

That couldn't be your formula, as it would error out with no ending paren. Please go to report->selection formula->record and copy and paste the result here.

-LB

 

[rwn]

When it runs, it does not acknowledge these two items. {Delivery.Remaining_Quantity} > 0 or {Job.Status} in ["Active", "Pending"].

Here it is copied and pasted from Report/Selection Formula / Record.
-------------------------------
{Job.Status} in ["Active", "Pending"] and
{Delivery.Remaining_Quantity} > 0 and

{?CustomerSelection}= "Specific" and
{Customer.Customer} = {?CustomerID}
or

{?CustomerSelection} = "Exclude" and
{Customer.Customer} <> {?CustomerID}
or
{?CustomerSelection} = "ALL" and
{Job.Assembly_Level} = 0
----------------------------------------

 

[Turkbear]

Hi,
You are missing the point about using parens to isolate the criteria:

{Job.Status} in ["Active", "Pending"] and{Delivery.Remaining_Quantity} > 0 and

[COLOR=red][B]( //REQUIRED FOR THIS FORMULA[/B][/color]
[COLOR=red]([/color] {?CustomerSelection}= "Specific" and{Customer.Customer} = {?CustomerID} [COLOR=red])[/color]
or
[COLOR=red]([/color]{?CustomerSelection} = "Exclude" and{Customer.Customer} <> {?CustomerID} [COLOR=red])[/color]
or
[COLOR=red]([/color]{?{?CustomerSelection} = "ALL" 
and{Job.Assembly_Level} = 0[COLOR=red])[/color]
[COLOR=red][B]) //REQUIRED FOR THIS FORMULA[/B][/color]

Without those the OR tests are not handled correctly - that is, in ADDITION to the 2 primary criteria :
{Job.Status} in ["Active", "Pending"] and{Delivery.Remaining_Quantity} > 0

Copy and paste LB's formula into your record selection criteria , it should give you what you want.


.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[rwn]

Thanks to both. You are correct, I didn't understand about the Para's being critical.