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

 

[synapsevampire]

I forgot to post what I'd learned:

I had poor results using IIF, so I went with a straight IF successfully.

To pass the SQL on a multiple parameter I use the following in the record selection criteria:

-----

(if {?Classification}[1] <> 'All'
and {?Classification}[1] <> ''
then
({MyTable.CLASSIFICATION} in {?Classification})
else
if {?Classification}[1] = 'All'
or {?Classification}[1] = ''
then
true)

and...

-----

Noteworthy:
- The if clause is encapsulated in paretheses
- If your clients can switch the type of viewer they use (as in Crystal Enterprise), the Non-Active-X viewers will NOT use the default value, the Active-X does, hence the check for 'All' (the default which is pre-populated in the Active-X) and '' (empty, the check for non Active-X viewer parameters)

A simple way to display what was chosen is to use the JOIN function:

// Demonstrates the choices made for the Classification
stringvar strLabel := 'Classification';
stringvar array strSelections := {?Classification};
if strSelections[1] <> 'All' and
strSelections[1] <> ''
then
strLabel+&quot;: &quot; + join(strSelections,&quot;, &quot + chr(13)

I use varaiables here because I have lots of parameters and it adds reusability. Do NOT use variables in any formula which creates data to be used in the record selection criteria, or directly in the record selection criteria, it appears to break the pass through.

<yearning for the contracts where I use SP's>

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com