INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

How to set Null option in string parameter

How to set Null option in string parameter

(OP)
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.

RE: How to set Null option in string parameter

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:

CODE

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


Hope this helps

Cheers
Pete

RE: How to set Null option in string parameter

(OP)
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.

CODE -->

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




Thanks.

RE: How to set Null option in string parameter

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
www.decisionfirst.com

RE: How to set Null option in string parameter

(OP)
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.

RE: How to set Null option in string parameter

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):

CODE

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

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

Cheers
Pete

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close