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     &

Related

• There was an error while enumerating services on local machine
• SQL Reporting Services Multi-value parameters: A How to
• SQL Reporting Services 2008 R2: Using URLs to call reports with single and Multi value parameters (HTML and Report Viewer applicable)
• Newly added enterprise custom field values not visible in Project Professional 2010
• Reporting Services Error: The configuration parameter SharePointIntegrated is set to True but Share Point Object Model cannot be loaded.