Free selection parameters: Easier way

[APB1981]

Hey guys:

I want to create 4 parameters in my report - if the user leaves them blank - then all data will display - but if the parameters are populated then that selection will be filtered.

I have done quite bit of code to tackle all the diiferent scenarios - but I was wondering if there is a much easier way. In VB all you need to do is state whether or not the param is null. I want to create a Report with 10 paramaters next and with this code the report may become to slow to run:

By the way - feel free to use this coding it works pretty nicely.

Example of code:

Note: {@Param1blank} = {?Paramater1} = "" and so on.

.....................................


//4 Filter Parameter Settings


//A or 1 Param 1: Paramater1 - {@Param1blank} - {ST_Table1.field1} = {?Paramater1}

//B or 2 Param 2: Paramater2 - {@Param2blank} - {ST_Table2.field2} = {?Parameter2}

//C or 3 Param 3: Paramater3 - {@Param3blank} - {ST_Table3.field3} = {?Parameter3}

//D or 4 Param 4: Paramater4 - {@Param4blank} - {ST_Table4.field4} = {?Parameter4}



//.............................................................................................
//.............................................................................................

// a={@Param1blank} b={@Param2blank} c={@Param3blank} d={@Param4blank}
//0 set: User will select 0 (None) Paramaters


IF {@Param1blank} and {@Param2blank} and {@Param3blank} and {@Param4blank}

THEN True

//.............................................................................................

//1 set: User will select 4 Paramaters

ELSE

//abcd


IF NOT {@Param1blank} and NOT {@Param2blank} and NOT {@Param3blank} and NOT {@Param4blank}

THEN {ST_Table1.field1} = {?Paramater1} and
{ST_Table2.field2} = {?Parameter2} and
{ST_Table3.field3} = {?Parameter3} and
{ST_Table4.field4} = {?Parameter4}

//.............................................................................................

// a={@Param1blank} b={@Param2blank} c={@Param3blank} d={@Param4blank}
// 4 Sets: User will select 3 Paramaters

ELSE

IF {@Param1blank} and NOT {@Param2blank} and NOT {@Param3blank} and NOT {@Param4blank}

//bcd

THEN {ST_Table2.field2} = {?Parameter2} and
{ST_Table3.field3} = {?Parameter3} and
{ST_Table4.field4} = {?Parameter4}

ELSE

IF NOT {@Param1blank} and {@Param2blank} and NOT {@Param3blank} and NOT {@Param4blank}

//acd

THEN {ST_Table1.field1} = {?Paramater1} and
{ST_Table3.field3} = {?Parameter3} and
{ST_Table4.field4} = {?Parameter4}

ELSE

IF NOT {@Param1blank} and NOT {@Param2blank} and {@Param3blank} and NOT {@Param4blank}

//abd

THEN {ST_Table1.field1} = {?Paramater1} and
{ST_Table2.field2} = {?Parameter2} and
{ST_Table4.field4} = {?Parameter4}

ELSE

IF NOT {@Param1blank} and NOT {@Param2blank} and NOT {@Param3blank} and {@Param4blank}

//abc

THEN {ST_Table1.field1} = {?Paramater1} and
{ST_Table2.field2} = {?Parameter2} and
{ST_Table3.field3} = {?Parameter3}


//.............................................................................................

// a={@Param1blank} b={@Param2blank} c={@Param3blank} d={@Param4blank}
// 6 Sets: User will select 2 Paramaters


ELSE

//bc

IF {@Param1blank} and NOT {@Param2blank} and NOT {@Param3blank} and {@Param4blank}

THEN {ST_Table2.field2} = {?Parameter2} and
{ST_Table3.field3} = {?Parameter3}


ELSE

//bd

IF {@Param1blank} and NOT {@Param2blank} and {@Param3blank} and NOT {@Param4blank}

THEN {ST_Table2.field2} = {?Parameter2} and
{ST_Table4.field4} = {?Parameter4}

ELSE

//cd

IF {@Param1blank} and {@Param2blank} and NOT {@Param3blank} and NOT {@Param4blank}

THEN {ST_Table3.field3} = {?Parameter3} and
{ST_Table4.field4} = {?Parameter4}

ELSE

//ac

IF NOT {@Param1blank} and {@Param2blank} and NOT {@Param3blank} and {@Param4blank}

THEN {ST_Table1.field1} = {?Paramater1} and
{ST_Table3.field3} = {?Parameter3}

ELSE

//ad

IF NOT {@Param1blank} and {@Param2blank} and {@Param3blank} and NOT {@Param4blank}

THEN {ST_Table1.field1} = {?Paramater1} and
{ST_Table4.field4} = {?Parameter4}

ELSE

//ab

IF NOT {@Param1blank} and NOT {@Param2blank} and {@Param3blank} and {@Param4blank}

THEN {ST_Table1.field1} = {?Paramater1} and
{ST_Table2.field2} = {?Parameter2}



//.............................................................................................

// a={@Param1blank} b={@Param2blank} c={@Param3blank} d={@Param4blank}
// 4 sets: User will select 1 Paramater


ELSE

//a


IF NOT {@Param1blank} and {@Param2blank} and {@Param3blank} and {@Param4blank}

THEN {ST_Table1.field1} = {?Paramater1}

ELSE

//b


IF {@Param1blank} and NOT {@Param2blank} and {@Param3blank} and {@Param4blank}

THEN {ST_Table2.field2} = {?Parameter2}

ELSE

//c


IF {@Param1blank} and {@Param2blank} and NOT {@Param3blank} and {@Param4blank}

THEN {ST_Table3.field3} = {?Parameter3}


ELSE

//d


IF {@Param1blank} and {@Param2blank} and {@Param3blank} and NOT {@Param4blank}

THEN {ST_Table4.field4} = {?Parameter4}

 

[synapsevampire]

Whole lotta code there...

In Crystal we generally create a default value, and then code the Report->Edit Selection Formula as follows:

(
if {?MyParm1} <> "Default value" then
{table.field} = {?MyParm1}
else
if {?MyParm1} = "Default value" then
true
)

and

(
if {?MyParm2} <> "Default value" then
{table.field} = {?MyParm2}
else
if {?MyParm2} = "Default value" then
true
)
...etc...

Now you can just pass the parameter values to the report and let Crystal handle it all.

If you elect to do it all in VB, this should be your model in VB as well, as you are taking the long way home.

-k

 

[APB1981]

Thanks synapsevampire,

This does seem more logical.

As long as it deals with all scenarios. Example: a user selects 2 parameters, and 2 remain blank. I thought you needed to express all possible scenarios in one single "If then else" statement. I'll give it a go.

Keep up the great work.

APB