Null and Multi-value parameter possible?
Does anyone know how to allow null values in a multi-value parameter?
The parameter is passed to a stored procedure which handles the null values.
The parameter represents a hierarchy in which none, any or all values can be selected. However, the report parameters cannot be setup to be both Multi-value and Allow Null. When I try to submit the report it requires a value to be selected.
I have also tried to add a Null value to the parameter list:
SELECT NULL AS Level1ID, 'None' AS Level1Name
UNION ALL
...
But this doesn't show up in the parameter list, is the Multi-value filtering off the null?
How can I configure both multi-value and null to be allowed?
[691 byte] By [
CLT] at [2007-12-20]
According to the SQL standards (SQL-92, SQL-99) a query like the following:
Select Column from Table1 where Name in ('A', 'B', NULL)
will only return rows that have Name = 'A' or Name = 'B'. It will not return rows with the Name being NULL. This is true SQL Server, Oracle, etc.
In order to get the desired behavior, the query would need to look like this:
Select Column from Table1 where Name in ('A', 'B') OR Name IS NULL
Given this, I don't think supporting NULL directly in RS 2005 multi value parameters would actually help you because you would need write the dataset query differently anyway to compare with NULLs.
Depending on what your query is actually doing, you could add a second boolean report parameter that represents the NULL, or you could define a specific value that should represent the NULL value in case you don't want to change the query.
Note: automatically detecting this case and having RS rewrite your query commandtext is unfortunately non-trivial because the "left part" of the IN clause (i.e. Name column in the example above) could be e.g. a complex sub select or other complex SQL statements which makes it difficult to determine what is actually the "left part" of IN clause in your query.
-- Robert
