SSRS Records Not Showing Up When (Select All) Is Used But Is When Selecting A Particular Value

Question

I have a report that is has a multiple value parameter. It has available values (1, 2, 3, 4, 5, and blank). The blank value isn't NULL it is just an empty cell.

For testing purposes, there should only be one record showing up. The record has a blank value in the cell that I am using to filter on in my stored procedure.

In the parameter drop down, I see all of the options. If I choose the (Select All) option, I don't get the record I am wanting. However, if I choose just the blank value, I get the record I am wanting. I updated the record manually to "2". I choose (Select All) and I get the record. I choose only the "2" value and I get the record. If I remove the value again to make the value blank, I only get the record if I only choose the "Blank" option in the drop down.

I make a text box to output the value of the parameter and it is showing blank when it is selected either by itself or with other values.

What am I missing in either my stored procedure or in my report? Thanks in advance.

Booking

Answer 1

When you have a multiple select parameter, SSRS should send a comma separated list of values to your stored procedure. You are responsible for splitting these back out into something that you can join on.

In the report itself, you don't get the list of values in a nicely wrapped up string for displaying. I've had to use code to iterate over the values in the parameter.

I tested what SSRS is doing when you have a blank available value. I created a test report that had the six available options and then a stored procedure to force the selected values to be output:

CREATE PROCEDURE dbo.Test_RPT
(
 @TestMultiSelect varchar(1000)
)
AS
SELECT @TestMultiSelect RVAL

In the report, I just had a single textbox that displayed this RVAL field.

If I put the blank option at the start, the output was 1,2,3,4,5. If the blank option was at any other location, it was included: 1,2,3,,4,5, 1,2,3,4,5,.

Tangram

I have a function that does this. That is how I am able to use the other values. – Booking

Is this function in SQL? Or, are you talking about a function that you added to the report? – Tangram

Custom function in SQL. I have used it in many reports before to assist with multiple value parameters. – Booking

Have you tested to see what SSRS is sending to the stored procedure for the blank? Try writing the parameter value into a db table so that you can then see what is really being sent in. Blank might be a string with no length. As in, the value passed might be ',1,2,3,4,5'. Is your table expecting a space character instead? – Tangram

The string with no length is what is happening and is what I am hoping to find with the report. The record comes up if I pick the blank value only but not if I pick any other combination of that blank value and another value. – Booking

I put together a quick report that had the same multi-select that you listed. If you put the blank at the beginning of your list of available values (either directly or via a dataset), it appears SSRS is omitting the blank. I will edit my answer to show the results. – Tangram

In my situation my record would only show if it was in the middle of the entries. I created text box to output the parameter value, similar to what you did, and I was getting the blank no matter where I would place it in the values. However, if the blank value was at the beginning or the end, the record wouldn't come up. My guess is that it has something to do with my function that I am passing the parameter into to parse the values. Thanks for all of your help. – Booking

Answer 2

If you want to pass multiple values to Sql IN clause within a Stored Procedure from SSRS report dataset, slight changes needed in the Stored Procedure:

See the Sample WHERE clause in a SP

...  
AND Courses.Type_Code IN (@Type_Code)

Add a SplitString() function to split the passed parameters based on a delimiter.

The body of the SplitString() will look like:

CREATE FUNCTION [dbo].[SplitString]
(
    @pString VARCHAR(8000), 
    @pDelimiter CHAR(1)
)
RETURNS @Values TABLE (Value VARCHAR(50))
AS
BEGIN

        DECLARE @xml as XML;

        SET @xml = cast(('<X>'+replace(@pString, @pDelimiter, '</X><X>')+'</X>') as xml);

        INSERT @Values
        SELECT N.value('.', 'varchar(50)') as value 
        FROM @xml.nodes('X') as T(N);

        RETURN
END;

Finally the IN clause will looks like:

...
AND Courses.Type_Code IN (SELECT * FROM dbo.SplitString(@type_code,','))

Bertsche