How to set Null option in string parameter

[kamkaro]

Hi All,

I am trying to create one User parameter (Data type is string) where if user does not enter any value, crystal report should consider NULL value.

When i am creating parameter from Command, does not allow to change Optional prompt (False to True) in Edit parameter though.

Could someone, please explain any workaround how to make String parameter where "Null" option there. I know if my source is Store procedure, we have 'Set to Null" option available.


Really Appreciate that.

Thanks.

 

[pmax9999]

If the parameter is a string, it will accept nothing being entered, ie an empty string.

Then in the Command, include a Where clause to select null field when the parameter is an empty string (ie, ''). Something like:

Where	(
		('{?Parameter}' = '' and Table.Your_Field is null) or
		'{?Parameter}' = Table.Your_Field
	)

Hope this helps

Cheers
Pete

 

[kamkaro]

Thanks Pete for your reply.

After modified my code according to your suggestion, When i am leaving my parameter empty "getting all Null values" (which is fine) but when I am entering the value (LEAP) "getting all values (Null + LEAP).

My requirement is that if user leave Alert parameter empty, bring all Null records and when user enter value (LEAP) so just bring (LEAP) records which match the parameter value.

Please let me know how do I get this task? Appreciate that.

where ( ('{?Alert}' = '' or x.alert_type_cd is null)  or   '{?Alert}' = x.alert_type_cd )

Thanks.

 

[hilfy]

Try this:

where ( ('{?Alert}' = '' AND x.alert_type_cd is null) or '{?Alert}' = x.alert_type_cd )

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[kamkaro]

Dell,
According to your suggestion, When i am leaving my parameter empty, getting "Zero" record (which is wrong) but when I am entering the parameter value (LEAP) "getting all LEAP records (fine now).

As I said earlier, I would like to display records base of parameter's input.

Parameter Value(LEAP) = show all LEAP records
Parameter empty = show all Null records (NO LEAP)

Please give me some suggestions.

Thanks.

 

[pmax9999]

You did not use the code as I provided it. As Dell identified, the first OR should be an AND.

What database are you using? I tested this on SQL Server and it worked. While not tested on Oracle, I have used this approach before and it does work.

Perhaps we have just not understood what you are trying to do. When the parameter is blank it will return records where the x.alert_type_cd is null. When the parameter has something entered it will return records where the x.alert_type_cd matches the parameter.

Your Where clause should look like this (note, the layout is not important, but I do it this way so that the bracket grouping is obvious):

Where	(
		('{?Alert}' = '' [b][u]and[/u][/b] x.alert_type_cd is null) [b][u]or[/u][/b]   
		'{?Alert}' = x.alert_type_cd 
	)

It may help if you post the entire SQL Query used in your Command.

Cheers
Pete