Multiple parameters (nested IFs) 10

[kruby]

Hi,

I'm having difficulty with a using multiple parameters in my query selection.
Having a single parameter is no problem but how do I do nest multiple parameters for record retrieval?

The user has several options: They can choose,
1. All Industries or
2. Specific Industry and
3. All Customers or
4. Specific Customer and
5. Date Range

i.e. The user can select "All Industries" or multiple discrete industry values, plus, they can select "All Customers" or multiple discrete customer names. Whatever they choose (All or specific) they still have to select a date range for the report.

What I have so far is:

if {?pIndustry} = "All Industries" then
{industry_list.ind_name} = {industry_list.ind_name} and
{dealer.dl_date} = {?pDateRange}
else
{industry_list.ind_name} = {?pIndustry} and
{dealer.dl_date} = {?pDateRange}
elseif {?pCustomer} = "All Customers" then
{customer_list.cust_name} = {customer_list.cust_name} and
{dealer.dl_date} = {?pDateRange}
else
{customer_list.cust_name} = (?pCustomer) and
{dealer.dl_date} = {?pDateRange}

This isn't working (obviously) but if someone can point me in the right direction of nested IFs with multiple parameters, I would appreciate it.

I'm using CR8.5.

Thanks very much,
Karen

 

[MalcolmW]

Hi Karen,
A few suggestions - use IIF instead of If-Then-Else in this case - with 8.5, this is the way to go.
//record selection formula
{industry_list.ind_name} in
IIF ({?pIndustry} = 'All Industries', Is >= '',{?pIndustry} to {?pIndustry} + 'ZZZZ')
//depending on the value of the parameter, either picks all industries or ones that start with
//the parameter value
and
{customer_list.cust_name} in
IIF ({?pCustomer} = 'All Customers', Is >= '',{?pCustomer} to {?pCustomer} + 'ZZZZ')
//depending on the value of the parameter, either picks all customers or ones that start with
//the parameter value
and {dealer.dl_date} in
{?pStart Date} to {?pEnd Date}

By not using a date range parameter, and by using IIF statements, this record selection formula will now be completely converted to SQL, which will make this report quite quick to run. Otherwise, this report will be typically quite slow.
Malcolm Wynden
Authorized Crystal Engineer

http://www.wynden.net

malcolm@wynden.net

 

[kenhamady]

Malcolm, is your true condition (Is >= '') the same as just saying TRUE? Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[MalcolmW]

Hi Ken,
Is >= ''
The result is to eliminate null values, which may or may not be desirable, depending on the parameter type. I haven't tried it with just TRUE yet, and am working on a 8.0 machine now.
{?pParameter} to {?pParameter} + 'ZZZZ')
This does partial text, case insensitive matches - so if you type in the parameter "ke", it would search for first names "KE" to "KEZZZZ", and of course find "Ken".

The ability of V8.5 to push this type of record selection formula to the WHERE clause in a SQL statement is one of the best kept secrets (because of the enormous performance benefits available), but we going to change that now in Tek-Tips...

Malcolm Wynden
Authorized Crystal Engineer

http://www.wynden.net

malcolm@wynden.net

 

[kenhamady]

Malcolm,

That is the difference. I use TRUE to INCLUDE null values.

Interesting note on the IIF going to SQL. Which environments have you verified this with?

Ken
Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[kruby]

Malcolm,

I appreciate your response. In trying the formula I'm getting the following error message:

"This array must be subscripted. Array ."

It's stopping at the comma in ...
" ,{?pIndustry} to {?pIndustry} + 'ZZZZ')"

I tried putting in square brackets around the condition, but that didn't work.

Any advice?

TIA,
Karen

 

[kruby]

FYI,

I found that if I enclose the conditions in quotes, the error message went away.

The original formula of:

IIF ({?pIndustry} = 'All Industries', Is >= '',{?pIndustry} to {?pIndustry} + 'ZZZZ')

becomes...

