Blank Parameter to Null Parameter in SSRS

reporting-services

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.

Best Answer

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.

Related Solutions

SSRS null parameter to return values

You would need to add a condition to your Dataset query to handle the NULL parameter. For example:

WHERE @Parameter is NULL or ColumnValue = @Parameter

Sql-server – SQL variable to hold list of integers

Table variable

declare @listOfIDs table (id int);
insert @listOfIDs(id) values(1),(2),(3);    

select *
from TabA
where TabA.ID in (select id from @listOfIDs)

declare @listOfIDs varchar(1000);
SET @listOfIDs = ',1,2,3,'; --in this solution need put coma on begin and end

select *
from TabA
where charindex(',' + CAST(TabA.ID as nvarchar(20)) + ',', @listOfIDs) > 0

Best Answer

Related Solutions

SSRS null parameter to return values

Sql-server – SQL variable to hold list of integers

Related Topic