ssrs nullable parameter is demanding a value

Question

Building a report in VS2013. I have a parameter that needs to be able to be null. I can successfully run the report in the query builder, with a null parameter value. But if I run the report in preview or deployed, there is no NULL check box next to the parameter select, so it demands a value.

My stored proc has an optional parameter and it is coded to accept null in the where clause:

spAccomplishedSummaryGet @BurnType char = NULL

 ...

AND ((r.BurnType = @BurnType) OR @BurnType IS NULL)

The burn type parameter gets its values from a query. I have selected "allow null"

I'm sure I can add a 'null' value to the burntype list, but before I do that I wanted to see if there is a better way.

Answer 1

You need to allow null values against that parameter in SSRS report, and optionally assign null to that parameters as well. Please look at the following steps.

1. Navigate to "Report Data" option in your SSRS report
2. under "Parameters" you will see all the parameters that your stored procedure will be demanding, open the parameter properties of that particular parameter which you wish to pass null value (right click on parameter and select option "Parameter Properties").
3. In the new window under that "General" tab, check the option "Allow null value" Click here in case of ambiguity

Answer 2

I have had similar issues especially when setting up subscriptions where it requires a value for a parameter that should be able to be left NULL. Rather than using NULL to ignore the parameter, consider using an "All" value in your list of available values. You can easily change the where clause to account for that:

AND (r.BurnType = @BurnType OR @BurnType = 'All')

I have found that this is actually more user-friendly than having a checkbox to essentially disable a parameter.