Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Free selection parameters: Easier way

Status
Not open for further replies.

APB1981

Technical User
Jul 10, 2005
83
NO
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}


 
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top