Post reply

Not allowing Null in Parameter

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

  • October 12, 2010 at 3:32 am

    #1235371

    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.

  • October 12, 2010 at 8:07 am

    #1235549

    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:

  • October 12, 2010 at 8:33 am

    #1235579

    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

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

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