CODEWITHSUNDEEP
reporting-services
UpwardD
UpwardD

Reputation: 767

Blank Parameter to Null Parameter in SSRS

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.

Upvotes: 6

Views: 44452

Answers (3)

Greg Jacobs
Greg Jacobs

Reputation: 1

Set the Parameter to Allow Blank = Yes and the Default Value = "". In the code make allowances for the blank parameter.

Upvotes: 0

Pablo Contreras
Pablo Contreras

Reputation: 588

if you're considering passing a null parameter from URL, let's say some sort of CLI, you must include a parameter:isNull=True in the parameter section of the URL. and allways remenber to check the "allow null value" in report editor, parameter properties.

let's say we have a report, of sports and stadiums, and we have a null sport... it would be something like this>

http://<server/report>&rs:Command=Render&rc:Parameters=false&rc:toolbar=false&sport:isNull=True&stadium=CampNou

Upvotes: 2

Ian Preston
Ian Preston

Reputation: 39586

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.

Upvotes: 15

Related Questions