Reports in SSRS with Multi Value Parameters and NULL Values

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”.

 

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.

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:

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.

SAMPLE CODE

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

FROM MSP_EpmProject_UserView

As with using any parameter in the Dataset Properties the Filter conditions must be set.

The final result is a Parameter Selection drop down that will include both Actual and (NULL) values. This can be especially helpful when filtering with multiple parameters.

Reports in SSRS with Multi Value Parameters and NULL Values