Not allowing Null in Parameter

Question

Not allowing Null in Parameter

HakunaMatata

SSCrazy

Points: 2610
More actions
September 29, 2010 at 9:13 pm

#241031

Hi,
I have created one parameter in a report which takes values from a dataset.
I have marked option to allow NULL values but when values are populating in the parameter when report runs it doesn't shows option to select it as NULL. I have not selected Allow Multiple or anything, only Allow NULL is selected.
I have other parameters in the report which have same functionality but they do show option to select NULL.
Any idea on this?
:rolleyes:

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Cody Konior SSCarpal Tunnel Points: 4827 More actions · Answer 1

Sacheen (9/29/2010)
Hi,
I have created one parameter in a report which takes values from a dataset.
I have marked option to allow NULL values but when values are populating in the parameter when report runs it doesn't shows option to select it as NULL. I have not selected Allow Multiple or anything, only Allow NULL is selected.

When you preview/run the report you should get a tick-box that lets you set the parameter to null, even if it's from a query.

I have other parameters in the report which have same functionality but they do show option to select NULL.

The only thing I can think of is those aren't ones that have Allow NULL, but instead allow for it in the query that populates the parameter itself. For example:

SELECT AllowedStuff FROM SomeTables

UNION

SELECT '(Null)'

And then whatever uses the parameter checks for it like so:

SELECT ...

WHERE TestedStuff IN (@AllowedStuff) OR (TestedStuff IS NULL AND '(Null)' IN (@AllowedStuff))

Any idea on this?

Maybe some screenshots of the parameter settings, the parameter source/default value settings, and the parameter input area at the top of the report when you go to preview it in BIDS would help.

HakunaMatata SSCrazy Points: 2610 More actions · Answer 2

Hey thanx.. I worked out this way only by using Union with NULL....

But I am still not able to find why its not showing me NULL in dropdown without it.. :unsure:

:rolleyes:

getoffmyfoot Hall of Fame Points: 3338 More actions · Answer 3

Yea, I dont think the "Allow Null" tick box shows up when you pull the parameters from a dataset, if I remember right. Anyhew, Yes try wording your dataset query like this

SELECT <parameters> from <table>

UNION

SELECT 'no_value'

Then in your dataset, you could put a NULLIF(@param, 'no_value') in the where clause.

See if that works