Blank Parameter to Null Parameter in SSRS

Question

I have a drilldown report whose parameters are: parent report - @valid Nvarchar(20) = '' and in child report - @valid Nvarchar(20) = Null.

Both reports run very well but I have an issue after mapping up this parameters in the Text Box Properties > Action > Go to Report. When the report is run I get the error,

"the value provided for the report parameter is not valid"

The query for this report is from a stored Procedure. In the child report, Parameter Properties, I have ticked the "allow Null values" text box, Set available parameter to come from a query and specify a default value to come from a query. What have I done wrong to allow for the error returned? Your help is appreciated.

Thank you.

I'm not 100% sure that this is what you're after, but you can pass an expression like =IIf(Parameters!valid.Value = "", Nothing, Parameters!valid.Value) to the child report parameter, i.e. NULLing out empty strings. Is that what you meant? — Ian Preston
Oh, cool! It was just a guess, so I just added a comment, but since it seems on the mark I will add as a proper answer in a few seconds... Thanks! — Ian Preston

Ian Preston Ian Preston · Accepted Answer · 2014-01-29T16:38:42

If you're second report is expecting a NULL values, (i.e. Nothing in SSRS), you can pass an expression-based parameter to the child report based on the parent parameter, making sure that if it's an empty string at the parent level, you can explicitly set this to Nothing:

=IIf(Parameters!valid.Value = "", Nothing, Parameters!valid.Value)

This way the empty string will never get passed and your child report; only the NULL value it expects.

Blank Parameter to Null Parameter in SSRS

3 Answers