IIF ({?pIndustry} = 'All Industries', Is >= '',"{?pIndustry} to {?pIndustry} + 'ZZZZ'&quot

This seems to be working.

Thanks,
Karen

 

[kruby]

Hi,

I spoke too soon, the quotes didn't work. It simply replaced the condition as a string in the SQL statement.

Any solution to the error message...

"This array must be subscripted. For example: Array ."

is appreciated.

Karen

 

[kenhamady]

kruby,

it appears that Malcolm didn't include the field in the formula. I think it should be like this:

IIF ({?pIndustry} = 'All Industries',
{industry_list.ind_name}>= '',
{industry_list.ind_name} in {?pIndustry} to {?pIndustry} + 'ZZZZ')


Malcolm,
I tried the following selection formula in MS ACCESS and didn't get the WHERE to pick up the condition:

IIF ({?pIndustry} = 'All Industries',
{Customer.Customer Name}>= '',
{Customer.Customer Name} in {?pIndustry} to {?pIndustry} + 'ZZZZ')

I guess it doesn't carry into MS Acces SQL. Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[kruby]

Thanks for the idea, Ken, but when I implement the change, I'm still getting the subscript error message.

The error is highlighting the 'in' word in the formula:

//record selection formula
{industry_list.ind_name} in
IIF ({?pIndustry} = 'All Industries',
{industry_list.ind_name}>= '',
{industry_list.ind_name} in {?pIndustry} to {?pIndustry} + 'ZZZZ')

Any more ideas?

Thanks,
Karen

 

[kenhamady]

OK, my brain must still be on vacation.

I missed the first line of Malcolm's formula. I tried it again and it works fine just as he wrote it. It even passes to SQL just as he said. However, this won't work with the
"allow multiple values" setting.


If you need to keep the multiple, as the original post stated, you can try this:


If {?pIndustry} = 'All Industries'
then True
else {Customer.Customer Name} = {?pIndustry}

This will work with a multiple, but it won't pass to SQL. Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[MalcolmW]

Sorry Karen - when you said in your first post that you had a problem with multiple parameters, I interpreted that to simply mean more than one parameter. The solution above only works with multiple parameters and a sort of range value (ie partial string matching), not multiple valued parameters.
There is an alternative for multiple valued parameters that also gets pushed to SQL - I get back to my version 8.5 install tomorrow and will post it then. That is, if no one had beaten me to it (I described Tek-Tips to a non friend as "competetive altruism" - let's just see how competetive it is out there!).
Happy 4th of July to all the Yanks out there...
cheers,
Malcolm Malcolm Wynden
Authorized Crystal Engineer

http://www.wynden.net

malcolm@wynden.net

 

[kruby]

I'm so confused on this end

It seems to me that it would be a very common report request--to have several parameters and a date range on a report.

I have 30 reports, all with a minimum 2 parameters, some with 4 parameters. In Access, these kinds of parameter reports would take me minutes. What's the answer to nested IFs statements in CR8.5?

I find it hard to believe that no one has ever generated a report where, for instance..

Scenario 1
Show the total revenue for selected Customer1 or Customer2 in all industries for Jan 1, 2001 - July 3, 2001?

or

Scenario 2
Show the total revenue for all customers in selected Banking industry or Diversified industry or Consumer Products industry for Jan 1, 2001 - July 3, 2001

or

Scenario 3
Show the total revenue for selected Customer1 or Customer2 or Customer3, for the selected Banking industry or Diversified industry or Technology industry generated during Jan 1, 2001 - July 3, 2001.

or

Scenario 4
Show the total revenue for all Customers in all Industries for Jan 1, 2001 - July 3, 2001.

Do I have to do 4 separate reports?

Thanks for the help.

Karen

 

[kenhamady]

I do this all the time, but I don't consider it nesting, I consder it stacking separate conditions. It would look something like this:


(If {?pIndustry} = 'All Industries'
then True
else {Customer.Industry} = {?pIndustry})

AND

(If {?pCustomer} = 'All Customers'
then True
else {Customer.Customer Name} = {?pCustomer})

AND

{Customer.Date} in {?startDate} to {?endDate}

Make sure that you put parenthesis around the if then conditions, to keep the next rule from being lumped in with the ELSE.

Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[MalcolmW]

Karen,
The terminology is confusing. First of all, logic flow either is stacked, nested, or overlapping. Quite often the only reason for choosing one over the other is making the logic easy for humans to understand. For example, a stacked example of how a nerd tells if he/she is on the beach looking at the ocean:
If IsBlue then True Else False AND
If IsWet then True Else False
The same thing in nested format:
Is IsBlue then
If IsWet then True Else False
Else False
Regarding multiple parameters, this is potentially confusing in meaning because you can have a report with multiple parameters, and you can also have a report that has multiple parameters which can each have multiple values.
A parameter that can have multiple values is a an array of values, not a single value.
The solution that Ken posted above is logically equivalent to the one I posted - it will just be slower with V8.5 because that style of record selection formula is not pushed down to the SQL statement. If speed is not an issue, then just use whichever coding style you feel most comfortable with.


Malcolm Wynden
Authorized Crystal Engineer

http://www.wynden.net

malcolm@wynden.net

 

[kruby]

Thanks Ken and Malcolm for your assistance. The parameters are now working.

Karen

 

[AJTexDev]

Karen,

After reading this thread all the way through, I'm very happy you were able to get it working, and also VERY curious HOW you got it working. Could you post the code that DID work. I also have run across this type of scenario and it would be helpful in the future.

Thanks

AJ

PS Thanks to Ken and Malcom for sticking all the way through.

 

[kruby]

AJ,

Malcom's method in his first response would have worked, if I didn't want multiple discrete values.

When I changed my parameters to only accept discrete values, his method worked. I really liked that you could see the SQL statement in this method. But, I couldn't get past the error message with multiple discrete values.

Ken's response to bracket the IF statements is what I am using now.

for example, one of my reports have 4 parameters:
(industry, dealer name, dealer role, and a date range)
so my record selection formula is:

(If {?pIndustry} = 'All Industries'
then True
else {industry_list.ind_name} = {?pIndustry})

AND

{dealer_list.dl_name} = {?pDealer}

AND

(if {?pDealerRole}= "Lead Underwriter" then
{dealer_invld.position} = 1
else
{dealer_invld.position} = {dealer_invld.position})

AND
{dealer_invld.dl_date} = {?pDateRange}

I've kept my date range parameter because I think it is easier for the user to understand and enter a date range(performance isn't really an issue.)

Even though I can't see the parameters working through the SQL statement, I'm verifying the results through my Access queries. This is now working for me.

Thanks very much,
Karen

 

[synapsevampire]

I thought Malcolm had a solution for multiple values he was going to post?

"There is an alternative for multiple valued parameters that also gets pushed to SQL - I get back to my version 8.5 install tomorrow and will post it then."

If so, it will make life MUCH simpler, I can't get anything to pass once an iif or if is used, and performance is everything on this contract...

 

[kenhamady]

I am not sure if this will help, but my last newsletter had a note that shows an interesting behavior in relation to if-then logic and SQL statements.

http://www.kenhamady.com/news0204.html

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com