CODEWITHSUNDEEP
sqlstored-proceduresreporting-servicesparameters
Jt2ouan
Jt2ouan

Reputation: 1962

Allow empty string for SSRS multivalue parameter

I have a SSRS report that passes a few multi-value report parameters to a stored procedure in order to build the report. I'm having trouble returning the values that are empty strings when that option is select. As you can see the drop down has an empty string option, there are definitely rows that should be returned with this empty field chosen. The parameter is set to allow for blank values. The parameter has the join on comma in the parameter options for the dataset. I also have a separate table valued function that is called when I'm trying to search for the specific parameters in the query.

Here's where I would select by the report parameter:

where car.Cars in (SELECT  Val from [dbo].[fn_String](@localCars,',',1))

Heres the function tha t is called to split the SSRS parameter values to separate string values:

ALTER FUNCTION [dbo].[fn_String] (
        @String VARCHAR(max), 
@Delimeter char(1),   
@TrimSpace bit )    
RETURNS @Table TABLE ( [Val] VARCHAR(500) )
AS
BEGIN
DECLARE @Val    VARCHAR(500)
WHILE LEN(@String) > 0
BEGIN
    SET @Val    = LEFT(@String,
         ISNULL(NULLIF(CHARINDEX(@Delimeter, @String) - 1, -1),
         LEN(@String)))
    SET @String = SUBSTRING(@String,
         ISNULL(NULLIF(CHARINDEX(@Delimeter, @String), 0),
         LEN(@String)) + 1, LEN(@String))
IF @TrimSpace = 1 Set @Val = LTRIM(RTRIM(@Val))
INSERT INTO @Table ( [Val] )
    VALUES ( @Val )
 END
 RETURN
 END

Upvotes: 1

Views: 1431

Answers (1)

user933467
user933467

Reputation:

I had the same trouble a few weeks ago. Are you sure you have the JOIN in each of the parameters your passing in. That was my error I forget one or two. JOIN(parameter!paramName.value,",")

Upvotes: 1

Related Questions