If you are creating a SSRS report in Visual Studio 2008 and would like a parameter to display multiple values including NULL values, you will get a message saying, “A multi-value parameter cannot include null values”.

clip_image002[6]

However, there is a 2 step method to work around this.

1)       In the Dataset that provides values for the Parameter when creating the query add an entry to create a (NULL) item.

clip_image004[6]

SAMPLE CODE

SELECT DISTINCT [AE Fiscal Year]

FROM         MSP_EpmProject_UserView

WHERE     ([AE Fiscal Year] IS NOT NULL)

UNION

SELECT     ‘(NULL)’ AS Expr1

ORDER BY [AE Fiscal Year]

This provides the artificial (NULL) in the parameter drop down menu.

Set the Parameter Properties as shown below:

clip_image006[6]

 

2)      The second step is to modify the main dataset so all actual NULL values in the desired field are replaced by the artificial (NULL) in the query results.

clip_image008[6]

SAMPLE CODE

SELECT DISTINCT ProjectName, ISNULL([AE Fiscal Year], ‘(NULL)’) AS [AE Fiscal Year]

FROM     &