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}
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}