Selection formula

[mcaizpp2]

Hi Im using crystal reports version IX.
I'm making a report with 7 parameters. 4 of the parameters are drop down list (with the * for all) and the other 3 are free text boxes.

All parameters are optional except for the period parameter(2004, 2005 etc).

I'm having reall difficulty with the select expert I need a formula where it only filters if a value has been entered in the parameter field. To this end I created a formula which checked if the text box parameters had values or not - the formula is below -

(SelectionManager) -
if (?ManagerName} = "" THEN "*" ELSE (?ManagerName}

The select expert would then filter the view using this formula -

View.Manager like {SelectionManager}

If the select expert filtered on "*" (derived from the forumla) it would return all records except for fields with a null value.

To allow for null values i changed my select formula to -

View.Manager like {SelectionManager} or View.Manager is null.

My problem now is that if you enter a manager name into the parameter it filters on the manager name and also brings back "null" values as well.

Does anyone have a better selection formula for this - what i done cant be right can it?

My last question is the 7th optional text box is for a number field "SPU" I cant use the above formula on a number field because you cant use an "*" on a number to select all.

What kind of formula would you use to filter if there was a value in the "SPU" or not to filter if no value was given for "SPU"?


I know this post is quite long but I keep trying new methods to solve this problem and each one leads to another problem - seriously frustrating!

Thanks for any assisitance it will be seriously appreciated!

 

[lbass]

For the number parameter, add a zero as the default and then use a formula like:
(
if {?SPU} <> 0 then
{table.spu} = {?SPU} else
if {?SPU} = 0 then true
) and //etc.

For the string parameters, I think you should use "All" instead of "*". The selection formula would then look like:

(
if not isnull({view.manager}) and
{?ManagerName} <> "All" then
{view.manager} = {?ManagerName} else
if {?ManagerName} = "All"
then true
)

-LB

 

[mcaizpp2]

Thanks so much just ried out what you suggested on my report and it worked perfectly - your a life saver I was tearing my hair out with this problem!